1 Set transaction isolation level read committedbegin transaction update TB set val = Val + 10 where id = 2; waitfor delay '00: 00: 07 '-- simulate transaction processing, wait for 5 seconds -- select * from TB; -- Again select TB table commit transaction -- rollback -- roll back the transaction if select * from TB where id = 1 at this time, the ID must be followed by at least one (unique non-clustered index or clustered index) after the where statement. Otherwise, only the first SQL statement is completed. -- Create a table first: Create Table Tb (ID int, Val INT) insert TB values () and then execute: SET transaction isolation level read committedbegin transaction select * from TB; -- after the select operation is completed, the shared lock waitfor delay '00: 00: 05 'will be released -- simulate transaction processing, wait for 5 seconds to select * from TB; -- select TB table rollback again -- roll back the transaction in connection 2 and execute update TB set val = Val + 10 where id = 2; -------- return to connection 1. you can see that. the two select statements have different results. because in the default read committed isolation level, select Finished. the shared lock will be released immediately. -- Example 2. repeatable read connection 1: Set transaction isolation level repeatable readbegin transaction select * from TB; -- after this select is completed, the shared lock waitfor delay' 00: 00: 05 '-- simulate transaction processing, wait for 5 seconds select * from TB; -- select TB table rollback again -- roll back transaction connection 2: update TB set val = Val + 10 where id = 2; --- you can see that connection 2 is blocked. and connect to 1. the results of two select statements are the same. because the shared lock at the Repeatable read isolation level is retained until the end of the transaction. therefore, the X lock of other transactions is incompatible with the S lock. so connection 2 waits. http://topic.csdn.net/u/2011 0813/10/The Relationship Between the cac77918-667e-483f-b819-7bfe9e058097.html index and the deadlock 3 tablock, rowlock and so on should be put in the transaction to take effect. Read committed read, release immediately after reading, can read, write, 1 if useful to the index, if still that line, will wait, 2 if no index is used, no matter what, other Connections cannot be read, but can be read using nolock. Repeatable read reads, row locks (if there is no index in where), and if another statement also reads, it will wait,
Because the shared lock at the Repeatable read isolation level is retained until the end of the transaction.
