This is SQL Server question but I would appreciate the answers form other DBMS contexts properly identified.
这是SQL Server问题，但我希望从其他DBMS上下文中得到正确的答案。
The answer by Seth Lynch to my question in MSDN forum:
Seth Lynch在MSDN论坛上回答我的问题:
- Is a half-written values reading prevented WITH (NOLOCK) hint?
- 使用(NOLOCK)提示是否可以防止半写的值被读取?
tells:
告诉:
"When data is updated it is not over written - the original row is marked as deleted and a new row is inserted"
“当数据被更新时，它不会被重写——原始行被标记为已删除，并插入新行”
Is it correct statement? Can you give references supporting this in docs?
How can it be verified?
这是正确的声明吗?你能在文档中提供支持这一点的参考资料吗?如何验证?
Related discussions:
相关讨论:
- In SQL, is UPDATE always faster than DELETE+INSERT?
- 在SQL中，更新总是比删除+插入快吗?
Update: Not long time ago I believed that dirty reads permitted in READ UNCOMMITTED transaction isolation level (or, what is the same in SQL Server, through WITH(NOLOCK) hint) permitted reading (from other transactions) uncommitted (or committed, if not yet changed) values but not partly-changed (partly updated, partly deleted or partly inserted.
更新:很久以前我认为允许脏读取以读未提交的事务隔离级别(或相同的SQL Server,通过(NOLOCK)提示)允许阅读(从其他事务)未提交(或承诺,如果没有改变)值而不是partly-changed(部分更新,部分删除或部分插入。
RESUME': putting it short, that phrase is generally and for most cases incorrect (while it states categorically about rather uncommon cases in SQL Server)
简而言之，这个短语在大多数情况下都是不正确的(尽管它明确地指出SQL Server中不常见的情况)
3 个解决方案
#1
9
According to Kalen Delaney, in her book Inside Microsoft SQL Server 2005: The Storage Engine, SQL Server 2005 (and now 2008) can update a row by either using an insert/delete or in place, by just changing one column's value. Here's a quick summary of what she says on p. 306-311 of the book.
根据Kalen Delaney，在她的书中，微软SQL Server 2005:存储引擎，SQL Server 2005(和现在的2008)可以通过使用插入/删除或替换的方式来更新一行，只需改变一个列的值。以下是她在这本书的306-311页所做的简要总结。
The normal behavior in SQL Server 2005/2008 is to update a row in place. The row stays in the same location on the page and only the affected bytes are changed. An example of this would be updating the value in an integer column that's not part of the culstered index.
SQL Server 2005/2008中的正常行为是更新一个行。行保持在页面上的相同位置，只更改受影响的字节。一个例子是更新不属于已注册索引的整数列中的值。
A row may updated with an insert/delete when it's size changes and it no longer fits on the original page. This could happen when you change the value in a varchar column and make it longer. It also happens when the clustered index column is changing and the row needs to move because of its position in the index (because rows are ordered by the clustered key). An example of this would be changing someone's last name from "Smith" to "Jones" in a table with a clustered index on last name.
当一行的大小发生变化时，它可以使用insert/delete进行更新，并且不再适合原始页面。当您更改varchar列中的值并使其更长时，可能会发生这种情况。当聚集索引列发生更改，并且由于行在索引中的位置而需要移动时(因为行是由聚集键排序的)，也会发生这种情况。一个例子是将某人的姓从“Smith”更改为“Jones”，并在表中使用姓的聚集索引。
#2
2
This depends on the implementation.
这取决于实现。
In general, when multiversion consurrency control (MVCC) is used, the original row is kept. It is either marked as deleted by the transaction that deleted it and a replacement row is created, or a delta is stored elsewhere in the transaction context, until the transaction commits and the delta is applied to the existing row.
一般来说，当使用多版本consurency control (MVCC)时，将保留原始行。它要么被删除的事务标记为删除，然后创建一个替换行，要么将一个delta存储在事务上下文中的其他地方，直到事务提交并将这个delta应用到现有的行。
In lock based concurrency control, the row can be changed in situ as only a single transaction can read and write the row.
在基于锁的并发控制中，可以就地更改行，因为只有一个事务可以读写行。
The details are implementation dependent. Some systems will use a delta until commit, and some will change the row but keep a copy of the original to use in case of rollback.
细节取决于实现。有些系统将使用delta直到提交，有些系统将更改行，但保留原来的副本以便在回滚时使用。
#3
1
In Oracle, an UPDATE always changes the original row. The old values of the row are written to the UNDO log and remain there for some time as part of the implementation of multiversion concurrency control (MVCC).
在Oracle中，更新总是更改原始行。行的旧值被写入到撤消日志中，并作为多版本并发控制(MVCC)实现的一部分保留一段时间。
As long as the new values are not committed, all other transactions will get the old values from the UNDO log. The same happens if your query started before the COMMIT of the new values or with certain transaction isolation modes.
只要不提交新值，所有其他事务都将从UNDO日志中获取旧值。如果在提交新值之前或使用某些事务隔离模式之前开始查询，也会发生相同的情况。
If the new values are bigger and the row doesn't fit into the same page anymore, the row is migrated to a new page and the space on the old page is freed.
如果新值更大，且行不再适合于同一页面，那么该行将迁移到一个新页面，旧页面上的空间将被释放。
#1
9
According to Kalen Delaney, in her book Inside Microsoft SQL Server 2005: The Storage Engine, SQL Server 2005 (and now 2008) can update a row by either using an insert/delete or in place, by just changing one column's value. Here's a quick summary of what she says on p. 306-311 of the book.
根据Kalen Delaney，在她的书中，微软SQL Server 2005:存储引擎，SQL Server 2005(和现在的2008)可以通过使用插入/删除或替换的方式来更新一行，只需改变一个列的值。以下是她在这本书的306-311页所做的简要总结。
The normal behavior in SQL Server 2005/2008 is to update a row in place. The row stays in the same location on the page and only the affected bytes are changed. An example of this would be updating the value in an integer column that's not part of the culstered index.
SQL Server 2005/2008中的正常行为是更新一个行。行保持在页面上的相同位置，只更改受影响的字节。一个例子是更新不属于已注册索引的整数列中的值。
A row may updated with an insert/delete when it's size changes and it no longer fits on the original page. This could happen when you change the value in a varchar column and make it longer. It also happens when the clustered index column is changing and the row needs to move because of its position in the index (because rows are ordered by the clustered key). An example of this would be changing someone's last name from "Smith" to "Jones" in a table with a clustered index on last name.
当一行的大小发生变化时，它可以使用insert/delete进行更新，并且不再适合原始页面。当您更改varchar列中的值并使其更长时，可能会发生这种情况。当聚集索引列发生更改，并且由于行在索引中的位置而需要移动时(因为行是由聚集键排序的)，也会发生这种情况。一个例子是将某人的姓从“Smith”更改为“Jones”，并在表中使用姓的聚集索引。
#2
2
This depends on the implementation.
这取决于实现。
In general, when multiversion consurrency control (MVCC) is used, the original row is kept. It is either marked as deleted by the transaction that deleted it and a replacement row is created, or a delta is stored elsewhere in the transaction context, until the transaction commits and the delta is applied to the existing row.
一般来说，当使用多版本consurency control (MVCC)时，将保留原始行。它要么被删除的事务标记为删除，然后创建一个替换行，要么将一个delta存储在事务上下文中的其他地方，直到事务提交并将这个delta应用到现有的行。
In lock based concurrency control, the row can be changed in situ as only a single transaction can read and write the row.
在基于锁的并发控制中，可以就地更改行，因为只有一个事务可以读写行。
The details are implementation dependent. Some systems will use a delta until commit, and some will change the row but keep a copy of the original to use in case of rollback.
细节取决于实现。有些系统将使用delta直到提交，有些系统将更改行，但保留原来的副本以便在回滚时使用。
#3
1
In Oracle, an UPDATE always changes the original row. The old values of the row are written to the UNDO log and remain there for some time as part of the implementation of multiversion concurrency control (MVCC).
在Oracle中，更新总是更改原始行。行的旧值被写入到撤消日志中，并作为多版本并发控制(MVCC)实现的一部分保留一段时间。
As long as the new values are not committed, all other transactions will get the old values from the UNDO log. The same happens if your query started before the COMMIT of the new values or with certain transaction isolation modes.
只要不提交新值，所有其他事务都将从UNDO日志中获取旧值。如果在提交新值之前或使用某些事务隔离模式之前开始查询，也会发生相同的情况。
If the new values are bigger and the row doesn't fit into the same page anymore, the row is migrated to a new page and the space on the old page is freed.
如果新值更大，且行不再适合于同一页面，那么该行将迁移到一个新页面，旧页面上的空间将被释放。