MySQL中怎么實現死鎖

這篇文章將為大家詳細講解有關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 當前運行的所有事務。

• 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 。

關于MySQL中怎么實現死鎖就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。