MySQL的锁
锁的种类
1.全局锁
- FTWRL
顾名思义,对整个数据库实例加锁。
FTWRL : MySQL提供的对数据库实例加全局锁的方法 Flush tables with read lock
对应的释放锁:UNLOCK TABLES
这个命令,让数据库实例处于只读状态,以下操作会被锁住(阻塞),数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事物的提交语句。
场景:一般锁全库是用来做全库的逻辑备份用的。 - mysqldump –single-transaction
这种做法让全库处于只读状态,一般情况不使用这种方式。
mysqldump –single-transaction:
在做全库逻辑备份时,可以使用MySQL逻辑备份工具mysqldump,参数是 –single-transaction
它的作用是启动一个事物,拿到一个视图,为当前执行命令时的全库的视图,这时数据库还可以正常运行,备份的时候使用这个视图里的数据。
mysqldump –single-transaction 虽然有好处,也有它的限制,由于要用到事物,所以只有支持事物的数据库引擎可以使用 比如 InnoDB,像MyISAM这种引擎,如果要备份,还是需要使用FTWRL。 - set global readonly=true
让数据库处于只读状态。
这种方式也可以锁全库,但是比较与FTWRL,更危险。因为FTWRL在客户端异常断开时,会撤销全局锁。
但是set global readonly 这种方式,还会让数据库处于只读状态。
2.表级锁
- 表锁
lock tables ‘table_name’ read/write
类似于FTWRL,unlock tables主动释放。也可在客户端断开时自动释放。
两个线程同时访问两张表,A和B。
A执行 lock tables t1 read,t2 write;
这时
A线程访问 t1:两种情况,读 - 无阻塞,写 - 阻塞。
A线程访问t2:一种情况,读/写 - 无阻塞。
这时B线程访问t1:两种情况,读 - 无阻塞,写 - 阻塞。
B线程访问t2: 一种情况,读/写 - 阻塞。
这种表锁一般用于不支持事物的引擎。 - 元数据锁 MDL(meta data lock)
不需要显示声明,访问表的时候会自动加上。
MDL作用是防止DDL和DML并发的冲突。
比如A线程,遍历一张表,遍历一遍,另一个事物B来修改表结构,那A事物前后查出的数据结构就会不一致。
对表的增删改查 加 MDL读锁。
对表的字段进行修改 加MDL写锁。
读/读之间不互斥,读/写之间互斥,写/写之间互斥。
虽然不需要显示的加MDL锁,但还是需要注意一些地方。
比如这种情况,4个事物,其中 A C D是查询语句,B是修改表结构语句。由于B被锁住,后续的读请求,也会被锁住,如果客户端有超时重试机制的话,可能会造成MySQL中启动大量线程。
3.行锁
InnoDB支持行锁。
一个事物中,行锁是在SQL语句需要执行的时候加上,在事物提交后释放锁。
这里的B事物会被锁住,等待A事物提交。
所以在开发的时候,一个事物中,尽量把更新频率高的表操作放在事物最后。减少锁冲突时间。
死锁检测
一种场景:
A事物 → set age = 1 where id = 1; set age =1 where id =2;
B事物 → set age=2 where id=2; set age=2 where id=1;
这两个事物就有可能出现死锁。
两种凡是解决死锁;
- 死锁等待
innodb_lock_wait_timeout,超过时间结束事物。默认50s。
这种意味着死锁时要等50秒才能解决。 - 死锁检测
MySQL发现死锁,干掉一个事物,让其他的继续执行,innodb_deadlock_detect设置为on。默认开启。
每个新来的被阻塞的线程,都会判断是否是自己的加入导致死锁,如果1000个线程并发更新一行,那也就需要检测1000*1000 = 100万,这种检测消耗大量CPU,影响效率。但是有不能轻易关掉这个死锁检测。
可以根据业务场景来尝试解决:
控制并发,只有5个线程能同时更新数据库。
分散存储,比如钱这种数据,可以分成是张表进行存储,然后算总金额的时候,十张表再加起来。当然,这种方式代码实现要麻烦很多。