深入分析MSSQL数据库中事务隔离级别和锁机制
锁机制
NOLOCK和READPAST的区别。
1. 开启一个事务执行插入数据的操作。
BEGIN TRAN t INSERT INTO Customer SELECT 'a','a'
2. 执行一条查询语句。
SELECT * FROM Customer WITH (NOLOCK)
结果中显示”a”和”a”。当1中事务回滚后，那么a将成为脏数据。(注:1中的事务未提交) 。NOLOCK表明没有对数据表添加共享锁以阻止其它事务对数据表数据的修改。
SELECT * FROM Customer
这条语句将一直死锁，直到排他锁解除或者锁超时为止。(注:设置锁超时SET LOCK_TIMEOUT 1800)
SELECT * FROM Customer WITH (READPAST)
这条语句将显示a未提交前的状态，但不锁定整个表。这个提示指明数据库引擎返回结果时忽略加锁的行或数据页。
3. 执行一条插入语句。
BEGIN TRAN t INSERT INTO Customer SELECT 'b','b' COMMIT TRAN t
这个时候，即使步骤1的事务回滚，那么a这条数据将丢失，而b继续插入数据库中。
NOLOCK
1． 执行如下语句。
BEGIN TRAN ttt SELECT * FROM Customer WITH (NOLOCK) WAITFOR delay '00:00:20' COMMIT TRAN ttt
注：NOLOCK不加任何锁，可以增删查改而不锁定。
INSERT INTO Customer SELECT 'a','b' –不锁定 DELETE Customer where ID=1 –不锁定 SELECT * FROM Customer –不锁定 UPDATE Customer SET Title='aa' WHERE ID=1 –不锁定
ROWLOCK
1. 执行一条带行锁的查询语句。
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- (必须) BEGIN TRAN ttt SELECT * FROM Customer WITH (ROWLOCK) WHERE ID=17 WAITFOR delay '00:00:20' COMMIT TRAN ttt
注：在删除和更新正在查询的数据时，会锁定数据。对其他未查询的行和增加，查询数据无影响。
INSERT INTO Customer SELECT 'a','b' –不等待 DELETE Customer where ID=17 –等待 DELETE Customer where ID<>17 –不等待 SELECT * FROM Customer –不等待 UPDATE Customer SET Title='aa' WHERE ID=17–等待 UPDATE Customer SET Title='aa' WHERE ID<>17–不等待
HOLDLOCK，TABLOCK和TABLOCKX
1. 执行HOLDLOCK
BEGIN TRAN ttt SELECT * FROM Customer WITH (HOLDLOCK) WAITFOR delay '00:00:10' COMMIT TRAN ttt
注：其他事务可以读取表，但不能更新删除
update Customer set Title='aa' —要等待10秒中。
SELECT * FROM Customer —不需要等待
2. 执行TABLOCKX
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
BEGIN TRAN ttt SELECT * FROM Customer WITH (TABLOCK) WAITFOR delay '00:00:10' COMMIT TRAN ttt
注：其他事务可以读取表，但不能更新删除
update Customer set Title='aa' —要等待10秒中。
SELECT * FROM Customer —不需要等待
UDPLOCK
1. 在A连接中执行。
BEGIN TRAN ttt SELECT * FROM Customer WITH (UPDLOCK) WAITFOR delay '00:00:10' COMMIT TRAN ttt
2. 在其他连接中执行。
update Customer set Title='aa' where ID=1—要等10秒
SELECT * FROM Customer –不用等
insert into Customer select 'a','b'–不用等
注：对于UDPLOCK锁，只对更新数据锁定。
注：使用这些选项将使系统忽略原先在SET语句设定的事务隔离级别（SET Transaction Isolation Level)。
事务隔离级别
脏读：READ UNCOMMITTED
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
- 上一篇：SQL优化技巧指南
- 下一篇：浅析SQL Server中包含事务的存储过程
