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

一、MySQL锁的作用

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

二、锁的分类与区别

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

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

mysql > flush tables with read lock

· 表级别锁（又分表锁和元数据锁（metadata lock）、意向锁）

表锁：对整个表进行上锁，表锁开销小、加锁速度快，不会出现死锁，但是发生冲突概率高，导致数据库并发低。用EXCEL举例的话就是用户A正在编辑的表格文件，用户B是无法编辑的。通常数据库引擎不支持行锁的情况下才会用到表锁。适用于查询为主、少量更新的应用，如WEB应用（需要注意全表扫描行为其实也是属于表锁，只不过数据库Server层进行了过滤优化）。当程序里有locak tables这样的语句需要注意更换引擎或者代码，客户端断开或执行unlock tables语句可以释放表锁。

mysql > show open tables where in_use >0 ;  #查看数据库当前是否存在表锁

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、如该表查询语句频繁且客户端有重试机制，数据库线程很快就会撑满

意向锁：意向锁是存储引擎自己维护的，无法手动去操作意向锁。事务在加共享或者排他锁之前，InnoDB会先获取该数据行所在表对应的意向锁进行快速的锁冲突检测。比如t1表有10万行数据，某事务只修改了其中一行，这个时候除了被修改的一行会上行锁以外，还会在表上加上意向排他锁。这样后续的事务想对该表进行更新需要上锁的时候只需要先看该表有没有意向锁就可以了，如果没有意向锁的话就需要遍历每一行才知道这个事情

· 行锁：InnoDB引擎默认使用行级锁，行锁相对表锁来性能开销更大，但是可以最大程度地支持并发，冲突概率较低，有可能出现死，适合并发度高的业务。用EXCEL举例的话就是用户A正在编辑表格第9行，那么只有第9行是其他用户无法编辑的。文件行级锁是通过给索引上的索引项加锁来实现的，只有通过索引查询的数据才会使用行级锁，否则使用表锁。即一个SQL如果没用使用索引而使用全表遍历时，行锁会升级为表锁。UPDATE 和 DELETE 操作都会加行级锁，且锁的类型都是独占锁(X型锁)，而普通的 SELECT 语句属于快照读不会对记录加锁，如果要在查询时对记录加行级锁可以使用锁定读的方式实现

#对读取的记录加共享锁(S型锁)
select ... lock in share mode;

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

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

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

· 读锁：读锁也可以被称作共享锁、S锁。被上读锁的数据可以被其他事务读，但是不能写；执行上读锁的客户端只能读，不能写，也不能读其他表。S锁和S锁之间兼容，可以同时上锁

· 写锁：都写锁也可以被称作排他锁、X锁、独占锁。通常被修改的数据都会上写锁。被上写锁的数据没有被完成前，阻断其他事务对该数据进行读写，直到上锁的事务完成并释放锁。这样就可以确保数据不会同时被多个事务修改。X锁和S锁或者其他X锁是不兼容的，只要一个数据存在其中一种锁，另外一种锁就无法上锁。虽然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)是会被锁住的

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

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

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

三、死锁问题

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万次。

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