深入分析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
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这类修改还没有提交到数据库中，这时候，另外一个事务也访问这个数据，然后使用了这个数据。由于这个数据是还没有提交的数据，那末另外一个事务读到的这个数据是脏数据，根据脏数据所做的操作多是不正确的。
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 提示或未提交读隔离级别时才会进行读取操作。
数据修改语句（如 INSERT、UPDATE 和 DELETE）合并了修改和读取操作。语句在履行所需的修改操作之前首先履行读取操作以获得数据。因此，数据修改语句通常要求同享锁和排他锁。例如，UPDATE 语句可能根据与一个表的联接修改另外一个表中的行。在此情况下，除要求更新行上的排他锁以外，UPDATE 语句还将要求在联接表中读取的行上的同享锁。
文章来源：丸子建站
文章标题：深入分析MSSQL数据库中事务隔离级别和锁机制
https://www.wanzijz.com/view/61839.html
