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

网络整理 - 08-16

锁机制
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 语句还将请求在联接表中读取的行上的共享锁。

sql加锁机制是什？

您好！锁是数据库中的一个非常重要的概念，它主要用于多用户环境下保证数据库完整性和一致性。
我们知道，多个用户能够同时操纵同一个数据库中的数据，会发生数据不一致现象。即如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。这些问题包括：丢失更新、脏读、不可重复读和幻觉读。数据库加锁就是为了解决以上的问题。
当然，加锁固然好，但是一定要避免死锁的出现。
在数据库系统中，死锁是指多个用户（进程）分别锁定了一个资源，并又试图请求锁定对方已经锁定的资源，这就产生了一个锁定请求环，导致多个用户（进程）都处于等待对方释放所锁定资源的状态。这种死锁是最典型的死锁形式, 例如在同一时间内有两个事务A和B，事务A有两个操作：锁定表part和请求访问表supplier；事务B也有两个操作：锁定表supplier和请求访问表part。结果，事务A和事务B之间发生了死锁。死锁的第二种情况是，当在一个数据库中时，有若干个长时间运行的事务执行并行的操作，当查询分析器处理一种非常复杂的查询例如连接查询时，那么由于不能控制处理的顺序，有可能发生死锁现象。
在应用程序中就可以采用下面的一些方法来尽量避免死锁了：（1）合理安排表访问顺序。（2）在事务中尽量避免用户干预，尽量使一个事务处理的任务少些, 保持事务简短并在一个批处理中。（3）数据访问时域离散法, 数据访问时域离散法是指在客户机/服务器结构中，采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间，采用工作流对后台事务进行统一管理。工作流在管理任务时，一方面限制同一类任务的线程数（往往限制为1个），防止资源过多占用; 另一方面合理安排不同任务执行时序、时间，尽量避免多个后台任务同时执行，另外，避免在前台交易高峰时间运行后台任务。（4）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过以下方法实现: 第一，将大表按行或列分解为若干小表; 第二，按不同的用户群分解。（5）使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度，SQL92定义了4种隔离性级别：未提交读、提交读、可重复读和可串行。如果选择过高的隔离性级别，如可串行，虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性，但各事务间冲突而死锁的机会大大增加，大大影响了系统性能。（6）使用绑定连接, 绑定连接允许两个或多个事务连接共享事务和锁，而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样，因此可以允许这些事务共享数据而不会有加锁的冲突。
总之，了解SQL Server的锁机制，掌握数据库锁定方法, 对一个合格的DBA来说是很重要的。

sql server对于事务隔离级别的问题，

你开两个进程（比如同一个工具开两个）

设置不自动提交。

一个程序对同一个表进行操作，另一个程序去读取数据

就可以看出区别。

这个你搜索一下，应该有现成的例子。