mysql中如何查看表是否被鎖問題
更新時間：2023年02月18日 14:26:10 作者：普通網友
這篇文章主要介紹了mysql中如何查看表是否被鎖問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
如何查看是否發(fā)生死鎖
在使用mysql的時候，如何查看表是否被鎖呢？
查看表被鎖狀態(tài)和結束死鎖步驟：
1.在mysql命令行執(zhí)行sql語句
use dbName; // 切換到具體數據庫 show engine innodb status; // 查詢db是否發(fā)生死鎖
2.查看數據表被鎖狀態(tài)
show OPEN TABLES where In_use > 0;
該語句可以查詢到當前鎖表的狀態(tài)
3.分析鎖表的SQL
通過sql日志，分析相應SQL，給表加索引，常用字段加索引，表關聯字段加索引等方式對sql進行優(yōu)化。
4.查看正在鎖的事務
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
在5.5中，information_schema 庫中增加了三個關于鎖的表（innoDB引擎）：
innodb_trx ## 當前運行的所有事務 innodb_locks ## 當前出現的鎖 innodb_lock_waits ## 鎖等待的對應關系
先來看一下這三張表結構：
root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks; +————-+———————+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +————-+———————+——+—–+———+——-+ | lock_id | varchar(81) | NO | | | |#鎖ID | lock_trx_id | varchar(18) | NO | | | |#擁有鎖的事務ID | lock_mode | varchar(32) | NO | | | |#鎖模式 | lock_type | varchar(32) | NO | | | |#鎖類型 | lock_table | varchar(1024) | NO | | | |#被鎖的表 | lock_index | varchar(1024) | YES | | NULL | |#被鎖的索引 | lock_space | bigint(21) unsigned | YES | | NULL | |#被鎖的表空間號 | lock_page | bigint(21) unsigned | YES | | NULL | |#被鎖的頁號 | lock_rec | bigint(21) unsigned | YES | | NULL | |#被鎖的記錄號 | lock_data | varchar(8192) | YES | | NULL | |#被鎖的數據 +————-+———————+——+—–+———+——-+ 10 rows in set (0.00 sec) root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits; +——————-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——————-+————-+——+—–+———+——-+ | requesting_trx_id | varchar(18) | NO | | | |#請求鎖的事務ID | requested_lock_id | varchar(81) | NO | | | |#請求鎖的鎖ID | blocking_trx_id | varchar(18) | NO | | | |#當前擁有鎖的事務ID | blocking_lock_id | varchar(81) | NO | | | |#當前擁有鎖的鎖ID +——————-+————-+——+—–+———+——-+ 4 rows in set (0.00 sec) root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ; +—————————-+———————+——+—–+———————+——-+ | Field | Type | Null | Key | Default | Extra | +—————————-+———————+——+—–+———————+——-+ | trx_id | varchar(18) | NO | | | |#事務ID | trx_state | varchar(13) | NO | | | |#事務狀態(tài)： | trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事務開始時間； | trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id | trx_wait_started | datetime | YES | | NULL | |#事務開始等待的時間 | trx_weight | bigint(21) unsigned | NO | | 0 | |# | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事務線程ID | trx_query | varchar(1024) | YES | | NULL | |#具體SQL語句 | trx_operation_state | varchar(64) | YES | | NULL | |#事務當前操作狀態(tài) | trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事務中有多少個表被使用 | trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事務擁有多少個鎖 | trx_lock_structs | bigint(21) unsigned | NO | | 0 | |# | trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事務鎖住的內存大小（B） | trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事務鎖住的行數 | trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事務更改的行數 | trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事務并發(fā)票數 | trx_isolation_level | varchar(16) | NO | | | |#事務隔離級別 | trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性檢查 | trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外鍵檢查 | trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最后的外鍵錯誤 | trx_adaptive_hash_latched | int(1) | NO | | 0 | |# | trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |# +—————————-+———————+——+—–+———————+——-+ 22 rows in set (0.01 sec)
5.查看等待鎖的事務
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS.
6.查看鎖的類型和狀態(tài)：
show status like '%lock%';
7.查詢進程
show processlist
8.殺死發(fā)生死鎖的進程
如果發(fā)生死鎖，通過該語句可以查詢到表被鎖的進程，然后通過kill命令殺掉該進程。
其他查看表鎖的情況：
# 查看表鎖的情況： mysql> show status like 'table%'; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | Table_locks_immediate | 100 | | Table_locks_waited | 11 | +----------------------------+---------+ # 查看InnoDB_row_lock狀態(tài)變量來分析系統上的行鎖的爭奪情況： mysql> show status like 'InnoDB_row_lock%'; +-------------------------------+--------+ | Variable_name | Value | +-------------------------------+--------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 159372 | | Innodb_row_lock_time_avg | 39843 | | Innodb_row_lock_time_max | 51154 | | Innodb_row_lock_waits | 4 | +-------------------------------+--------+ 5 rows in set (0.01 sec) mysql>
在分析innodb中鎖阻塞時，幾種方法的對比情況：
- （1）使用show processlist查看不靠譜；
- （2）直接使用show engine innodb status查看，無法判斷到問題的根因；
- （3）使用mysqladmin debug查看，能看到所有產生鎖的線程，但無法判斷哪個才是根因；
- （4）開啟innodb_lock_monitor后，再使用show engine innodb status查看，能夠找到鎖阻塞的根因。
死鎖發(fā)生情況及原因
產生原因
所謂死鎖：是指兩個或兩個以上的進程在執(zhí)行過程中，因爭奪資源而造成的一種互相等待的現象。若無外力作用，它們都將無法推進下去。此時稱系統處于死鎖狀態(tài)或系統產生了死鎖，這些永遠在互相等待的進程稱為死鎖進程。表級鎖不會產生死鎖。所以解決死鎖主要還是針對于最常用的InnoDB。
死鎖的關鍵在于：兩個(或以上)的Session加鎖的順序不一致。
那么對應的解決死鎖問題的關鍵就是：讓不同的session加鎖有次序
發(fā)生死鎖的幾種情況
會出現死鎖的幾種情況
我們有兩張結構一模一樣的表，分別為t1和t2：
id：integer token：varchar message：varchar
其中id是主鍵（自增），token是非聚集索引，message沒有索引。
1、一張表兩行記錄交叉申請互斥鎖
A執(zhí)行到第二步時，等待B釋放第一步的鎖，而B需要執(zhí)行完第二步結束事務之后才能釋放鎖；
B執(zhí)行到第二步時，等待A釋放第一步的速，這樣A和B都無法進行下去，就產生了死鎖現象。
2、兩張表兩行記錄交叉申請互斥鎖
這種情況與1中的類似。
3、聚集索引與非聚集索引沖突
這種不一定會產生死鎖，表面上也看不出來。
假設A中滿足條件的記錄加鎖順序為（5,4,3,2,1），B中加鎖順序為（1,2,3,4,5），這里的排序是對應record的主鍵；
（InnoDB的鎖是逐步獲取的，而不是一次獲取全部需要的鎖。）
有可能A加鎖了5和4，B加鎖了1、2、3，再往下進行的話就會出相互等待陷入僵局的情況，就是死鎖。
4、聚集索引沖突
這種情況與3中的類似。
5、間隙鎖沖突
這種情況是因為A第一步使用了間隙鎖，在A釋放鎖之前B第二步無法完成，也會形成死鎖。
innodb提供了wait-for graph算法來主動進行死鎖檢測，每當加鎖請求無法立即滿足需要并進入等待時，wait-for graph算法都會被觸發(fā)，檢測是否出現等待環(huán)路。
當檢測到死鎖時，InnoDB會選擇代價比較小的事務進行回滾。
總結
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
MySQL中的insert-on-duplicate語句舉例詳解
這篇文章主要給大家介紹了關于MySQL中insert-on-duplicate語句的相關資料,文中通過圖文以及代碼示例將insert-on-duplicate的用法介紹的非常詳細,對大家的學習或者工作具有一定的參考借鑒價值,需要的朋友可以參考下2024-06-06
linux下mysql提示"mysql deamon failed to start"錯誤的解決方法
網站突然連接不上數據庫,于是朋友直接重啟了一下服務器。進到cli模式下，執(zhí)行 service myqsld start 發(fā)現還是提示"mysql deamon failed to start"錯誤信息2013-04-04
MySQL為JSON字段創(chuàng)建索引方式(Multi-Valued?Indexes?多值索引)
這篇文章主要介紹了MySQL為JSON字段創(chuàng)建索引方式(Multi-Valued?Indexes?多值索引)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
MySQL支持大量的列類型，它可以被分為3類：數字類型、日期和時間類型以及字符串(字符)類型。本節(jié)首先給出可用類型的一個概述，并且總結每個列類型的存儲需求，然后提供每個類中的類型性質的更詳細的描述。概述有意簡化，更詳細的說明應該考慮到有關特定列類型的附加信息，例如你能為其指定值的允許格式。2008-08-08
由于項目變態(tài)需求；需要在一個比較短時間段急劇增加數據庫記錄（兩三天內，由于0增加至4億）。在整個過程調優(yōu)過程非常艱辛2012-07-07
在Linux系統中,卸載MySQL可通過dpkg-l和grep命令組合來查詢已安裝的MySQL相關軟件包,然后使用適當命令進行卸載,安裝MySQL時,可以選擇直接安裝或編譯安裝,直接安裝需要設置密碼,編譯安裝需選擇帶boost庫的版本,初始化MySQL時要指定data目錄2024-10-10
本文主要講解了Mysql主從同步原理，主從同步可以擴展數據庫的負載能力、容錯還可以數據備份等。想要了解相關內容的朋友可以閱讀這篇文章2021-08-08
索引是存儲引擎用于快速找到數據記錄的一種數據結構,類似于教科書的目錄部分,在MySQL中,索引可以加速數據查找,減少磁盤I/O的次數,提高查詢速率,但是,創(chuàng)建和維護索引需要耗費時間,并且索引需要占磁盤空間,在InnoDB中,索引的實現基于B+樹結構2024-12-12
本文主要介紹了MYSQL統計逗號分隔字段元素的個數，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-01-01
在MySQL數據表設計中,時間字段一般都設計為時間戳格式的,開發(fā)人員去查看的時候就顯得有點不方便,可以使用FROM_UNIXTIME轉換成日期格式進行查看,下面這篇文章主要給大家介紹了關于SQL中from_unixtime函數的使用方法的相關資料,需要的朋友可以參考下2022-08-08