深入分析MSSQL数据库中事务隔离级别和锁机制

1、锁机制NOLOCK和 READPAST 的区别。1. 开启一个事务执行插入数据的操作。?12345 BEGIN TRAN t INSERT INTO Customer SELECT a,a2. 执行一条查询语句。?1 SELECT * FROM Customer WITH (NOLOCK)结果中显示”提交 ) 。 NOLOCKa”和” a”。当 1 中事务回滚后，那么 a 将成为脏数据。 (注 :1 中的事务未表明没有对数据表添加共享锁以阻止其它事务对数据表数据的修改。?1 SELECT * FROM Customer这条语句将一直死锁，直到排他锁解除或者锁超时为止。( 注 : 设置锁超时SE

2、TLOCK_TIMEOUT 1800)?1 SELECT * FROM Customer WITH (READPAST)这条语句将显示 a 未提交前的状态， 但不锁定整个表。 这个提示指明数据库引擎返回结果时忽略加锁的行或数据页。3. 执行一条插入语句。?1234567 BEGIN TRAN t INSERT INTO Customer SELECT b,b COMMIT TRAN t这个时候，即使步骤1 的事务回滚，那么a 这条数据将丢失，而b 继续插入数据库中。NOLOCK1 执行如下语句。?1234567 BEGIN TRAN ttt SELECT * FROM Customer WIT

3、H (NOLOCK) WAITFOR delay 00:00:20 COMMIT TRAN ttt注： NOLOCK 不加任何锁，可以增删查改而不锁定。?1234567 INSERT INTO Customer SELECT a,b 不锁定 DELETE Customer where ID=1 不锁定 SELECT * FROM Customer 不锁定 UPDATE Customer SET Title=aaWHERE ID=1不锁定ROWLOCK1. 执行一条带行锁的查询语句。?123456789 SET TRANSACTION ISOLATION LEVEL REPEA TABLE RE

4、AD - (必须 ) BEGIN TRAN ttt SELECT * FROM Customer WITH (ROWLOCK) WHERE ID=17 WAITFORdelay 00:00:20 COMMIT TRAN ttt注：在删除和更新正在查询的数据时，会锁定数据。 对其他未查询的行和增加，查询数据无影响。?1234567891011 INSERT INTOCustomer SELECT a,b 不等待DELETECustomerwhere ID=17等待DELETECustomer where ID<>17不等待SELECT * FROMCustomer 不等待UPDATE

5、Customer SET Title=aaWHEREID=17 等待UPDATECustomer SET Title=aa WHERE ID<>17不等待HOLDLOCK ，TABLOCK和 TABLOCKX1. 执行 HOLDLOCK?1234567 BEGIN TRAN ttt SELECT * FROM Customer WITH (HOLDLOCK) WAITFOR delay 00:00:10 COMMIT TRAN ttt注：其他事务可以读取表，但不能更新删除update Customer set Title=aa 要等待10 秒中。SELECT * FROM Cust

6、omer不需要等待2. 执行 TABLOCKX?1234567 BEGIN TRAN ttt SELECT * FROM Customer WITH (TABLOCKX) WAITFOR delay 00:00:10 COMMIT TRAN ttt注：其他事务不能读取表,更新和删除update Customer set Title=aa 要等待10 秒中。SELECT * FROM Customer要等待10 秒中。3. 执行 TABLOCK?1234567 BEGIN TRAN ttt SELECT * FROM Customer WITH (TABLOCK) WAITFOR delay 0

7、0:00:10 COMMIT TRAN ttt注：其他事务可以读取表，但不能更新删除update Customer set Title=aa 要等待10 秒中。SELECT * FROM Customer不需要等待UDPLOCK1. 在 A 连接中执行。?1234567 BEGIN TRAN ttt SELECT* FROM Customer WITH (UPDLOCK) WAITFORdelay 00:00:10 COMMIT TRAN ttt2. 在其他连接中执行。update Customer set Title=aa where ID=1 要等 10 秒SELECT * FROM Cu

8、stomer不用等insert into Customer select a,b 不用等注：对于 UDPLOCK锁，只对更新数据锁定。注：使用这些选项将使系统忽略原先在SET 语句设定的事务隔离级别（ SET TransactionIsolation Level) 。事务隔离级别脏读： READ UNCOMMITTED脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据， 那么另外一个事务读到的这个数据是脏数据， 依据脏数据所做的操作可能是不正确的。1. 在 A 连接中执行。

