任务9.2    锁

SQL Server的关键特性之一是支持多用户共享同一数据库，但是，当某些用户同时对数据进行修改时，会产生一定的并发问题。使用事务便可以解决用户存取数据时出现的这个问题，从而保持数据库的完整性和一致性。然而如果希望防止其他用户修改另一个还没完成的事务中的数据，就必须在事务中使用锁。

       锁是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。并发控制的主要方法是封锁，锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致。当INSERT、UPDATE、DELETE语句更改某个行中的数据时，就在行上加了一个锁。这些锁被放在表中没有提交的行上，以防止在最后完成更改之前修改这些行。每个行的锁都独立于其他行的锁。完成提交或者回滚操作后，锁被释放。

一 、锁的粒度

锁定义在需要锁定的资源上，这些资源可以是索引、数据行、表或者数据库。SQL Server 总会尝试精细地锁住资源，在多数情况下，它首先会基于行级加锁。如果锁住的行太多，会提升锁的级别，这种锁的级别被称为“锁的粒度”。锁定的资源在粒度上差异很大。从细（行）到粗（数据库）。细粒度锁允许更大的数据库并发，因为用户能对某些未锁定的行执行查询。然而，每个由SQL Server产生的锁都需要内存，所以数以干计独立的行级别的锁也会影响SQL Server的性能。粗粒度的锁降低了并发性，但消耗的资源也较少。

二 、锁的分类

1）共享锁（S）：也叫读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁的时候，所有的事务都不能对这个资源进行修改，直到数据读取完成，共享锁释放。

2）排它锁（X）：也叫独占锁或写锁。就是如果你对数据资源进行增删改操作时，不允许其它任何事务操作这块资源，直到排它锁被释放，防止同时对同一资源进行多重操作。

3）更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取再修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排他锁，否则变为共享锁。

4）意向锁：SQL Server需要在层次结构中的底层资源上（如行，列）获取共享锁，排它锁，更新锁。例如表级放置了意向共享锁，就表示事务要对表的页或行上使用共享锁。在表的某一行上放置意向锁，可以防止其它事务获取其它不兼容的锁。意向锁可以提高性能，因为数据引擎不需要检测资源的每一列每一行，就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型：意向共享锁（IS），意向排他锁（IX），意向排他共享锁（SIX）。

5）架构锁：防止修改表结构时，并发访问的锁。

6）大容量更新锁：允许多个线程将大容量数据并发的插入到同一个表中，在加载的同时，不允许其它进程访问该表。

三、 事务的隔离级别

SQL Server通过在资源上使用不同类型的锁来隔离事务。为了开发安全的事务，要考虑到以下几方面的重要问题。

定义事务的内容。

定义应该在什么情况下进行回退。

定义如何在事务中保持锁定。

定义在多长时间内使事务保持锁定。

这些问题都需要由隔离级别来决定。应用不同的隔离级别，SQL Server赋予开发者一种能力，让他们能为每一个单独事务定义与其他事务的隔离程度。

三、 事务的隔离级别•

ANSI SQL定义了4种事务隔离级别，SQL Server 2008能够完全支持这些级别：

（1）未提交读(READ UNCOMMITTED):在读数据时不会检查或使用任何锁。因此，在  这种隔离级别中可能读到没有提交的数据。这是最低的隔离级别，它只能确保不读取发生物理损坏的数据。

（2）已提交读(READ COMMITTED):只读取提交的数据并等待其他事务释放排他锁。读数据的共享锁在读操作完成后立即释放。已提交读是SQL Server的默认隔离级别。

（3）可重复读(REPEATABLE READ):像已提交读级别那样读数据，但会保持共享锁直  到事务结束。

（4）可序列化(SERIALIZABLE):这是最严格的隔离级别，其工作方式类似于可重复读，但它不仅会锁定受影响的数据，还会锁定这个范围。这就阻止了第二个事务在第一个事务的查询所涉及的范围内执行插入和删除操作，而这种情况可以导致幻象读。

使用SET TRANSACTION ISOLATION LEVEL语句可以设置数据库的事务隔离级别。

四、 死锁及其处理

      当一个事务试图获取某个对象的锁，而另一个事务正对该对象进行了锁定，如果第一个事务必须等待第二个事务释放锁才能继续执行，此时即发生阻塞现象。

      死锁是指会导致永久阻塞的特殊场景。它发生在两个或多个事务相互阻塞的时候。如果发生这种情况，每一个事务都在等待其他事务释放它们的锁。但是这永远不会发生，因为其他事务也在等待。之所以称之为死锁，是因为事务永远不会释放它们所占用的锁。为了防止这种情况的发生，SQL Server 会通过回退其中一个事务并返回一个错误的方式来自己解决这种问题，以让其他的事务能够完成它们的工作。

为了防止并处理死锁，在实际的数据库操作中应该遵守以下原则：

(1)遵守最少化阻塞规则。阻塞越少，发生死锁的机会就越少。

(2)在事务中要按一致的顺序访问对象。如果以上示例中的两个事务都按一个顺序访问表。就不会发生死锁。因此，要在数据库中定义对所有表的访问顺序。

(3)在错误处理程序中检查错误1205，并在错误发生时重新提交事务。

(4)在错误处理程序中加入一个过程将错误的详细信息写入日志。

如果遵守这些规则，就有机会阻止死锁。当死锁发生时，由于事务会自动提交，因此对于用户来说是未知的，但可以通过日志来监视死锁。

死锁及避免方法

•在两个或多个事务中，如果每个事务都锁定了另外一个事务所需的资源数据，这时这些事务将无法获得对方资源而同时处于等待状态，因此而形成死锁。

•例：为了形成死锁，请分别开启两个查询窗口，输入命令并执行。

例：

--A程序  事务

USE 教务数据库

PRINT GETDATE()

SET DEADLOCK_PRIORITY LOW

--将死锁的优先级设置为低LOW,发生死锁将自动释放资源

BEGIN TRAN  --开启一个事务

PRINT GETDATE()

PRINT '正在执行第一个事务...'

UPDATE 学生表 SET 专业班级='19软件1班' WHERE  学号='201906003'

--将学生表中学号为的学生的专业班级改为'19软件1班‘,锁定学生表

WAITFOR DELAY '00:00:05'  --等待秒，再执行下一条命令

UPDATE 课程表 SET 学分=1 WHERE  课程号='3003'

--将课程表中课程号为的学分改为, 

--由于第二个事务锁定课程表，此事务将处于等待状态

select  *  from  学生表

select  *  from  课程表

COMMIT TRAN

PRINT GETDATE()

select  *  from  学生表

select  *  from  课程表