mianshi-SQL Server LOCK, ISOLATION LEVEL

SQL Server是自动实现锁的，但是有的时候需要手动调整锁的级别，那么如何做呢？在SQL Server是采用隔离级别(Isolation Level)来实现的。

有以下四种隔离级：

1. READ COMMITTED

和S锁(Share Lock)类似，在此隔离级下SELECT 命令不会返回尚未提交的数据，也不能返回脏数据，它是SQL Server 默认的隔离级；

2. READ UNCOMMITTED

与READ COMMITTED 隔离级相反，它允许读取已经被其它用户修改但尚未提交确定的数据，限制级别最小；

3. REPEATABLE READ

在此隔离级下用SELECT 命令读取的数据在整个命令执行过程中不会被更改，其他事务不能执行Update和Delete，但是可以Insert。此选项会影响系统的效能，非必要情况最好不用此隔离级；

4. SERIALIZABLE

这是最大的限制，和X（Exclusive Lock）锁类似，不允许其他事务进行任何写访问。如非必要，不要使用这个选项。

隔离级需要使用SET 命令来设定，其语法如下：

SET TRANSACTION ISOLATION LEVEL

{READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE}

在事务的开始使用这个命令即可，该隔离级别一直对该SQL Server连接（不是本事务）有效，直到下一次使用本命令设置了新的隔离级别为止。

活锁和死锁

下面我们来讨论关于锁的特殊情况：

假设T1要更新R1和R2，首先它Lock了R1，而另外一个事务T2也要更新R2和R1，他首先锁定了R2，这个时候，T1要锁定R2必须等待，而T2也要锁定R1，这个时候也必须等待，这样T1和T2互相等待对方解锁，造成了死循环，这个就称之为“死锁”。

再来看另外一个情况：T1锁定R，T2请求封锁R，这个时候必须等待，T3也请求封锁R，T1解锁之后，批准了T3的请求，于是T2必须等待，然后T3请求封锁R，T3解锁之后，批准了T4的请求，于是T2继续等待，...这样可能导致T2无限等待，这个就称之为“活锁”，活锁的解决比较简单，采取先来先服务策略即可。

死锁一般可以采取如下的策略：

1. 一次性对事务锁有请求的数据进行加锁，否则事务不能运行，缺点：降低了并发度；

2. 预先规定一个封锁顺序，所有事务按照一定的顺序进行加锁；

3. 不采取任何措施进行预防，而是检测是否有死锁和拆除死锁的方法。

关于死锁，我们应该尽可能降低死锁的可能性：事务尽可能简短；避免在事务中和用户交互；尽量使用低级别的隔离级别等等。

SQL Server中，对于死锁采取检测和拆除的方式：如果系统检测到有死锁，会选择一个事务作为牺牲者，该事务会自动终止并回滚，并且系统返回一个1025的错误给应用程序，任意一个程序有可能被系统作为牺牲品，因此，任意一个程序都应该处理1025错误，否则有可能你的应用程序不能正常运行。Oracle采取同样的策略。一般地，SQL Server会选择撤销花费代价最少的事务作为牺牲品。如果在SQL Server中要指定死锁时本事务的级别，可以使用如下的命令：

SET DEADLOCK_PRIORITY {LOW | NORMAL}

Low 表示如果发生死锁，那么当前事务作为首选的牺牲品，Normal表死按照正常的处理方式进行处理。前面讨论过，如果请求锁定，不能满足请求的时候，事务会进行等待，等待是不能无限期的，我们可以设定一个等待的时间，等待超过指定的时间之后，我们就认为可能发生了死锁，事物就自动回滚，锁定超时可以采用下面的命令来设置：

SET LOCK_TIMEOUT {–1 | 0 | n}

-1表示无限期等待，默认；0表示根本不等待；n则表示等待n毫秒，如果等待n毫秒之后还不能锁定成功，则返回锁定错误。

另外@@LOCK_TIMEOUT表示返回当前的锁超时设置。例如我们可以用SELECT @@Lock_Timeout来查看。