MySQL中怎么实现死锁

发布时间:2021-08-04 14:54:22 作者:Leah
来源:亿速云 阅读:330

这篇文章将为大家详细讲解有关MySQL中怎么实现死锁,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。

1.了解锁等待与死锁

出现锁等待或死锁的原因是访问数据库需要加锁,那你可能要问了,为啥要加锁呢?原因是为了确保并发更新场景下的数据正确性,保证数据库事务的隔离性。

锁等待也可称为事务等待,后执行的事务等待前面处理的事务释放锁,但是等待时间超过了 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表可以看到所有事务  # 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  来查看死锁日志,根据死锁日志,结合业务逻辑来进一步定位死锁原因。

在实际应用中,我们要尽量避免死锁现象的发生,可以从以下几个方面入手:

关于MySQL中怎么实现死锁就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。

推荐阅读:
  1. 如何避免mysql死锁
  2. mysql怎么防止死锁

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:MySQL 中怎么创建复制用户

下一篇:如何解决某些HTML字符打不出来的问题

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》