乐趣区

关于前端:MySQL锁等待与死锁问题分析

jiezi

前言：

在 MySQL 运维过程中，锁期待和死锁问题是令各位 DBA 及开发同学十分头痛的事。呈现此类问题会造成业务回滚、卡顿等故障，特地是业务忙碌的零碎，呈现死锁问题后影响会更重大。本篇文章咱们一起来学习下什么是锁期待及死锁，呈现此类问题又应该如何剖析解决呢？

1. 理解锁期待与死锁
呈现锁期待或死锁的起因是拜访数据库须要加锁，那你可能要问了，为啥要加锁呢？起因是为了确保并发更新场景下的数据正确性，保障数据库事务的隔离性。

试想一个场景，如果你要去图书馆借一本《高性能 MySQL》，为了避免有人提前把这本书借走，你能够提前进行预约（加锁），这把锁能够怎么加？

封闭图书馆（数据库级别的锁）
把数据库相干的书都锁住（表级别的锁）
只锁 MySQL 相干的书（页级别的锁）
只锁《高性能 MySQL》这本书（行级别的锁）
锁的粒度越细，并发级别越高，实现也更简单。

锁期待也可称为事务期待，后执行的事务期待后面解决的事务开释锁，然而等待时间超过了 MySQL 的锁等待时间，就会引发这个异样。期待超时后的报错为“Lock wait timeout exceeded…”。

死锁产生的起因是两个事务相互期待对方开释雷同资源的锁，从而造成的死循环。产生死锁后会立刻报错“Deadlock found when trying to get lock…”。

2. 景象复现及解决
上面咱们以 MySQL 5.7.23 版本为例（隔离级别是 RR），来复现下上述两种异常现象。

mysql> show create table test_tb\G
1. row **

   Table: test_tb

