MySQL入门教程（12）数据库锁的介绍与相关配置

一、MySQL锁的作用

数据库锁主要用于解决并发问题，当并发操作发生时，数据库依靠锁来控制这些并发请求对资源（锁是针对资源而非事务）的访问规则，因为被上锁的资源不会被其他事务修改，因为可以保证事务之间的隔离性与一致性 

二、锁的分类与区别

1、按锁的粒度分为全局锁、表锁、行锁

· 全局锁

对整个数据库加读锁，当需要让整个数据库处于只读状态时才会加全局读锁，比如需要对使用非InnoDB引擎的数据库做全库逻辑备份。加锁后其他线程的增删改语句、建表、修改表结构语句都会被阻塞，只能进行查询操作，当上锁的客户端断开时会自动释放全局锁

mysql > flush tables with read lock

· 表锁

对整个表进行上锁，表锁开销小、加锁速度快，不会出现死锁，但是发生冲突概率高，导致数据库并发低。用EXCEL举例的话就是用户A正在编辑的表格文件，用户B是无法编辑的。通常数据库引擎不支持行锁的情况下才会用到表锁。适用于查询为主、少量更新的应用，如WEB应用（需要注意全表扫描行为其实也是属于表锁，只不过数据库Server层进行了过滤优化）。

mysql > lock tables t1 read  #给t1表上读锁，自己和其他事务可读，但是都不可写
mysql > unlock tables  #释放锁

mysql > lock tables t1 write ; #给t1表上写锁，仅上锁的事务可读可写
mysql > unlock tables  #释放锁

· 元数据锁

表锁的另一种形式，在对表进行DDL操作时，如果有其他事务对该表并发执行DML操作，这些后续的DML都会被阻塞，反过来先有DML再DDL也会有阻塞情况，这都是因为MDL元数据锁的存在。该锁不需要显式使用，事务执行UPDATE、INSERT等操作修改数据时都会对表加MDL写锁，而事务SELECT读取数据时也会自动加MDL读锁，让查询语句未结束无法对数据进行变更。MDL的存在避免DDL操作影响了事务的正常执行，还可以控制不同线程之间事务的执行顺序，避免主从数据不一致。不过也因为MDL的存在，有时候给一个小表进行变更的时候会导致数据库挂掉，如下图：

1、session A进行查询，此时会对表加一个MDL读锁

2、session B进行查询，需要的也是MDL读锁，因此不会互斥，可以正常执行
3、session C修改表结构会被卡住，因为session A的MDL读锁还没有释放，而session C需要MDL写锁，二者互斥
4、后续要对该表申请MDL读锁的请求全部会被session C阻塞，因为所有对表的增删改查操作都需要先申请MDL读锁，自然都被锁住，这个表完全不可读写了

5、如该表查询语句频繁且客户端有重试机制，数据库线程很快就会撑满

· 意向锁

意向锁分为意向共享锁（IS）和意向排他锁（IX），它们都是由存储引擎自行维护的，无法手动去操作意向锁。事务在加共享锁或者排他锁的时候，会自动在该表上添加对应的意向共享锁或意向排他锁。当有其他事务产生时，存储引擎会先获取该数据行所在表上对应的意向锁，这样实现高效的锁冲突检测。

举例说明，比如t1表有10万行数据，某事务只修改了其中一行，这个时候除了被修改的一行会上行锁以外，还会在表上加上意向排他锁。这样后续的事务想对该表进行更新需要上锁的时候只需要先看该表有没有意向锁就可以了，如果没有意向锁的话就需要遍历每一行才知道这个事情

· 行锁

InnoDB引擎默认使用行级锁，行锁相对表锁来性能开销更大，但是可以最大程度地支持并发，冲突概率较低，有可能出现死锁，适合并发度高的业务。用EXCEL举例的话就是用户A正在编辑表格第9行，那么只有第9行是其他用户无法编辑的。InnoDB 的行级锁是通过对索引项加锁来实现的。当查询使用索引时，InnoDB 能够高效地加行级锁。如果查询未使用索引，InnoDB 会进行全表扫描并对扫描到的行逐行加锁，而如果是写操作，如果没有使用索引并且影响的数据行数较多（接近全表），InnoDB为了简化锁管理，会升级会表锁。

UPDATE 和 DELETE 操作都会为数据加行锁，且锁的类型都是独占锁(X型锁)，而普通的 SELECT 语句属于快照读不会对记录加锁，如果要在查询时对记录加行级锁可以使用锁定读的方式实现

#对读取的记录加共享锁(S型锁)
select ... lock in share mode;
select ... lock for share;  # 8.0新增语法

#对读取的记录加独占锁(X型锁)
select ... for update;

· 页锁：开销和并发都介于表锁和行锁之间，会出现死锁

2、按数据操作的类型分为读锁和写锁

· 读锁

读锁也可称为共享锁、S锁。当读取一行数据时，为了防止数据被其他事务修改而产生数据不一致的情况，此时就需要对该数据添加S锁。读锁和读锁之间是兼容的，例如事务1在某一行数据上加了共享锁，另外一个事务2也是可以正常对改行进行读取的，因为读取数据的操作并没有改变数据本身，这种情况锁和锁之间是兼容的

· 写锁

写锁也可称为排他锁、X锁、独占锁。通常被修改的数据都会上写锁，这也是为了防止其他事务同时对数据进行修改或者查询。写锁与写锁以及写锁与读锁之间都是互斥的，也就是说必须等当前上锁的事务结束后才能继续上其他锁

SELECT * FROM t1 where id=1 FOR UPDATE  #使用FOR UPDATE对数据加X锁

· 间隙锁（GAP锁）

