威哥浅谈Sql锁机制(悲观锁&乐观锁)

我会带着你远行 2023-06-12 05:29 18阅读 0赞

With NoLock：无锁（除了本身不锁表（不加任何锁） 也不会受其他的已存在的锁影响， 锁住的行数据也照样读）。

With  HolDLock：挂一个保持锁。

With  UpDLock：挂一个更新锁。

With  XLock：挂一个排他锁。

注意: With NoLock 是不能用于update,delete insert 这种更新语句的，简单的说 With NoLock 只能用于Select。

NOLOCK（不加锁）：

此选项被选中时，SQL Server 在读取或修改数据时不加任何锁。NOLOCK在概念上类似于READ UNCOMMITTED隔离级别。只针对于SELECT查询语句，它不会获取表的共享锁，换句话说不会阻止排它锁来更新数据行。 在这种情况下， 用户有可能读取到未完成事务（Uncommited Transaction）或回滚(Roll Back)中的数据, 即所谓的“脏数据”。

HOLDLOCK（保持锁）：

此选项被选中时，SQL Server 会将此共享锁保持至整个事务结束，而不会在途中释放。该选项等同于Serializable 隔离级别。

如：SELECT \* FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除 。

UPDLOCK（修改锁）：

此选项被选中时，SQL Server 在读取数据时使用修改锁来代替共享锁， 并将此锁保持至整个事务或命令结束。使用此选项能够保证多个进程能同时读取数据但只有该进程能修改数据。

UPDLOCK只是针对于表中的某一行记录来锁定从而阻止其他操作对该行的数据更新，UPDLOCK是行级别。

XLOCK（排它锁）：

排它锁是表级别锁，使用排它锁并一直保持到由语句处理的所有数据上的事务结束时。也可以使用PAGLOCK或TABLOCK指定该锁，这种情况下排它锁适用于适当级别的粒度。

TABLOCK（表锁）：

此选项被选中时，SQL Server 将在整个表上置共享锁直至该命令结束。 这个选项保证其他进程只能读取而不能修改数据。

PAGLOCK（页锁）：

此选项为默认选项， 当被选中时，SQL Server 使用共享页锁。 PAGLOCK 在使用一个表锁的地方用多个页锁。

TABLOCKX（排它表锁） ：

此选项被选中时，SQL Server 将在整个表上置排它锁直至该命令或事务结束。这将防止其他进程读取或修改表中的数据。 TABLOCKX强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表。

如：SELECT \* FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除。

READPAST：

让查询忽略被其他事务锁定的行或页，仅返回能够被读取的数据。只能用在运行于Read Committed 或 Repeatable Read 隔离级别下的事务中。

ROWLOCK（行锁）：

行级锁确保在用户取得被更新的行，到该行进行更新，这段时间内不被其它用户所修改。因而行级锁即可保证数据的一致性，又能提高数据操作的并发性。ROWLOCK语法可以使用在SELECT,UPDATE和DELETE语句中。

**现在随威哥一起再了解下锁模式：**

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

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

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

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

**架构锁** 在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。

大容量更新 (BU) 向表中大容量复制数据并指定了 TABLOCK 提示时使用。

哈哈，其实后面意向锁，威哥也没搞很明白。下面威哥就自己明白的做下描述。

共享锁：

共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。

更新锁：  
更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

排它锁 ：  
排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。

意向锁:

意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

**如何避免死锁？**

下面是威哥总结了一些方法。

1.按同一顺序访问对象；

2.避免事务中的用户交互；

3.保持事务简短并处于一个批处理中；

4.使用较低的隔离级别；

5. SELECT语句加With(NoLock)提示。

按同一顺序访问对象：

如果所有并发事务按同一顺序访问对象，则发生死锁的可能性会降低。例如，如果两个并发事务获得 Supplier 表上的锁，然后获得 Part 表上的锁，则在其中一个事务完成之前，另一个事务被阻塞在 Supplier 表上。第一个事务提交或回滚后，第二个事务继续进行。不发生死锁。将存储过程用于所有的数据修改可以标准化访问对象的顺序。

避免事务中的用户交互：

避免编写包含用户交互的事务，因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度，例如答复应用程序请求参数的提示。例如，如果事务正在等待用户输入，而用户去吃午餐了或者甚至回家过周末了，则用户将此事务挂起使之不能完成。这样将降低系统的吞吐量，因为事务持有的任何锁只有在事务提交或回滚时才会释放。即使不出现死锁的情况，访问同一资源的其它事务也会被阻塞，等待该事务完成。

保持事务简短并在一个批处理中：

在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致死锁。

保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁。

使用低隔离级别：

确定事务是否能在更低的隔离级别上运行。执行提交读允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。使用较低的隔离级别（例如提交读）而不使用较高的隔离级别（例如可串行读）可以缩短持有共享锁的时间，从而降低了锁定争夺。

SELECT语句加With(NoLock)提示：

默认情况下SELECT语句会对查询到的资源加S锁(共享锁)，S锁与X锁(排他锁)不兼容；但加上With(NoLock)后，SELECT不对查询到的资源加锁(或者加Sch-S锁，Sch-S锁可以与任何锁兼容)；从而可以是这两条SQL可以并发地访问同一资源。当然，此方法适合解决读与写并发死锁的情况，但加With(NoLock)可能会导致脏读。

**从程序员的角度看：分为乐观锁和悲观锁。**

乐观锁：相信并非是极少数的，假设运气不好遇到了，就放弃并返回信息告诉它再次尝试，因为它是极少数发生的。一般完全依靠数据库来管理锁的工作。

悲观锁：相信并发是绝大大部分的，并且每一个线程都必须要达到目的的。一般由程序员自己管理数据或对象上的锁处理，

下面威哥针对乐观锁和悲观锁做一个例子。

假如12306上售卖上海到武汉的票还剩一张，而2个用户同时操作购票，导致并发，那就会产生一票多卖的情况。下面跟着威哥分别使用悲观锁和乐观锁解决该问题。

注意：售票时，需要先拿到余票，之后售卖，再操作余票减1。

**先看会产生一票多卖的情况：**

Declare @count  as int;

Begin Tran

Select @count=余票数 From  TrainTable

Where Delay  ’00:00:05’  --模拟并发，故意延迟5秒

Update TrainTable Set余票数=@count-1