一文读懂常用mysql锁

Mysql中的锁

锁机制是用来解决资源争用的常用手段。对某个粒度的资源加锁，访问资源资源需要先得到锁。

Mysql的锁按锁定粒度分为三类：全局锁（整个数据库实例加锁），表级锁（对表加锁）和行锁（对数据行加锁，引擎层实现）

全局锁

对整个数据库实例加锁，Flush tables with read lock (FTWRL) 对全局加读锁，使得全局可读。

释放全局锁命令如下：unlock tables;此外，在客户端异常断开后，全局锁会自动释放。

应用场景主要是逻辑库数据备份（mysqldump)

表级锁

mysql表级别的锁分为表锁 、元数据锁（meta data lock MDL）、意向锁（InnoDB,后面补充讲解）。

表锁

显式表锁可以锁住表。实际应用场景需要对整个数据表加锁的情况并不多见，没有行锁可用时的方案。

按照锁定的目的分为读锁和写锁。读锁用于读取数据，写锁用于更改数据。

加锁语法lock tables … read/write

解锁语法 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。

当innodb引擎支持更细致的行锁时，很少使用表锁

元数据锁（MDL）

MDL作为另一种表锁，不需要手动加，而是mysql自动加。目的是为了保证数据读写的正确性。场景比如，select一个表的同时有一个删除表字段语句执行，如果允许并发那么查询出的数据应该有没有这个表字段呢？答案是不能允许执行。这就是MDL的设计目的。

对一个表做增删改查时，加MDL读锁；对表做结构变更操作时，加MDL写锁。

• 读锁之间不互斥，也就是可以多个线程同时对数据进行增删改查
• 读锁与写锁，写锁与写锁之间是互斥的，也就是说当数据结构变更时，是无法执行增删改查或者其他数据结构变更的

行锁

行锁是在引擎层实现的，innodb支持，myisam不支持

行锁，就是对数据记录的行加锁。事务A更新了一行，事务B要更新这行时则需要等待A执行完成.

共享锁与排他锁

行锁同样分为共享锁（shared lock 简称s锁）和排他锁(exclusive lock简称x锁)

• 共享锁用于读取时加锁，可以共享，也就是事务T1加了读锁，T2也可以加读锁
• 排他锁用于更新数据是加锁，是排他的，也就是事务T1加了读锁，则T2必须等待释放后才能获取锁

mysql默认会加锁，不需要显式指定。显示指定的语法为：

# 共享锁（S）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
# 排他锁（X) 
SELECT * FROM table_name WHERE ..id=1 FOR UPDATE
# 排他锁解锁
UPDATE ...set... where id=1

注意：
1.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
2.两个事务不能锁同一个索引
3.insert ，delete ， update在事务中都会自动默认加上排它锁。

两阶段锁协议

在innodb中，行锁是在需要时加的，但是要在事务结束后才释放，这就是两阶段锁协议。相对应的，如果单阶段协议，则在事务一开始时申请所有的锁。

举个例子，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

1. 从顾客 A 账户余额中扣除电影票价；

2. 给影院 B 的账户余额增加这张电影票价；

3. 记录一条交易日志。

也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。

当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

死锁

上述方案，但是仍然不能避免死锁，当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。这里我用数据库中的行锁举个例子。

T1 T2

从A账户扣钱 给B账户加钱

给B账户加钱 从A账户扣钱

这个事例就是死锁。解决方式是

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。默认配置式50s，这个时间对于在线业务也是非常长的。但也不能设置的过小，因为误伤就大了。

  show global variables like 'innodb_lock_wait_timeout'; # 50
  

• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。但是死锁检测的性能消耗不能小看。每次加锁时，需要对所有依赖的线程作检测，看是否有锁住资源，然后再来判断是否为死锁。

  show global variables like 'innodb_deadlock_detect'; # on
  

当有大量进程同时更新一行（热点账户）时，死锁检测会造成大量的问题，死锁检测会消耗大量的CPU，怎么解决呢？一种是关掉死锁检测；另一种是控制并发度，这需要在中间层或者在数据库源码层完成；还有就是将逻辑的一行换成多行。将并发压力就降低了，这种也是热点账户问题的常用解决方案。

下面我们来造一个死锁并观察处理结果。

创建movie表的ddl如下，注意一定是innodb引擎

DROP TABLE IF EXISTS `movie`;
CREATE TABLE `movie` (
  `movie_id` int(11) NOT NULL AUTO_INCREMENT,
  `movie_name` varchar(256) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`movie_id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

造死锁语句：

1） 开启一个客户端（Navicat的一个查询）

# 客户端1 
# 打开事务，必须要有这句，否则事务自动提交就没有效果了
begin;
# 更新id为1的记录，根据行锁规则，修改时自动锁定该记录
update movie set movie_name='测试1' where movie_id=1;
# 睡眠30秒，此时持有id=1记录的锁，睡醒后即将请求id=2的锁
select sleep(30) from dual;
# 请求id=2的锁
update movie set movie_name='测试2' where movie_id=2;

2. 打开另一个客户端

# 客户端2

begin;
# 持有id=2的锁
update movie set movie_name='测试222' where movie_id=2;
select sleep(30) from dual;
# 请求id=1的锁
update movie set movie_name='测试111' where movie_id=1;

这两个语句块同时执行，睡眠结束后会产生死锁，触发innodb的死锁检测，客户端2会报错：“SQL错误1213:Deadlock found when trying to get lock;try restarting transaction”

意向锁-表级与行级协调

表锁和行级锁可能存在冲突情况，比如事务T1申请一个行级排他锁，那么事务T2申请一个表级写锁会如何呢？如果会成功则违反了行级排他锁，其他事务无法修改该行的规定；
T2加锁可以先循环所有行判断是否有行锁，然后再决定是否加锁成功，但是这样效率极低
为何协调表级锁和行级锁，InnoDB有了意向锁的概念，注意意向锁是表级锁。
当行级锁加了共享锁，则自动加上意向共享锁（IS）；当行级锁加上排他锁则自动加上意向排他锁（IX）。
意向锁的作用是协调表级锁与行级锁。意向锁之间是兼容的，但是与其他表锁不兼容

	共享锁（S)	排他锁（x)	意向共享锁（IS）	意向排他锁（IX）
共享锁（S)	兼容	冲突	兼容	冲突
排他锁（x)	冲突	冲突	冲突	冲突
意向共享锁（IS）	兼容	冲突	兼容	兼容
意向排他锁（IX）	冲突	冲突	兼容	兼容

总结

本文讲述了mysql的锁分类，下面总结下提纲

• 全局锁 FTWRL 对整个数据库实例加锁
• 表级锁
  • 表锁 lock table read write / unlock
  • 元数据锁 MDL
    • 读锁：数据增删改查时加，不与其他冲突
    • 写锁 （ 与其他锁排斥）： 数据结构变更时加写锁
  • 意向锁，当加行锁时会加意向锁，联动的方式与表锁协调
• 行锁
  • 共享锁：读取数据时加行锁
  • 排他锁： 数据库数据增删改时默认加排他锁。可以通过 select … for update方式显示加锁

无论你在学习上有任何问题，重庆蜗牛学院欢迎你前来咨询，联系QQ：296799112