数据库锁解析

目录

• 1. 锁的概念
  • 1. 锁的定义
  • 2. 锁的解释
• 2. 锁的分类
  • 1. 按锁的功能来划分
  • 2. 按锁的概念来划分
• 3. 锁的关键字
• 4. 死锁
  • 1. 死锁实战
  • 2. 死锁原理
  • 3. 死锁解决方案
• 5. 无锁查询
  • 1. 举例实战
  • 2. 原理解析
• 7. 何时加锁
• 8. 锁的粒度
  • 1. 定义
  • 2. 页锁
  • 3. 行锁
  • 4. 表锁
• 9. 锁与事物隔离级别的优先级
• 10. 锁类型比较
  • 1. HOLDLOCK与TABLOCK
  • 2. TABLOCKX与XLOCK
• 11. 锁的超时等待
• 12. 各种锁的兼容关系表
• 13. 如何提高并发效率
  • 1. 悲观锁（Pessimistic Lock）
  • 2. 乐观锁（Optimistic Lock）
• 14. 参考

1. 锁的概念

1. 锁的定义

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

2. 锁的解释

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

2. 锁的分类

1. 按锁的功能来划分

2. 按锁的概念来划分

1. 共享锁(Shared lock)
2. 更新锁(Update lock)
3. 排它锁(独占锁，Exclusive Locks)
   其它事务既不能读，又不能改排他锁锁定的资源
4. 意向锁(Intent Locks)
   意向锁加在表级
5. 计划锁(Schema Locks)
   DDL语句都会加Sch-M锁，该锁不允许任何其它session连接该表
6. 间隙锁（Gap Lock）
   锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。
7. Next-key 锁
   行锁与间隙锁的综合。

Next-key 锁：行锁与间隙锁的综合。

3. 锁的关键字

-- 共享锁与共享锁可以同时在同一资源上，共享锁不阻止其它session同时读资源，但阻止其它session update
SELECT * FROM table WITH (HOLDLOCK) /*共享锁*/

-- 共享锁和更新锁可以同时在同一个资源上
SELECT * FROM table WITH (UPDLOCK)  /*更新锁*/ 

-- 排他锁与更新锁是不兼容的，它们不能同时加在同一个资源上
SELECT * FROM table WITH (XLOCK) WHERE id=10 /*排它锁*/  

SELECT * FROM table WITH (ROWLOCK) WHERE id=10; /*行锁*/ 

SELECT * FROM table WITH (TABLOCKX) /*大容量更新锁*/ 

SELECT * FROM table WITH (XLOCK,ROWLOCK) WHERE id=10; /*锁的组合使用*/ 
/*XLOCK 本身是锁住数据行的，TABLOCKX是锁住整张表*/

SELECT * FROM table WITH (NOLOCK)   /*不加锁，当一个事务回滚后，出现脏数据*/   
SELECT * FROM table WITH (READPAST)  /*忽略掉加锁的数据（行数据，页数据）*/

4. 死锁

1. 死锁实战

-- 事务1：
START TRANSACTION;
SELECT * FROM table WHERE id  = 10(HOLDLOCK); -- (HOLDLOCK意思是加共享锁，直到事物结束才释放)
UPDATE table SET name ='test01' where id = 10;

-- 事务2：
START TRANSACTION;
SELECT * FROM table WHERE id  = 10(HOLDLOCK);
UPDATE AppLog SET name = 'test02' where id = 10;

2. 死锁原理

1. 事务1和事务2同时执行 SELECT，事务1对记录加了共享锁，事务2对记录也加了共享锁
2. 当事务1 SELECT 执行完毕，准备执行UPDATE的时候，根据锁机制，事务1的共享锁需要升级到排他锁才能执行接下来的UPDATE
3. 在升级排他锁前，必须等待记录上的其它共享锁释放，但是因为共享锁只有等事务结束后才释放。
4. 事务2的共享锁不释放而导致事务1等待，同理，这时因为事务1的共享锁不释放而导致事务2等待，从而导致死锁发生。

3. 死锁解决方案

1. 添加更新锁(推荐)

-- T1:
START TRANSACTION;
select * from table(updlock) ; -- (加更新锁)
update table set column1='hello';

--T2:
START TRANSACTION;
select * from table(updlock);
update table set column1='world';

1. 更新锁指当前事务直接获得从共享锁（用来读）升级到排他锁（用来更新）的权限。同一个资源只能有一个更新锁获此权限。
2. T1执行select，加更新锁。
3. T2运行，准备加更新锁，但发现已经有一个事务获得更新锁，事务T2只能等待。
4. 当有其他事务需要查询table表中的数据时，并不会因为T1的select在执行就被阻塞，照样能查询，因此，提高了效率。

2. 添加排他锁

-- T1:
START TRANSACTION;
select * from table(xlock); -- (xlock意思是直接对表加排他锁)
update table set column1='hello';

-- T2:
START TRANSACTION;
select * from table(xlock);
update table set column1='world';

1. 当T1的select 执行时，直接对表加上了排他锁
2. T2在执行select时，需要等T1完全执行完才能执行。排除了死锁发生。
3. 但当其他事务过来想执行一个查询语句时，也因为排他锁的存在而不得不等待。在大并发情况下，这种方式的性能就显得有些低效。

5. 无锁查询

1. 举例实战

-- 打开两个查询窗口，其中一个执行下面语句：
CREATE TABLE a
(
    id INT ,
    name NVARCHAR(20)
);

--开启一个事务，而不提交也不回滚，此时insert 语句产生的排它锁是不会释放的
START TRANSACTION;
INSERT INTO a VALUES ('1','a'); 

-- 在另一个窗口中执行：
--无锁查询，会查出结果为1
select COUNT(*) from a with(NOLOCK);
--忽略所有有锁的记录，此时为0
select COUNT(*) from a with(READPAST); 

