InnoDB锁阻塞
因为不同锁之间的兼容性关系,在有些时候一个事务中的锁要等待另一个事务中的锁释放它所占用的资源,这就是阻塞。这里需要明确的是,阻塞不是死锁,并不一定是坏事,它的存在意义是为了确保事务可以并发且正常的运行。
在InnoDB存储引擎中,参数Innodb_lock_wait_timeout用来控制锁等待的时间。
当一个事务锁等待超过这个时间以后,会产生如下错误:
1 |
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
当这个错误报出后,当前的这个SQL语句将被回滚(是SQL语句,不是事务),如果想在这种时候让事务也回滚的话可以设置下面这个参数。
Innodb_rollback_on_timeout用来设定是否在等待超时时对事物进行回滚操作(默认是OFF,不回滚)。
我们来实践一下:
注:我这张图片做的真的好丑啊!!!!
如上,即使我在插入199时锁等待超时了,但是整个事务并没有回滚,在事务2提交之后,我依旧可以从事务1中读到id为2980的值。也就是说,session在锁等待超时后虽然抛出了异常,但是既没有主动commit 也没有主动rollback。 而这是十分危险的状态,因此用户必须判断是否需要commit或者rollback,之后再做下一步的操作。
这里可能会有疑惑,MVCC的作用不就是读写不冲突吗 ? 为什么我在两个事务中的读写还互相阻塞呢 ?
MVCC 虽然可以做到读写不冲突,但是MVCC中所谓的“读写”中的“读”指的是 快照读 ,“写”指的是当前读。 上面的例子中是当前读和当前读,相当于两个写操作,当然还是冲突的。
InnoDB 死锁
死锁是指两个或两个以上的事务在执行过程中,因争夺资源而造成的一种互相等待的现象。 若无外力作用,则事务将无法推进下去。我们随便举两个常见的例子:
1. 这种情况很好理解,事务A和事务B操作两张表,但出现循环等待锁情况
session1 | session2 |
begin | begin |
# 这里两边事务都插入数据是为了方便观察一会有死锁后任何一个事务回滚后是否会将这些insert一起回滚,还是只回滚当前SQL 。
mysql> insert into t5 (name) values (“11111”),(“22222”),(“33333”) ; |
mysql> insert into t3 (name) values (“puppet”) ; Query OK, 1 row affected (0.00 sec) |
mysql> update t5 set name = “d1” where id = 1 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
mysql> update t3 set name = “ansible” where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
# 更新时阻塞住了
mysql> update t3 set name = “salt” where id = 1; |
|
# 同时这边的阻塞也通了
Query OK, 1 row affected (9.41 sec) |
# 这边刚执行这条SQL,就直接报死锁发生了,本事务回滚。
mysql> update t5 set name = “d2” where id = 1 ; |
commit | commit |
从上面的测试中我们得到如下结果:
- 死锁发生时,立刻就被MySQL发现并选择其中一个事务回滚了,并没有感觉阻塞的过程。
- 和阻塞的回滚不一样的是,从t3表中并没有查到事务2的插入,所以是将事务2整个事务回滚掉了。
2. 相同表记录的行锁冲突,两个job在执行数据批量更新时,session 1 处理的的id先后顺序与 session 2处理的id先后顺序顺序相反相交,就可能造成死锁。
session1 | session2 |
begin | begin |
mysql> update t3 set name = “000000” where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
mysql> update t3 set name = “99999” where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
# 阻塞了
mysql> update t3 set name = “888888” where id = 2; |
|
# 这边立马就通过了 Query OK, 1 row affected (9.37 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
# 执行后立马报死锁,失败回滚
mysql> update t3 set name = “77777” where id = 1; |
mysql> commit ; Query OK, 0 rows affected (0.01 sec) |
那看上面这个情况,感觉死锁的场景还是很有可能的啊,那么怎么去避免死锁呢 ?
可能这时候你会想到,如果只要事务等待了就把它回滚,并且让事务重新开始不就完了吗 ? 这或许是一种解决办法,但是风险很大,因为在线上环境中,这可能导致并发性能的下降,甚至到某些情况下任何一个事务都不能进行,这样的话造成的后果比死锁还严重,得不偿失了。
解决死锁问题最简单的一种办法是超时,即当两个事务互相等待时,当一个等待时间超过设置的某一阈值时,其中一个事务进行回滚,另一个等待的事务就可以继续进行了。 在InnoDB存储引擎中,参数innodb_lock_wait_timeout用来设置超时的时间。
超时机制虽然简单,但是它遵循的FIFO(先入先出)的回滚原则,这时候就可能会有问题,因为倘若先超时的那个事务所占权重比较大,回滚代价也比较高,那么其实如果回滚其他的可能更划算些。
这点MySQL也想到了,因此,除了超时机制,当前数据库都还普遍采用 wait-for graph(等待图)的方式来进行死锁检测。 较之超时的解决方案,这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用这种方式。这也是我们上面的死锁为什么刚发生就立刻回滚的原因。
wait-for graph 要求数据库保存以下两种信息:
- 锁的信息链表
- 事务等待链表
它根据这两个列表主动的去发现是否存在回环来确定死锁的存在。在每个事务请求锁并发生等待时都会判断是否存在回路,若存在则有死锁,通常来说InnoDB会选择回滚undo量最小的事务。
那么到这里,大家可能会有疑问,死锁这么可怕,它发生的概率高吗 ? 都和哪些因素有关 ?
在这里我们举个例子:假设当前数据库中共有100(n)个并发线程在执行,而且每个线程都是在执行同样的一个事务,这个事务由10 (r)个操作组成,每个操作从10000 (R )行数据里面随机操作一行数据,并占用该行的锁。每个事务在执行完最后一个操作后释放锁占用的所有锁资源。
在上述模型下:
- 事务发生锁等待的概率为: 1 / 2 (二分之一)
- 一个事务发生死锁的概率为: 1 / 400 (四百分之一)
- 这个系统中有可能发生死锁的概率为: 1 / 4 (四分之一)
可能觉得有点难以接受? 我这个系统中竟然有四分之一的概率发生死锁。大兄弟,我们这个案例可是开了一百个并发执行同样的事务哦。
死锁发生的概率主要与以下几点因素有关:
- 系统中同一时间运行的事务的数量,数量越多发生死锁的概率越大(废话)
- 每个事务包含的操作越多,发生死锁的概率就越大(废话)
- 操作的数据集合越小,发生的死锁概率就越大
注:当一个事务发生死锁后它会回滚,这和我们之前说的事务中锁等待的情况不一样。总结起来说:如果一个事务中的某个操作锁等待超时了,那么这个事务不会回滚,而如果一个事务死锁了,那么InnoDB存储引擎会回滚整个事务哦。
InnoDB锁信息查看
其实,对于我们做数据库运维来说,查看锁信息并依次来定位问题至关重要。 或许我们对锁信息的查看还仅仅局限于通过 show processlist 或者 show engine innodb status 命令。但其实在5.6及以上的版本中我们不必再那样去看了,因为那样看很不直观,而且在高并发的情况下执行结果瞬间挤满屏幕,很不利于我们分析查看。
在 information_schema 库底下有三张表分别为 innodb_trx 、innodb_locks、innodb_lock_waits 。通过这三张表,我们可以更简单的监控当前事务并分析可能存在的锁问题。
innodb_trx
这张表描述了当前数据库系统下所有正在进行的事务的状态:
名称 | 属性 |
---|---|
TRX_ID | 唯一的事务ID号 |
TRX_WEIGHT | 一个事务的权重,主要反映改变的记录数和被事务锁定的记录数。 在处理死锁时, InnoDB 会选择一个具有小权重的事务作为"受害者”回滚。 |
TRX_STATE | 事务执行的状态, 允许的值为 RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING. |
TRX_STARTED | 事务开始时间 |
TRX_REQUESTED_LOCK_ID | 如果TRX_STATE值为LOCK WAIT,则此值表示的是事务等待的锁ID,否则此值为null,可以使用LOCK_ID和INNODB_LOCKS表关联 |
TRX_WAIT_STARTED | 如果TRX_STATE值为LOCK WAIT,则此值表示事务从什么时候开始等待的 |
TRX_MYSQL_THREAD_ID | MySQL thread ID,得到细节关于thread, 使用这个列和NFORMATION_SCHEMA PROCESSLIST table的ID进行关联 |
TRX_QUERY | 事务正在执行的语句 |
TRX_OPERATION_STATE | 事务的当前操作(如果有的话)否则为NULL |
TRX_TABLES_IN_USE | 此事务涉及的表的数量 |
TRX_TABLES_LOCKED | 事务加锁的表的数量 |
TRX_ISOLATION_LEVEL | 事务的隔离级别 |
TRX_LOCK_STRUCTS | 事务占据的锁的数量 |
TRX_LOCK_MEMORY_BYTES | 事务的锁消耗的内存 |
TRX_ROWS_LOCKED | 事务锁定的行数(大约值) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
示例: mysql> select * from innodb_trx limit 1 \G; *************************** 1. row *************************** trx_id: 14775505136 trx_state: RUNNING trx_started: 2017-04-13 16:41:05 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 7 trx_mysql_thread_id: 714481453 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 4 trx_lock_memory_bytes: 1184 trx_rows_locked: 1 trx_rows_modified: 3 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0 |
innodb_locks
顾名思义,这张表描述的是innodb存储引擎的锁信息:
名称 | 属性 |
---|---|
LOCK_ID | 全局的锁ID |
LOCK_TRX_ID | 锁对应的事务ID,可以与innodb_trx做链接 |
LOCK_MODE | 锁模式,主要包括: S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, and UNKNOWN. Lock |
LOCK_TYPE | 锁类型,主要包括: RECORD for a row-level lock, TABLE for a table-level lock. |
LOCK_TABLE | 锁对应的表 |
LOCK_INDEX | 锁住的索引 |
LOCK_SPACE | 所对象的space id |
LOCK_PAGE | 事务锁住的页的数量,若是表锁,则该值为null |
LOCK_REC | 事务锁定行的数量,若是表锁,则该值为null |
LOCK_DATA | 事务锁定记录的主键值,若是表锁,则该值为null |
其实,当我们在某些场景下想查看当前数据库系统的锁情况时,可以直接查询这张表即可。下面为一个示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G *************************** 1. row *************************** lock_id: 3723:72:3:2 lock_trx_id: 3723 lock_mode: X lock_type: RECORD lock_table: `mysql`.`t` lock_index: PRIMARY lock_space: 72 lock_page: 3 lock_rec: 2 lock_data: 1, 9 *************************** 2. row *************************** lock_id: 3722:72:3:2 lock_trx_id: 3722 lock_mode: S lock_type: RECORD lock_table: `mysql`.`t` lock_index: PRIMARY lock_space: 72 lock_page: 3 lock_rec: 2 lock_data: 1, 9 |
innodb_lock_waits
顾名思义,这张表描述了锁等待的信息:
名称 | 属性 |
---|---|
REQUESTING_TRX_ID | 被阻塞的事务ID |
REQUESTED_LOCK_ID | 被阻塞的锁ID |
BLOCKING_TRX_ID | 阻塞了别人的事务ID |
BLOCKING_LOCK_ID | 阻塞了别人的锁ID |
1 2 3 4 5 6 |
mysql>SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G *************************** 1. row *************************** requesting_trx_id: 3396 requested_lock_id: 3396:91:3:2 blocking_trx_id: 3395 blocking_lock_id: 3395:91:3:2 |
这张表需要和前两张表一起联合查询能起到查看当前事务和锁的状态。
操作实例
查看当前数据库系统有哪些事务在等待锁 ? 处于等待状态的相关SQL是什么?在等待哪些事务完成 ?拥有当前锁的SQL是什么?比如下面这个例子:
我们可以这样的方式查询得出想要的结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id; 或者(两者等价) SELECT r.trx_id 等待锁的事务id, r.trx_mysql_thread_id 等待锁事务的线程号, r.trx_query 等待锁事务的查询, b.trx_id 持有锁的事务id, b.trx_mysql_thread_id 持有锁的线程id, b.trx_query 持有锁的查询 FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id; |
我们举个实际的例子:
session1 | session2 |
begin | begin |
mysql> select * from t3 for update ; +—-+———–+ | id | name | +—-+———–+ | 1 | 000000 | | 2 | 888888 | | 3 | gggggg | | 4 | 444444 | | 5 | ccccccc | | 6 | 666666 | | 7 | hehehehe | | 8 | sssssss | | 9 | bbbbbbbbb | | 11 | nnnnnn | | 12 | lkjhgfd | | 13 | hhhh | | 15 | pppppp | +—-+———–+ 13 rows in set (0.00 sec) |
|
mysql> update t3 set name = “cvbnm” where id = 6; |
如上,当我们在事务2中执行update就阻塞了,然后我们看看:
1 2 3 4 5 6 7 8 9 10 11 |
# 这是当前连接线程的情况 mysql> show processlist ; +----+-----------+---------------------+------+-------------+-------+---------------------------------------------------------------+-------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+-----------+---------------------+------+-------------+-------+---------------------------------------------------------------+-------------------------------------------+-----------+---------------+ | 31 | root | localhost | test | Query | 0 | starting | show processlist | 0 | 0 | | 32 | root | localhost | test | Sleep | 33 | | NULL | 13 | 13 | | 33 | root | localhost | test | Query | 12 | updating | update t3 set name = "cvbnm" where id = 6 | 0 | 0 | +----+-----------+---------------------+------+-------------+-------+---------------------------------------------------------------+-------------------------------------------+-----------+---------------+ 4 rows in set (0.00 sec) |
如上,Id 为 31 是我的第三个session用来查看的 ,Id 为 32 是session 1的连接,Id 为 33 是 session 2 的连接,我们用上面的SQL试一下:
1 2 3 4 5 6 7 8 9 |
mysql> SELECT r.trx_id 等待锁的事务id, r.trx_mysql_thread_id 等待锁事务的线程号, r.trx_query 等待锁事务的查询, b.trx_id 持有锁的事务id, b.trx_mysql_thread_id 持有锁的线程id, b.trx_query 持有锁的查询 FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G; *************************** 1. row *************************** 等待锁的事务id: 8760 等待锁事务的线程号: 33 等待锁事务的查询: update t3 set name = "cvbnm" where id = 6 持有锁的事务id: 8759 持有锁的线程id: 32 持有锁的查询: NULL 1 row in set, 1 warning (0.00 sec |
是不是一目了然呢 ?
对于死锁来说,在MySQL中可以把死锁信息打印到错误日志里,开启如下变量即可。
1 |
mysql> set global innodb_print_all_deadlocks = 1; |
然后死锁发生时错误日志中会出现如下信息,以及对应的SQL语句和死锁信息和事务信息。:
1 |
2018-05-16 20:16:30 7f126a1e4700 InnoDB: transactions deadlock detected, dumping detailed information. |
另外,我们也可以根据全局的status变量来监控和衡量锁的相关情况:
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show status like "%innodb%lock%" ; +-------------------------------+--------+ | Variable_name | Value | +-------------------------------+--------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 207180 | | Innodb_row_lock_time_avg | 17265 | | Innodb_row_lock_time_max | 61145 | | Innodb_row_lock_waits | 12 | +-------------------------------+--------+ 5 rows in set (0.00 sec) |
- Innodb_row_lock_current_waits:当前锁等待的数量
- Innodb_row_lock_time:自系统启动到现在,锁定的总时间,单位:毫秒 ms。
- Innodb_row_lock_time_avg:平均锁定的时间,单位:毫秒 ms。
- Innodb_row_lock_time_max:最大锁定时间,单位:毫秒 ms。
- Innodb_row_lock_waits:自系统启动到现在,锁等待次数,即锁定的总次数。