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:

 

Create Table
Foo (fooid
Int not nullprimary
Key)

Create tablebar (fooidint
Not null,
Barid intnot
Null)

Alter tablebar
Add Constraint
Pk_bar primary
Key (fooid, Barid)

Alter tablebar
Add Constraint
Fk_bar_foo foreign
Key (fooid) References
Foo (fooid) on
Delete Cascade

Insert into foo
Values (1) Insert
Into bar
Values (1, 1)

Set transactionisolation
Level read
Committed

Begin tran

Delete fromfoo
Where fooid
= 1

Commit tran

 

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