9、?123456789 BEGIN TRAN t INSERT INTO Customer SELECT 123,123 WAITFOR delay00:00:20 COMMIT TRAN t2. 在 B 连接中执行。?123 SET TRANSACTIONISOLATIONLEVELREADUNCOMMITTEDSELECT*FROM Customer这个时候，未提交的数据会123会显示出来，当A 事务回滚时就导致了脏数据。相当于(NOLOCK)提交读： READ COMMITTED1. 在 A 连接中执行。?123456789 BEGIN TRAN t INSERT INTO Custome

10、r SELECT 123,123 WAITFOR delay 00:00:20 COMMIT TRAN t2. 在 B 连接中执行。?123 SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROMCustomer这个时候，未提交的数据会123不会显示出来，当据。避免了脏读。A 事务提交以后B 中才能读取到数不可重复读：REPEATABLE READ不可重复读是指在一个事务内， 多次读同一数据。 在这个事务还没有结束时， 另外一个事务也访问该同一数据。 那么，在第一个事务中的两次读数据之间， 由于第二个事务的修改，那么第一个事务两

11、次读到的数据可能是不一样的。 这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如：1. 在 A 连接中执行如下语句。?1234567891011 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN ttt SELECT * FROM Customer WHERE ID=17 WAITFOR delay 00:00:30 SELECT * FROM Customer WHERE ID=17 COMMIT TRAN ttt2. 在 B 连接中执行如下语句，而且要在第一个事物的三十秒等待内。UPDATE Cu

12、stomer SET Title=d WHERE ID=17这个时候，此连接将锁住不能执行，一直等到取到的数据相同，不受B 连接干扰。注，对于 Read Committed 和 Read UnCommitted执行完以后，两条查询语句结果不同，即第二条查询的序列化读： SERIALIZABLEA 连接结束为止。而且A 连接中两次读情况下， B 连接不会锁住， 等到 A 连接Title 变成了 d。1. 在 A 连接中执行。?123456789 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN t UPDATE Customer SE

13、T Title=111 WAITFOR delay 00:00:20 COMMIT TRAN t2. 在 B 连接中执行，并且要在A 执行后的20 秒内。?1234567 BEGIN TRAN tt INSERT INTO Customer SELECT 2,2 COMMIT TRAN tt在 A 连接的事务提交之前，B 连接无法插入数据到表中，这就避免了幻觉读。注：幻觉读是指当事务不是独立执行时发生的一种现象，例如 第一个事务对一个表中的数据进行了修改， 这种修改涉及到表中的全部数据行。 同时，第二个事务也修改这个表中的数据， 这种修改是向表中插入一行新数据。 那么，以后就会发生操作第一个事

14、务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。共享锁共享锁（ S 锁）允许并发事务在封闭式并发控制（请参阅并发控制的类型）下读取(SELECT)资源。资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。读取操作一完成，就立即释放资源上的共享锁（S 锁），除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示保留共享锁（S 锁）。更新锁更新锁（ U 锁）可以防止常见的死锁。在可重复读或可序列化事务中，此事务读取数据 获取资源（页或行）的共享锁（S 锁） ，然后修改数据 此操作要求锁转换为排他锁（X锁） 。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁（X 锁）。共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。 第二个事务试图获取排他锁（X锁）以进行更新。由于两个事务都要转换为排他锁（X 锁），并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。若要避免这种潜在的死锁问题，请使用更新锁（U 锁）。一次只有一个事务可以获得资源的更新锁（ U 锁）。如果事务修改资源，则更新锁（U 锁）转换为排他锁（X锁）。排他锁排他锁（ X锁）可以防止并发事务对资源进行访问。使用排他锁（X 锁）时，任何其他事务