SQL Server DBA面试知识点（七）-- 锁

锁有两个维度的属性，粒度和类型，下面分别描述。

锁的粒度granularity

粒度即一个锁控制的范围，SQL Server中有RID (row identifier) , key, page, extent, table, db。如RID用于Heap中的行锁，key为index用的行锁，表锁即范围为整张表的锁。
由定义可知，粒度越小并发性越高，反之亦然。例如两个用户分别修改一张表中不同的两行数据，且都使用的是行锁，那么这两个用户的修改互不干扰。但如果其中一个用户使用了表锁，则另一个用户需要等到第一个用户修改完毕，释放表锁后才能进行，因此事务之间更容易被阻塞。
使用何种粒度的锁，是SQL Server的自动行为，通常不需要人为干涉。但我们也可以人为指定锁的粒度，甚至是否使用锁，方法是在SQL中使用query hint。
SQL Server选择锁粒度的原则是在资源充足，保证一致性的情况下，尽量使用小粒度锁以提高并发度。但由于SQL Server在内存中管理锁，因此当数据库出现内存压力时，SQL Server会上升锁的粒度(例如行锁变表锁)。这样用户修改一张表时，以前可能需要例如1000个行锁，现在只需要一个表锁，从而节省了资源。

锁的类型mode

SQL Server的锁类型有Shared(S),update(U), exclusive(X), intent, schema, bulk update(table lock).
对于不同的SQL，SQL Server自动选择锁的类型。例如用户A正在运行一个表查询SQL，SQL Server会在该SQL运行期间在涉及的数据加上共享锁，意为“嗨！我正在查询这些数据。你可以同时查询这些数据，但是不能修改，因为那样会影响我的查询结果”。

不同的recovery mode会影响SQL Server的锁策略。例如在READ UNCOMMITTED级别下允许脏读。此时SQL Server不会给查询语句涉及的数据加共享锁，因此其他用户可以同时修改数据。
例如在该模式下用户A正在查询一批数据，用户B同时试图修改这些数据。由于没有共享锁，修改行为不会受到阻塞。而用户A则读取到了B最新修改的数据后，并结束了查询。但此时很不幸由于发生了一些异常，用户B的修改rollback了，但用户A已经得到了这些错误的数据，即发生了所谓的“脏读”。
具体请查看我的另外一篇文章：
http://blog.youkuaiyun.com/onlyqi/article/details/50418077

锁的作用机制非常复杂，我们不需要了解的太深入。下面这篇文章对锁做了概括性的描述，值得一看：
https://msdn.microsoft.com/en-us/library/aa213039(v=sql.100).aspx

在此我们对其中两种稍微难理解的锁类型做一些解释。

1. 意向锁
   意向锁解决在不同资源粒度加锁时，可能导致的阻塞问题。
   想象这样一个场景：一个表中的一行数据正在被更新，数据库只在这一行加排他锁就可以了。但是此时另外一个线程要更新表中的大部分数据，数据库可能会企图在整个表上加排他锁。如果此时加了第二个进程的排他锁，第一个进程就会被阻塞。
   解决方法就是使用意向锁。对任一资源加锁时，必须先对它的更高粒度加意向锁。对于上例，数据库在对一行加排他锁之前，会先对更高粒度的资源加意向排他锁，例如对page加意向锁。而第二个线程会检查page或更高粒度上是否有意向锁存在。
   意向锁可以提高性能：Intent locks improve performance because SQL Server examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.
   意向锁的含义是如果对一个资源加意向锁，则说明该资源的下层资源正在被加锁；对任一节点加锁时，必须先对它的上层节点加意向锁。注意这里的上层节点未必是仅仅指父一层，也许会在好几层上都加意向锁。

2. Update锁
   Update锁可以阻止一种常见死锁的发生。
   想象这样一个场景：一个典型的update事务，首先查询了一条记录，因此会请求一个共享锁(页级或行级)。然后修改一行，因此请求将共享锁转换为排它锁。
   当两个事务同时请求同一资源的共享锁，然后想同时修改数据时：
   第一个事务试图将共享锁转换为排它锁，而该过程必须等待，因为这个事务的排它锁和另外一个事务的共享锁是互斥的。
   第二个事务也试图做同样的事，它也必须等待，因为排它锁和第一个事务的共享锁是互斥的。
   两个事务都需要等待对方释放共享锁，死锁产生了。这种类型的死锁称为conversion deadlock
   为了避免这种死锁，SQL Server使用update锁。一次只有一个事务可以取得一个资源的update锁。当事务设法修改资源的时候，update锁转换为排它锁，否则，update锁转换为共享锁。

死锁

死锁是两个(或多个进程)在互相等待对方的资源时发生的现象，死锁会导致进程因永远等不到资源而饿死。由于同一个事务中的SQL会一直持有锁直到事务结束(而非SQL运行结束），因此容易发生死锁。例如下面的这个事务：

begin tran
delete tableA where ID=1
delete tableB where ID=1
commit tran

假如该事务运行时,第一个delete取得了tableA的的排他锁并运行后，该事务不会释放排他锁，而是继续持有。接着该事务试图获得tableB的排他锁，此时如果该有别的进程正占有该资源，则需要等待。这就发生了持有一个资源，并等待另外资源的状况。只有整个事务完成或rollback，事务中的锁才会被释放。如果此时有如下另外一个进程正在运行，则会发生死锁。

begin tran
delete tableB where ID=1
delete tableA where ID=1 –持有tableB的排他锁并等待tableA的排他锁
commit tran

事务如此设计是有原因的：只有一直持有事务涉及的所有资源的锁，才能保证在事务运行期间，这些资源不会被其他用户占用。因此当事务发生rollback时，才能不受阻碍的rollback(否则则需要等到其他用户释放资源后，才能rollback)。
SQL Server除snapshot之外的其他事务隔离级别都是这样工作的。snapshot因其提供row version，所以有所不同。

SQL Server有检测死锁的机制，一旦发生死锁，SQL Server会立即选择一个牺牲者，将其进程kill掉。因此死锁的生命期只有一瞬间。不会发生所谓的“数据库怎么这么慢，可能死锁了”之类的事儿。
如果一个SQL或存储过程频繁发生死锁，那一定是存在下面一种或多种情况：
1,同一个SQL或存储过程被并发调用。对同样的数据并发运行同样的修改必然导致冲突更易发生。
2,SQL或存储过程的效率太低，需要优化。
3,隔离级别太高导致可承受的并发太低。

一旦发生死锁，SQL Server会在log中记录该事件，也可以用trace flag来跟踪死锁。对经常发生的死锁进行详细分析并优化，是DBA的重要工作之一。