MySQL基础系列:锁

1 锁

计算机协调多个进程或线程并发访问计算机资源的机制,锁保证数据并发访问的一致性，有效性，锁冲突也是影响数据库并发访问性能的一个重要因素，锁是MySQL在服务器层和存储引擎层的并发控制。
加锁是消耗资源的，锁的相关操作:获得锁，检测锁是否解除，释放锁。

2 锁种类

2.1 乐观锁

假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性，不能解决幻读。
每次取数据时，认为其他事务不会修改,所以不会上锁，但是，在更新时会判断其他事务是否更新这个数据，可以使用版本号等机制，乐观锁适用于多读的应用类型，这样可以提高吞吐量。

2.2 悲观锁

假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作
每次取数据都认为其他事务会修改数据,因此,每次取数据都会加锁,其他事务取数据时会出现阻塞,直到获取锁,即行锁,表锁,读锁,写锁

2.3 全局锁

flush tables with read lock(FTWRL)
对整个数据库加锁,加全局锁后的数据库处于只读状态,所有改变数据的操作(增,删,改)均无法进行,被阻塞
应用场景:全库逻辑备份
风险:
主库备份,备份期间,所有数据库相关业务(CURD)无法正常使用;
从库备份,备份期间,无法从主库同步binlog,主从延迟;
官方逻辑备份工具mysqldump使用–single-transaction时,会启动一个事务,确保拿到一致性视图,多版本并发控制(MVCC),保证备份过程数据正常更新.
全库只读不使用:set global readonly=true
原因:
某些系统中,readonly的值会被用于做其他逻辑,如判断主备库,修改global变量的方式影响过大
异常处理差异,如果执行FTWRL命令之后,若客户端发生异常断开,MySQL会自动释放全局锁,整个库回到正常更新的状态,若整个库设置为readonly后,若客户端发生异常,数据库一直保持readonly状态,导致整个库长期处于不可写状态,风险较高

2.4 表级锁

默认情况,表锁和行锁是自动获得,无需额外命令
特殊情况下,用户需要明确进行锁表或者进行事务控制,保证整个事务的完整性,需要使用事务控制和锁定语句完成.

SELECT * FROM tb_name WHERE id=1 FOR UPDATE;

2.5 全表锁

加锁:
lock tables talbe-name read
lock tables talbe-name write
取消锁:
unlock tables
客户端断开时,自动释放锁
lock tables限制其他线程的读写,同时显示本线程的其他操作对象
InnoDB支持行级锁,一般不使用lock tables控制并发,锁住全表,影响较大
表共享读锁(Table Read Lock),不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
表独占写锁(Table Write Lock),会阻塞其他用户对同一表的读和写操作;
默认情况下,写锁比读锁优先级更高,当一个锁释放时,这个锁会优先给写锁队列中等候的获取锁请求,然后再给读锁队列中等候的获取锁请求.

2.6 元数据锁(Meta Data Lock,MDL)

无需显式使用,访问一个表时会自动加上
保证读写的正确性
加MDL锁的情况:
对表做增删改查操作
当变更表结构
读锁之间不互斥,写锁之间互斥
MDL当事务提交才会释放
变更表结构时,会锁住数据的查询和更新操作,小心处理

2.7 行锁

共享锁：允许一个事务读取一行，阻止其他事务获取相同的数据集的排它锁；
排它锁：允许获得排它锁的事务更新数据,阻止其他事务获取相同数据集的共享读锁和排他写锁；
意向锁：实现行锁和表锁共存；
意向共享锁：事务给数据行加行共享锁,事务给数据行加共享锁前必须先获得该表的意向共享锁；
意向排它锁：事务给数据行加排它锁,事务给数据行加排它锁前,必须先获得该表的意向排它锁；

2.8 间隙锁(GAP)

使用范围条件，不使用相等条件检索数据,并请求共享或排他锁时，InnoDB会给符合条件的已有数据索引项加锁,对于键值在条件范围内但不存在的记录，称为"间隙"，InnoDB对间隙加锁,即间隙锁(Next-Key Lock)
使用范围条件检索并锁定记录时，InnoDB加锁机制会阻塞符合条件范围内键值的并发插入，造成严重的锁等待，因此实际场景中,尤其是并发插入较多的应用，尽量优化业务逻辑,用相等条件更新数据，避免使用范围条件。
配置数据：id为主键索引。

