Is Key-Rang Lock only available at the serialization level?

I used to think that key range locking only exists at the serialization isolation level, but the Read-Committed isolation level also exists in a forum post:

59: 50. spid24s process id = process6463b88 taskpriority = 0 logused = 480 waitresource = KEY: 6: 72057594065715200 (8500ea663c04) waittime = 516

OwnerId = 773364767 transactionname = test lasttranstarted = 2013-06-04T08: 59: 49.450
XDES = 0x80092570 lockMode = RangeS-U schedulerid = 22 kpid = 2872 status = suincluded

Spid = 57 sbid = 0 ecid = 0 priority = 0 trancount = 3

Lastbatchstarted = 2013-06-04T08: 59: 49.453 lastbatchcompleted = 2013-06-04T08: 59: 49.450
Isolationlevel = read committed (2) xactid = 773364767 currentdb = 6 lockTimeout = 4294967295
Clientoption1 = 671088672 clientoption2 = 128056

Two articles mentioned that SQL Server implicitly elevated the isolation level to serialization under certain conditions.

The example of Microsoft cascade deletion is described as follows:

CreatetableFoo (FooIdintnotnullprimarykey)

CreatetableBar (FooIdintnotnull, BarIdintnotnull)

AltertableBaraddconstraintPK_Barprimarykey (FooId, BarId)

AltertableBaraddconstraintFK_Bar_Fooforeignkey (FooId) referencesFoo (FooId) ondeletecascade

InsertintoFoovalues (1) insertintoBarvalues (1, 1)

Settransactionisolationlevelreadcommitted

Begintran

DeletefromFoowhereFooId = 1

Committran

The deletion operation must be retained:

1. to delete the data in the Foo table, you must also delete the associated data in the Bar table.

2. When the statement ends, the foreign key constraint is still valid.

Therefore, at the read-committed isolation level, X locks continues until the end of the transaction, but it cannot be avoided that others insert data while deleting the data. Therefore, the SERIALIAZABLE isolation level is required.

Therefore, for similar operations, you do not need to display the serialization isolation level. SQL Server automatically upgrades the isolation level to block other operations from inserting data, thus affecting foreign key constraints. In this case, SQL Server uses the key range lock to avoid data corruption.

SERIALIZABLE locks are kept until the transaction ends. If a batch contains multiple statements, it may take some time to release these locks. Because SQL Server knows what locks are used and when to release them is safe.

 

In addition, the maintenance of the index view is the same.

For more information, see Read Committed isolation level, indexed views and locking behavior.

Conor vs. Isolation Level Upgrade on UPDATE/DELETE Cascading RI

 

This article is from the "follow SQL Server Technology" blog, please be sure to keep this source http://lzf328.blog.51cto.com/1196996/1217126