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

1.       在A连接中执行。

BEGIN TRAN t

INSERT INTO Customer

SELECT '123','123'

WAITFOR delay '00:00:20'

COMMIT TRAN t

2.       在B连接中执行。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM Customer

这个时候，未提交的数据会'123'会显示出来，当A事务回滚时就导致了脏数据。相当于(NOLOCK)

提交读：READ COMMITTED

1.       在A连接中执行。

BEGIN TRAN t

INSERT INTO Customer

SELECT '123','123'

WAITFOR delay '00:00:20'

COMMIT TRAN t

2.       在B连接中执行。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM Customer

这个时候，未提交的数据会'123'不会显示出来，当A事务提交以后B中才能读取到数据。避免了脏读。

不可重复读：REPEATABLE READ

不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

例如：

1.       在A连接中执行如下语句。

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 ttt

2.       在B连接中执行如下语句，而且要在第一个事物的三十秒等待内。

UPDATE Customer SET Title='d' WHERE ID=17

这个时候，此连接将锁住不能执行，一直等到A连接结束为止。而且A连接中两次读取到的数据相同，不受B连接干扰。

注，对于Read Committed和Read UnCommitted情况下，B连接不会锁住，等到A连接执行完以后，两条查询语句结果不同，即第二条查询的Title变成了d。

序列化读：SERIALIZABLE

1.       在A连接中执行。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN t

UPDATE Customer SET Title='111'

WAITFOR delay '00:00:20'

COMMIT TRAN t

2. 在B连接中执行，并且要在A执行后的20秒内。

BEGIN TRAN tt

INSERT INTO Customer

SELECT '2','2'

COMMIT TRAN tt

在A连接的事务提交之前，B连接无法插入数据到表中，这就避免了幻觉读。

注：幻觉读是指当事务不是独立执行时发生的一种现象，例如 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。

共享锁

共享锁（S 锁）允许并发事务在封闭式并发控制（请参阅并发控制的类型）下读取 (SELECT) 资源。资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。读取操作一完成，就立即释放资源上的共享锁（S 锁），除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示保留共享锁（S 锁）。

更新锁

更新锁（U 锁）可以防止常见的死锁。在可重复读或可序列化事务中，此事务读取数据 [获取资源（页或行）的共享锁（S 锁）]，然后修改数据 [此操作要求锁转换为排他锁（X 锁）]。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁（X 锁）。共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排他锁（X 锁）以进行更新。由于两个事务都要转换为排他锁（X 锁），并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

若要避免这种潜在的死锁问题，请使用更新锁（U 锁）。一次只有一个事务可以获得资源的更新锁（U 锁）。如果事务修改资源，则更新锁（U 锁）转换为排他锁（X 锁）。

排他锁

排他锁（X 锁）可以防止并发事务对资源进行访问。使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。