SQL Server数据库高级进阶之锁实战演练

一、SQL Server锁的本质

锁的定义：锁主要用于多用户环境下，保证数据库完整性和一致性的技术。

锁的解释：当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的完整性和一致性。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制。

二、SQL Server锁的分类

锁的分类，在教材上，网络上好多都是按两个维度来描述的。一种维度是按锁的功能来划分，一种维度是按概念来划分。

1)、按概念划分(从程序员的角度看)

•  悲观锁(Pessimistic Lock)

•  乐观锁(Optimistic Lock)

2）、按锁的功能来划分(从数据库系统的角度来看)

•  共享锁 (S)  (Shared (S) Locks) 用于不更改或不更新数据的操作（只读操作），如SELECT语句。

•  更新锁 (U) (Update (U) Locks)用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

•  排它锁 (X) (Exclusive (X) Locks) 用于数据修改操作，例如INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新。

•  意向锁 (I)(Intent Locks) 用于建立锁的层次结构。意向锁的类型为：意向共享(IS)、意向排它(IX)以及与意向排它共享(SIX)。

SQL Server数据库高级进阶之锁实战演练

 

三、认识SQL Server数据库锁

1）、共享锁 (S)

 共享锁 (S) (Shared (S) Locks) 用于不更改或不更新数据的操作（只读操作），如SELECT语句。

2）、 更新锁 (U)

 更新锁 (U) (Update (U) Locks) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

3）、排它锁 (X)

排它锁 (X) (Exclusive (X) Locks) 用于数据修改操作，例如INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新。

4）、意向锁 (I)

意向锁 (I) (Intent Locks)  用于建立锁的层次结构。意向锁的类型为：意向共享(IS)、意向排它(IX)以及与意向排它共享(SIX)。

四、如何避免锁升级

•  1、防止锁升级的最简单，最安全的方法是保持事务的简短，并减少昂贵查询的锁占用空间，以便不超过锁升级阈值,有几种方法可以实现这一目标。将大批量操作分解为几个较小的操作。删除大量数据的时候,可以一次只删除500个,执行多次，可以显着减少每个事务累积的锁定数量并防止锁定升级。

• 2、 创建索引使查询尽可能高效来减少查询的锁定占用空间。如果没有索引会造成表扫描可能会增加锁定升级的可能性, 更可怕的是，它增加了死锁的可能性，并且通常会对并发性和性能产生负面影响。根据查询条件创建合适的索引,最大化提升索引查找的效率,此优化的一个目标是使索引查找返回尽可能少的行，以最小化查询的的成本。

五、死锁的本质

死锁（Dead Lock）

死锁是指一种进程之间互相永久阻塞的状态，可能涉及两个或更多的进程。死锁是指在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。

六、如何预防死锁

 首先要理解，在多并发的环境中死锁是不可避免的，只能通过合理的数据库设计、良好的索引、适当的查询语句以及隔离等级等措施尽量减少死锁。

 最开始列出了死锁的4个必要条件，只要想办法破坏任意1个或多个条件就可以避免产生死锁。下列方法有助于最大限度的降低死锁：

 a） 按同一顺序访问对象；

 b）避免事务中的用户交互，也就是在事务执行过程中不要包含用户交互的步骤；

 c）保持事务简短并在一个批处理中；

 d）SELECT语句加WITH(NOLOCK)提示；

SELECT * FROM TableName WITH(NOLOCK);

七、如何利用锁来解决并发性带来的问题

1、并发性具体带来什么问题？

在多用户环境中，在同一时间可能会有多个用户更新相同的记录，这会产生冲突。这就是著名的并发性问题。

1）、脏读取：当一个事物读取其它完成一半事务的记录时，就会发生脏读取。

例：用户A和用户B看到的值都是5，用户B将值修改为2，用户A看到的值仍然是5，这时就发生了脏读取。

2）、不可重复读取：在每次读数据时，如果你获得的值都不一样，那表明你遇到了不可重复读取问题。

例：用户A看到的值是5，用户B将值修改为2，用户A刷新后看到的值仍然是5，这时就发生了不可重复读取。

3）、虚幻行：如果update和delect SQL语句未对数据造成影响，很可能遇到了虚幻行问题。

例：用户A将所有值都把5修改为2，用户B使用值2插入一个新记录，用户A查询所有值为2的记录，但却找不到新添加的记录，这时就叫虚幻行。

4）、更新丢失：一个事务的更新覆盖了其它事务的更新结果，就是所谓的更新丢失。

例：用户A将所有值从5修改为2，用户B将所有值从2修改为5，用户A丢失了他的更新。

2、如何解决并发性的问题

为了解决这些并发带来的问题。 我们需要引入并发控制机制。

悲观锁(Pessimistic Lock)——应用场景：写多

顾名思义，很悲观。每次去拿数据的时候都认为别人会修改数据，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。

乐观锁(Optimistic Lock)——应用场景：读多

顾名思义，很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断在此期间别人有没有去更新这个数据。

1，乐观锁适用于多读的应用类型，这样可以提高吞吐量。

2，乐观锁一般加时间戳字段（或者自定义版本号字段）来实现。

在实际生产环境里边,如果并发量不大且不允许脏读，可以使用悲观锁解决并发问题；但如果系统的并发非常大的话,悲观锁定会带来非常大的性能问题,所以我们就要选择乐观锁定的方法。

八、思考与总结

乐观锁复杂事务控制

乐观锁:大多数是基于数据版本(version)的记录机制实现的。即为数据增加一个 版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表添加一个”version" 字段来实现读取出数据时，将此版本号同读出,之后更新时，对此版本号加1。此时,将提交数据的版本号与数据库表对应记录的当前版本号进行比对，如果提交的数据版本号大于数据库当前版本号，则予以更新，否则认为是过期数据。