mysql的锁分为 全局锁、表锁 和 行锁。
全局锁:就是对整个mysql进行上锁。一旦Mysql被上全局锁,则数据库所有更新类语句、数据定义语句和修改语句事务均不可以执行。
对Mysql上全局读锁的语句:flush tables with read lock (FTWRL)
使用场景:做全表数据备份。
其实在做数据备份的时候,我们可以使用mysqldump -single-transaction 来生成一个可重复度读的视图,可重复读视图 + MVCC的支持,可以支持mysql还可以继续只是读写,比直接加全局锁好。但是single-transaction需要引擎支持可重复读事务。所以建议使用InnoDB,而不是MyISAM。
比较FTWRL 和 set global readonly = true
- readonly=true 可能在其他框架中被用来做业务判断,比如是主库还是备库。
- FTWRL如果异常,会释放锁。而global 数据设置,哪怕异常也会一直保持只读状态。
表锁
针对表级别的锁。使用lock tables t1 read, t2 write; 语法来指定需要锁的表加什么样的锁。
事实上,使用上面的语句给t1加读锁,给t2加写锁后,当前事务线程能且只能读t1表,和读写t2表。无法写t1表,也无法读t3表。
除了显示指定表锁外,我们执行MDL语句也会隐式获取表锁。
另外,当我们执行增删改查时,会加MDL读锁,执行MDL语句时会加MDL写锁。读锁之间不冲突,读写锁冲突。
sessionA | sessionB | sessionC | sessionD |
begin; | |||
select * from t limit 1; | |||
select * from t; | alter table t add f int;(blocked) | ||
select * from t;(blocked) |
当sessionA未提交的时候,由于sql会获取MDL读锁,sessionB也是获取读锁,不冲突,所以可以正常执行。但是sessionC要是MDL语句,会要求取写锁,但是sessionA已经先取了读锁,所以被阻塞住。sessionD此时由于sessionC加写锁,导致自己在加读锁时被阻塞住。
一个长事务可能导致表在MDL时,整个表都不可用了,全部给阻塞住了。
解决办法可以使用 alter table t wait N add f int; 通过指定等待时间,如果超时仍然未加锁,则异常再让人重试。
行锁
行锁是引擎级别的实现,MYISAM是不支持行锁的。这也是它被InnoDB取代的重要原因。
InnoDB中,行锁是在需要的时候才加上,但是并不是在不需要的时候释放,而是在事务提交的时候才释放。这就是两阶段锁协议。
根据以上信息,如果我们事务中需要锁多个行,要把最有可能锁冲突的语句往后放。
举例:一个电影院售卖电影票流程。
1、从A账户扣除电影票价格
2、往电影院账户B添加电影票价格
3、记录一条日志。
如果这个时候还有用户C也在购买电影票,那么步骤2就会被2个事务更新,此时,流程顺序 1-3-2会更优。
死锁和死锁检测
死锁概念无须多述。InnoDB关于死锁的处理有2个办法:
1、通过锁超时来处理,默认超时50s,超过此时间事务还没提交,则直接回滚。通过参数:
innodb_lock_wait_timeout 来设置超时时间。
2、通过死锁检测,判断是否出现死锁,如果出现,则直接选一个死锁线程结束事务,释放资源。
通过参数: innodb_deadlock_detect = on 来开启检测。
死锁检测的复杂度是O(n)的。如果1000个线程同时对一个数据加锁,则死锁检测需要100W次。这就会导致Mysql看似连接不多,但是CPU已经跑满的状态。