2. 原理解析

1. 在另一窗口执行"select * from a;"，此时是查不出结果的，会无限地等待下去
2. 因为排它锁未释放，默认查询的共享锁与之不兼容，所以就一直等待排它锁的释放，才会返回结果，
3. 即使表中已有许多数据，而排它锁只锁了一条记录，但是，查询语句也要等待这一条记录的锁的释放，才会返回结果。
4. 以上是人工手动设置的因为排它锁未释放而导致的死锁（不是相互等待，而是一方无尽的等待！）

7. 何时加锁

-- 可以通过hint手工强行指定，但大多是数据库系统自动决定的

-- T1:    
START TRANSACTION;
update table set column1='hello' where id=1;

-- T2:    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  -- 事物隔离级别为允许脏读
go
select * from table where id=1;

1) T1执行，数据库自动加排他锁
2) T2执行，数据库发现事物隔离级别允许脏读，便不加共享锁。不加共享锁，则不会与已有的排他锁冲突，所以可以脏读。

8. 锁的粒度

1. 定义

锁的粒度就是指锁的生效范围，就是说是行锁，还是页锁，还是整表锁

2. 页锁

-- T1:    
select * from table (paglock);

-- T2:
update table set column1='hello' where id>10;

1) T1执行时，会先对第一页加锁，读完第一页后，释放锁，再对第二页加锁，依此类推。
2) 假设前10行记录恰好是一页(当然，一般不可能一页只有10行记录），那么T1执行到第一页查询时，并不会阻塞T2的更新。

3. 行锁

-- T1:
select * from table (rowlock);

-- T2:
update table set column1='hello' where id=10;

1) T1执行时，对每行加共享锁，读取，然后释放，再对下一行加锁;
2) T2执行时，会对id=10的那一行试图加锁，只要该行没有被T1加上行锁，T2就可以顺利执行update操作。

4. 表锁

-- T1:
select * from table (tablock);

-- T2:
update table set column1='hello' where id = 10;

1) T1执行，对整个表加共享锁，T1必须完全查询完，T2才可以允许加锁，并开始更新。

9. 锁与事物隔离级别的优先级

-- T1:    
GO
       SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
       START TRANSACTION;
       SELECT * FROM table (NOLOCK);
GO

-- T2:
update table set column1='hello' where id=10

1) T1为事务隔离级别最高的串行锁
2) 数据库系统本应对后面的select语句自动加表级锁，但因为手工指定了NOLOCK，所以该select语句不会加任何锁，所以T2也就不会有任何阻塞。

10. 锁类型比较

1. HOLDLOCK与TABLOCK

• TABLOCK：对表加共享锁，只要statement不完成，共享锁不释放。

-- T1:
start transaction;
select * from table (tablock);

-- T2:
start transaction;
update table set column1='hello' where id = 10

1) T1执行完select，就会释放共享锁，然后T2就可以执行update。

• HOLDLOCK：对表加共享锁，且事物不完成，共享锁不释放。

-- T1:
start transaction;
select * from table (holdlock);
   
-- T2:
start transaction;
update table set column1='hello' where id = 10;

1) T1执行完select，共享锁仍然不会释放，仍然会被hold(持有），T2也因此必须等待而不能update。
2) 当T1最后执行了commit或rollback，即当前T1事务结束，T2才取得执行权限。

2. TABLOCKX与XLOCK

• TABLOCKX：对表加排他锁

start transaction;
select * from table(tablockx);

1) 必须整个事务执行完成（即commit或rollback后）才会释放排他锁。

• XLOCK：加排他锁

-- 对page加排他锁，而TABLELOCX不能这么用。
select * from table(xlock paglock);

-- xlock还可以这样使用
select * from table(xlock tablock);
-- ||
select * from table(tablockx);

11. 锁的超时等待

• SET LOCK_TIMEOUT 4000 用来设置锁等待时间，单位是毫秒，4000即等待4秒。
• 可以用select @@LOCK_TIMEOUT查看当前session的锁超时设置。-1 意味着永远等待。

-- T1: 
start transaction;
update table set column1='hello' where id = 10;

-- T2: 
set lock_timeout 4000;
select * from table where id = 10;

1) T2执行时，会等待T1释放排他锁，等了4秒钟
2) 如果T1还没有释放排他锁，T2就会抛出异常： Lock request time out period exceeded

12. 各种锁的兼容关系表

13. 如何提高并发效率

1. 悲观锁（Pessimistic Lock）

1. 原理

1. 利用数据库本身的锁机制实现。
2. 事务每次去操作数据的之前，都假设有其他事务会修改需要访问的数据，所以在访问之前都要求上锁。
3. 行锁、表锁、读锁、写锁等，都是在做操作之前先上锁，因此，在整个事务过程中，数据都处于锁定状态。

2. 优化

可以根据具体业务情况综合使用事务隔离级别与合理的手工指定锁的方式比如降低锁的粒度等减少并发等待。

2. 乐观锁（Optimistic Lock）

1. 原理

1. 利用程序处理并发。
2. 事务每次去操作数据之前，都假设其他事务不会修改这些需要访问的数据，所以在访问之前不要求上锁。
3. 在进行更新修改操作的时候会判断在访问的期间有没有其他人修改数据。

2. 实现

1. 对记录加版本号.
2. 对记录加时间戳.
3. 对将要更新的数据进行提前读取、事后对比。

14. 参考

https://www.cnblogs.com/xingzc/p/5987095.html
https://www.cnblogs.com/zhouqianhua/archive/2011/04/15/2017049.html

数据库锁解析

标签：sch   技术   优先级   period   理解   连接   读取   sel   完全