存在的间隙：
(-∞, 1] (1, 5] (5, 10] (10, 15] (15, +∞]

范围查询产生间隙锁：(5, 15]
左右条件之间以及右条件区间。

SELECT * FROM tb_name WHERE id BETWEEN 5 AND 10 FOR UPDATE;

空数据产生间隙：(5, 10]

SELECT * FROM tb_name WHERE id=6 FOR UPDATE;

作用:
防止幻读,满足相关隔离级别的要求
满足恢复和复制需要

2.9 表锁与行锁比较

表锁:开销小,加锁快,不会出现死锁,锁定范围广,发生锁冲突的概率最高,并发度最低
表锁如何避免死锁:一次性同时获取所有需要的锁以及总是按相同顺序获取表锁避免死锁
表级锁适合以查询为主,并发用户少,只有少量按索引条件更新数据的应用,如Web应用
行级锁:开销大,加锁慢,会出现死锁,锁范围小,发生锁冲突的概率最低,并发度最高
最大程度支持并发,同时带来了最大的锁开销;
InnoDB中,除了单个SQL组成的事务外,锁是逐步获得的,这决定了InnoDB可能发生死锁
行级锁只在存储引擎层实现,MySQL服务器层没有实现,行级锁更适合有大量按索引条件并发更新少量不同数据场景,同时又有并发查询的应用,如在线事务处理(OLTP)系统
页面锁:
开销和加锁时间在表锁和行锁之间,会出现死锁,锁范围在表级锁与行级锁之间,并发度一般.

2.10 加锁

InnoDB:
意向锁是InnoDB自动加的,无需用户干预
UPDATE,DELET,INSERT自动添加排它锁
普通SELECT,InnoDB不加任何锁
事务可以通过语句显式地给记录集添加共享锁或排它锁:
共享锁:select * from table-name where sentences lock in share mode
其他事务可以查询该记录,并且可以对该记录添加share mode的共享锁,如果当前事务需要对失去进行更新,会出现死锁
解析:将查找到的数据加上一个share锁,表示其他事务只能对这些数据进行简单的select操作,并不能进行DML操作
应用场景:确保查到的数据没有被其他事务正在修改,即确保查到的数据是最新的数据,并且不允许其他人修改数据,但自己同样不一定能修改数据,其他事务可能同样使用了共享锁
排它锁:Select * from table-name where sentences for update,其他事务可以查询该记录,不能对该记录添加共享锁或排他锁,而是获得等待获得锁
解析:相当于update效果,查询时,将对应索引查询的数据加排它锁
应用场景:保证自己查到的数据是最新数据,并且查到的数据只允许自己修改

2.11 死锁

相互持有锁,等待对方释放
事务A:持有id=2的写锁,修改id=1的数据
事务B:持有id=1的写锁,修改id=2的数据
A等待B释放id=1的写锁,
B等待A释放id=2的写锁
相互等待,无法释放,
避免死锁方案:
事务开始时,通过为预期要修改的行使用select *** for update,获取必要的锁,使这些行的更改语句在该操作之后执行的
事务中,如果要更新记录,应该直接申请足够级别的锁,即排它锁,而不应该申请共享锁或更新时再申请排它锁,因为这时当用户再申请排它锁是,其他事务可能已经获取相同记录的共享锁,造成锁冲突,甚至死锁
如果事务需要修改或锁定多个表,应该在每个事务中以相同的顺序使用加锁语句,应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,降低死锁机会
通过select *** lock in share mode获取行的读锁后,如果当前事务再需要对该记录进行更新操作,可能造成死锁
改变事务隔离级别

2.12 TODO

排他
意向共享
意向排他

---

【参考文献】
[1]https://my.oschina.net/iioschina/blog/2981137
[2]https://zhuanlan.zhihu.com/p/48269420