GAP只会在RR隔离级别才会产生，通过对一个被事务修改的数据的空隙上锁的手段来防止幻读，但是会让死锁的机率变高。这个间隙是根据表中数据的实际情况来进行划分的，比如事务1执行“update test set num=10 where num < 10”，如果表中存在1,3,5,10这几个值，那么被锁的范围应该是1-3，3-5，5-10，这时执行insert into test values (8)是会被锁住的。

假设有表数据如下图

SELECT * FROM t1 WHERE id > 15 FOR UPDATE;  
INSERT INTO t1 VALUES (20)  #这个被锁很好理解，>15区间
INSERT INTO t1 VALUES (13)  #也会被锁，因为命中了区间为(11,+∞)的GAP锁

3、从对待锁的态度划分（并不是真正的锁，只是锁的设计思想）

· 悲观锁：悲观锁在处理事务的时候认为数据大概率会被修改，所以每次操作都会先上锁，此时其他事务会被阻塞。行锁、表锁、写锁、读锁这些在操作前先上锁的行为都属于悲观锁。悲观锁适用于写操作频繁的场景，如果有大量的读操作，每次读取都会加锁，降低了系统的吞吐量

· 乐观锁：只有在进行数据提交的时候才会判断是否需要加锁。乐观锁不采用数据库自审的锁机制，通常通过程序来实现。乐观锁适用于读操作频繁的场景，如果有大量的写操作，则冲突的可能性会变高

三、MySQL死锁问题

1、MySQL死锁的产生

首先长时间的锁等待不等于死锁，偶尔的死锁也不可怕，频繁死锁就需要关注了，理论上来说并发度越高越容易出现死锁，而并发往往不是能自己控制的。所以建议如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。避免出现死锁后如果某表查询语句比较频繁且客户端有重试机制，把数据库线程撑爆。

死锁现象模拟：

# 事务A和事务B在互相等待对方释放锁，然后形成死锁情况

# 会话A：关闭自动提交事务，修改某行数据但并不提交
mysql> SET AUTOCOMMIT = off;
mysql> START TRANSACTION;
mysql> UPDATE worker SET type = 'B' WHERE id = 1;

# 会话B：关闭自动提交事务，修改某行数据但并不提交
mysql> SET AUTOCOMMIT = off;
mysql> START TRANSACTION;
mysql> UPDATE worker SET type = 'A' WHERE id = 2;

# 会话A：更新id=2的记录，此时事务就已经卡住了，因为会话B还没有提交对该行的修改
mysql> UPDATE worker SET type = 'A' WHERE id = 2;

# 会话B：更新id=1的记录，出现了死锁，MySQL报错，并让重启事务
mysql> UPDATE worker SET type = 'B' WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try resta rting transaction

# 会话A：更新id=2的记录成功，这是因为会话B出现死锁被KILL了，所以会话A才能执行成功，但是也经历了很长的锁等待时间

2、MySQL对于死锁的处理

当出现死锁后，如果不回滚其中一个事务就无法打破锁，MySQL数据库通过死锁检测和超时来解决死锁问题，当检测到有事务出现循环依赖的时候会返回一个错误信息或者超过锁等待时间后终止查询。为了避免死锁，最好的方式一是调整业务逻辑，避免一些会长时间持有锁的事务在前面执行。二是将大事务拆成小事务，缩短每个事务锁的时长；三是调整事务隔离级别，比如RR模式下存在的GAP锁就可能产生意料之外的死锁

· 等待，直到超时

设置锁超时时间，减少无意义的等待。但是也不能直接设置成一个很小的值，比如1S，这样虽然出现死锁的时候很快就可以解开，但如果是简单的锁等待也会被误伤

lock_wait_timeout = 1800  #默认是1年，非常不合理，建议设置为1800秒足够
innodb_lock_wait_timeout= 10  #行锁超时时间，默认50秒，建议调低

· 使用死锁检测自动处理

开启死锁自动检测机制，当事务出现死锁的时候触发，可以主动回滚死锁链条中代价较小的事务，让其他事务得以继续执行。死锁检测会消耗一定的CPU资源，复杂度是O(n)级别，比如100个并发更新同一行数据，那么检测次数就是100*100=1万次，所以如果是在一些经常出现死锁的业务场景，可以尝试关掉死锁的自动检测，让事务排队处理，节约CPU资源。

innodb_deadlock_detect=on  #默认已开启这个逻辑

四、MySQL 锁的分析与排查

1、查询当前数据库中是否存在表锁

mysql > show open tables where in_use >0 ;

2、查看事务锁的情况

在MySQL 5.7或之前版本中，通过information_schema系统库中的INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS三张表可以获得当前数据库事务锁信息，MySQL 8.0 开始使用 PERFORMANCE_SCHEMA.DATA_LOCKS 替代 INNODB_LOCKS，PERFORMANCE_SCHEMA.DATA_LOCK_WAITS 替代 INNODB_LOCK_WAITS。

· innodb_trx

查看当前正在执行的事务

· INNODB_LOCKS

如果没有记录返回，则当前没有事务持有锁，如果有记录则说明有事务对表或行进行了锁定，配合 lock_trx_id，到 INNODB_TRX 表中可以查看该事务的开始时间和执行的 SQL 语句，分析是否为长时间未提交的事务

· innodb_lock_waits

查看当前正在等待的事务，也就是因为锁而被阻塞的事务

SELECT * FROM information_schema.innodb_trx ;     
SELECT * FROM information_schema.innodb_locks ;    #查看当前存在的锁 
SELECT * FROM information_schema.innodb_lock_waits;   
SELECT * FROM sys.innodb_lock_waits          #将information_schema.innodb_lock_waits的信息进行了整理，方便用kill命令处理