SqlServer中的锁

一. 为什么要引入锁

多个用户同时对数据库的并发操作时会带来以下数据不一致的问题。

二、锁的分类

（1） 从数据库系统的角度来看

锁分为以下三种类型：
* 独占锁（Exclusive Lock）（排它锁）
独占锁锁定的资源只允许进行锁定操作的程序使用，其它任何对它的操作均不会被接受。执行数据更新命令，即INSERT、 UPDATE 或DELETE 命令时，SQL Server 会自动使用独占锁。但当对象上有其它锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放。
* 共享锁（Shared Lock）
共享锁锁定的资源可以被其它用户读取，但其它用户不能修改它。在SELECT 命令执行时，SQL Server 通常会对对象进行共享锁锁定。通常加共享锁的数据页被读取完毕后，共享锁就会立即被释放。
* 更新锁（Update Lock）
更新锁是为了防止死锁而设立的。当SQL Server 准备更新数据时，它首先对数据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到SQL Server 确定要进行更新数据操作时，它会自动将更新锁换为独占锁。但当对象上有其它锁存在时，无法对其作更新锁锁定。

（2）从程序员的角度看

锁分为以下两种类型：
* 乐观锁（Optimistic Lock）
乐观锁假定在处理数据时，不需要在应用程序的代码中做任何事情就可以直接在记录上加锁、即完全依靠数据库来管理锁的工作。一般情况下，当执行事务处理时SQL Server会自动对事务处理范围内更新到的表做锁定。
* 悲观锁（Pessimistic Lock）
悲观锁对数据库系统的自动管理不感冒，需要程序员直接管理数据或对象上的加锁处理，并负责获取、共享和放弃正在使用的数据上的任何锁。

（3）从锁的粒度看

锁是加在数据库对象上的。而数据库对象是有粒度的，比如同样是1这个单位，1行，1页，1个B树，1张表所含的数据完全不是一个粒度的。因此，所谓锁的粒度，是锁所在资源的粒度。

Microsoft SQL Server 数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。 为了尽量减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别。 锁定在较小的粒度（例如行）可以提高并发度，但开销较高，因为如果锁定了许多行，则需要持有更多的锁。 锁定在较大的粒度（例如表）会降低了并发度，因为锁定整个表限制了其他事务对表中任意部分的访问。 但其开销较低，因为需要维护的锁较少。

资源	说明
RID	用于锁定堆中的单个行的行标识符。
KEY	索引中用于保护可序列化事务中的键范围的行锁。
PAGE	数据库中的8KB页，例如数据页或索引页。
EXTENT	一组连续的八页，例如数据页或索引页。
HoBT	堆或B树。用于保护没有聚集索引的表中的B树（索引）或堆数据页的锁。
TABLE	包括所有数据和索引的整个表。
FILE	数据库文件。
APPLICATION	应用程序专用的资源。
METADATA	元数据锁。
ALLOCATION_UNIT	分配单元。
DATABASE	整个数据库。

三、sqlserver提供的表级锁

sqlserver所指定的表级锁定提示有如下几种 

1． HOLDLOCK: 在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。

2． NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。

3． PAGLOCK：指定添加页锁（否则通常可能添加表锁）

4． READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL Server 2000 在此隔离级别上操作。

5． READPAST: 跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，READPAST仅仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作

6． READUNCOMMITTED：等同于NOLOCK。

7． REPEATABLEREAD：设置事务为可重复读隔离性级别。

8． ROWLOCK：使用行级锁，而不使用粒度更粗的页级锁和表级锁。

9． SERIALIZABLE：用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。

10． TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQL Server在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。

11． TABLOCKX：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。

12． UPDLOCK ：指定在读表中数据时设置更新锁（update lock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改

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

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

四、sql (server) 行锁，表锁案例

--设table1(A,B,C) 
A B C 
a1 b1 c1 
a2 b2 c2 
a3 b3 c3

1）排它锁 tablockx

新建两个连接
在第一个连接中执行以下语句

begin tran 
update table1 set A='aa' where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran

在第二个连接中执行以下语句

begin tran 
select * from table1 where B='b2' 
commit tran 

若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒

2）共享锁

在第一个连接中执行以下语句

begin tran 
select * from table1 holdlock -holdlock  人为加锁 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran 

在第二个连接中执行以下语句

begin tran 
select A,C from table1 where B='b2' 
update table1 set A='aa' where B='b2' 
commit tran

若同时执行上述两个语句，则第二个连接中的select查询可以执行
而update必须等待第一个事务释放共享锁转为排它锁后才能执行 即要等待30秒

3）死锁

--增设table2(D,E) 
D E 
d1 e1 
d2 e2

在第一个连接中执行以下语句

begin tran 
update table1 set A='aa' where B='b2' 
waitfor delay '00:00:30' 
update table2 set D='d5' where E='e1' 
commit tran

在第二个连接中执行以下语句

begin tran 
update table2 set D='d5' where E='e1' 
waitfor delay '00:00:10' 
update table1 set A='aa' where B='b2' 
commit tran

同时执行，系统会检测出死锁，并中止进程

 

参考：

https://www.php.cn/mysql-tutorials-123100.html

https://blog.csdn.net/softuse/article/details/121940804