Create Table: CREATE TABLE test_tb (
id int(11) NOT NULL AUTO_INCREMENT,
col1 varchar(50) NOT NULL DEFAULT ”,
col2 int(11) NOT NULL DEFAULT ‘1’,
col3 varchar(20) NOT NULL DEFAULT ”,
PRIMARY KEY (id),
KEY idx_col1 (col1)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from test_tb;
id col1 col2 col3
1 fdg 1 abc
2 a 2 fg
3 ghrv 2 rhdv

3 rows in set (0.00 sec)

事务一首先执行

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_tb where col1 = ‘a’ for update;
id col1 col2 col3
2 a 2 fg

1 row in set (0.00 sec)

事务二而后执行

mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> update test_tb set col2 = 1 where col1 = ‘a’;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
呈现上种异样的起因是事务二在期待事务一的行锁，但事务一始终没提交，期待超时而报错。InnoDB 行锁期待超时工夫由 innodb_lock_wait_timeout 参数管制，此参数默认值为 50，单位为秒，即默认状况下，事务二会期待 50s，若仍拿不到行锁则会报期待超时异样并回滚此条语句。

对于 5.7 版本，呈现锁期待时，咱们能够查看 information_schema 中的几张零碎表来查问事务状态。

innodb_trx 以后运行的所有事务。
innodb_locks 以后呈现的锁。
innodb_lock_waits 锁期待的对应关系

锁期待产生时 查看 innodb_trx 表能够看到所有事务

trx_state 值为 LOCK WAIT 则代表该事务处于期待状态

mysql> select * from information_schema.innodb_trx\G
1. row **

                trx_id: 38511
             trx_state: LOCK WAIT
           trx_started: 2021-03-24 17:20:43
 trx_requested_lock_id: 38511:156:4:2
      trx_wait_started: 2021-03-24 17:20:43
            trx_weight: 2
   trx_mysql_thread_id: 1668447
             trx_query: update test_tb set col2 = 1 where col1 = 'a'
   trx_operation_state: starting index read
     trx_tables_in_use: 1
     trx_tables_locked: 1
      trx_lock_structs: 2
 trx_lock_memory_bytes: 1136
       trx_rows_locked: 1
     trx_rows_modified: 0

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: 0

      trx_is_read_only: 0

trx_autocommit_non_locking: 0
2. row **

                trx_id: 38510
             trx_state: RUNNING
           trx_started: 2021-03-24 17:18:54
 trx_requested_lock_id: NULL
      trx_wait_started: NULL
            trx_weight: 4
   trx_mysql_thread_id: 1667530
             trx_query: NULL
   trx_operation_state: NULL
     trx_tables_in_use: 0
     trx_tables_locked: 1
      trx_lock_structs: 4
 trx_lock_memory_bytes: 1136
       trx_rows_locked: 3
     trx_rows_modified: 0

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: 0

      trx_is_read_only: 0

trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

innodb_trx 字段值含意

trx_id：事务 ID。
trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started：事务开始工夫。
trx_requested_lock_id：事务以后正在期待锁的标识，能够和 INNODB_LOCKS 表 JOIN 以失去更多详细信息。
trx_wait_started：事务开始期待的工夫。
trx_weight：事务的权重。
trx_mysql_thread_id：事务线程 ID，能够和 PROCESSLIST 表 JOIN。
trx_query：事务正在执行的 SQL 语句。
trx_operation_state：事务以后操作状态。
trx_tables_in_use：以后事务执行的 SQL 中应用的表的个数。
trx_tables_locked：以后执行 SQL 的行锁数量。
trx_lock_structs：事务保留的锁数量。
trx_isolation_level：以后事务的隔离级别。

sys.innodb_lock_waits 视图也可看到事务期待情况，且给出了杀链接的 SQL

mysql> select * from sys.innodb_lock_waits\G
1. row **

            wait_started: 2021-03-24 17:20:43
                wait_age: 00:00:22
           wait_age_secs: 22
            locked_table: `testdb`.`test_tb`
            locked_index: idx_col1
             locked_type: RECORD
          waiting_trx_id: 38511
     waiting_trx_started: 2021-03-24 17:20:43
         waiting_trx_age: 00:00:22
 waiting_trx_rows_locked: 1

waiting_trx_rows_modified: 0

             waiting_pid: 1668447
             waiting_query: update test_tb set col2 = 1 where col1 = 'a'
         waiting_lock_id: 38511:156:4:2
       waiting_lock_mode: X
           blocking_trx_id: 38510
            blocking_pid: 1667530
          blocking_query: NULL
        blocking_lock_id: 38510:156:4:2
      blocking_lock_mode: X
    blocking_trx_started: 2021-03-24 17:18:54
        blocking_trx_age: 00:02:11
blocking_trx_rows_locked: 3

blocking_trx_rows_modified: 0

 sql_kill_blocking_query: KILL QUERY 1667530

sql_kill_blocking_connection: KILL 1667530
sys.innodb_lock_waits 视图整合了事务期待情况，同时给出杀掉梗塞源端的 kill 语句。不过是否要杀掉链接还是须要综合思考的。

死锁与锁期待稍有不同，咱们同样也来简略复现下死锁景象。

开启两个事务

事务一执行

mysql> update test_tb set col2 = 1 where col1 = ‘a’;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

事务二执行

mysql> update test_tb set col2 = 1 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 回到事务一执行 回车后 此条语句处于锁期待状态
mysql> update test_tb set col1 = ‘abcd’ where id = 3;
Query OK, 1 row affected (5.71 sec)
Rows matched: 1 Changed: 1 Warnings: 0

回到事务二再执行 此时二者互相期待产生死锁

mysql> update test_tb set col3 = ‘gddx’ where col1 = ‘a’;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
产生死锁后会抉择一个事务进行回滚，想查明死锁起因，能够执行 show engine innodb status 来查看死锁日志，依据死锁日志，联合业务逻辑来进一步定位死锁起因。

在理论利用中，咱们要尽量避免死锁景象的产生，能够从以下几个方面动手：

事务尽可能小，不要将简单逻辑放进一个事务里。
波及多行记录时，约定不同事务以雷同程序拜访。
业务中要及时提交或者回滚事务，可缩小死锁产生的概率。
表要有适合的索引。
可尝试将隔离级别改为 RC。
总结：

本篇文章简略介绍了锁期待及死锁产生的起因，其实实在业务中产生死锁还是很难剖析的，须要肯定的教训积攒。本篇文章只是面向初学者，心愿各位对死锁可能有个简略的印象。

退出移动版