一 基础知识
Mysql的行锁是由存储引擎实现的,不是所有的引擎都支持行锁,比如MyISAM就不支持行锁,InnoDB引擎就支持行锁,这是Mysql用InnoDB引擎替代MyISAM原因之一,还有重要原因是InnoDB引擎支持事务等。
故名思意,行锁是对表里面的一行数据的锁定,行锁开销比较大,锁表慢,当时好处是可以支持高并发;表锁刚好相反。
二 实践
模拟很简单:
A客户端:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test set c=c+1 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
B客户端:
mysql> use test;
Database changed
mysql> update test set c=c+2 where id=3;
Query OK, 1 row affected (8.68 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
在A客户端执行commit之前,B客户端操作的是同一个数据会被卡住,直到A提交了。
小坑
不是所有的情况都使用行锁,有些批量操作,或者查询条件不是唯一键查询,InnoDB会把行锁升级为表锁:
客户端A:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test set c=c+1 where c=6;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql>
客户端B:
mysql> update test set c=c+2 where c=8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
B客户端因为A客户端的操作使用了表锁,导致处理另外一条记录仍然是需要等待的状态。
InnoDB是针对索引加的行锁,而且索引还不能失效,不然就会自动升级为表锁。
Mysql这样操作,可能是因为没有按照唯一键索引查询的时候,操作的是批量数据,如果一行行加行锁,会速度很慢。
行锁除了注意刚才升级为表锁的情况,还需要注意为了提升并发,一个事务中,如果有多个操作,那么将共享用户最多的表放在最后,目的很明确,放在后面的表被锁定的时间越短,这样整体系统的并发量就会越高。
三 死锁问题
如果数据库的占cpu比较高,当时性能并不好的时候,就需要考虑死锁的情况了。InnoDB可以自动判断死锁避免死锁,有两种办法:
- 一种策略是,通过innodb_lock_wait_timeout 来设置,一个事务在等待锁的时候,如果超过这个时间没有得到锁,自动退出。
mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| lock_wait_timeout | 31536000 |
| wait_timeout | 28800 |
+--------------------------+----------+
innodb_lock_wait_timeout 这个时间默认是50s,即等待锁的时间不能超过50s,超过就自动终止。当时实际使用中,如果等待50s,粥都凉了。当时如果把这个等待锁的时间设置的时间短,可以快速失败,当时如果数据库本身压力大,执行慢这种情况就容易误伤。
这种办法本质就是无脑判断,只看时间不管实际情况,所以如果我们能够主动判断死锁的情况,这样不是更好吗,所以InnoDB又有了一种检测方法。
-
另一种就是进行死锁检测,发现死锁后,主动回滚死锁链条中的一条事务让其他事务可以执行,网上有个比较形象的图:
死锁
右图四辆车,转圈,每个车都依赖前面一辆车,彼此依赖形成死锁。这时候,我们如果把其中一辆拿掉,其他的车可以依次开走。
InnoDB利用这种图结构来检测可能存在的死锁。
mysql> show variables like '%innodb_deadlock_detect%'
-> ;
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON |
+------------------------+-------+
1 row in set, 1 warning (0.00 sec)
这种自动检测死锁的功能,也不是无代价的,每个新加入的线程都要判断是否有其他占有了锁,如果有100个线程,总共死锁检测就是1万次,所以死锁检测会很消耗cpu。
一般的解决办法,一是直接关闭自动检测,这种存在一定风险,除非特别简单,否则谁也无法保障不发生死锁,关闭死锁检测会出现大量操作的超时,可能得不偿失。
另一个方法是控制并发度,如果并发修改同一行记录的并发数比较少的话,那么死锁检测的成本并不高,可以通过控制客户端或中间件,来限制整个Mysql的操作同一行记录的并发数。
四 补充说明
InnoDB在利用主键索引进行更新和删除的时候是会锁一个记录,如果是利用非聚簇索引进行更新和删除的时候,会锁住查询到的记录;如果没有走索引,会锁住整个表。
delete from msg where id=2;
以主键操作表
delete from msg where token=’ cvs’;
二级索引操作表
delete from msg where message='你好‘
非索引查询
还有一种叫GAP锁,这种是为了防止幻读,InnoDB引入的:
update msg set message=‘订单’ where token=‘asd’;
GAP锁
这时候你插入一条token也是asd的数据会锁住等待,更新的事务提交,目的是为了在可重复度事务级别防止幻读。
网友评论