MySQL:Innodb 讓MDL LOCK和ROW LOCK 記錄到errlog
本文是一個說明文件,主要是為MySQL和Innodb做了兩個比較簡單的輸出功能用於便於大家學習這兩種鎖,其實這個方法也是我學習的時候用到的:
-
MySQL 層 MDL LOCK
-
Innodb 層ROW LOCK
但是由於能力有限肯定有考慮不周的地方,請指出。
引數名字gaopeng用於快速查詢自己加入的引數標誌。
僅用於學習測試。
一、新加入的引數和保留的引數
mysql> show variables like '%gaopeng%'; +--------------------------------+-------+| Variable_name | Value | +--------------------------------+-------+ | gaopeng_mdl_detail | OFF || innodb_gaopeng_row_lock_detail | ON | +--------------------------------+-------+
-
gaopeng_mdl_detail:預設OFF,可以設定ON 用於列印MDL LOCK獲取、等待、升級、降級、釋放日誌到errlog(GOBAL),並且可以在show engine中獲取
-
innodb_gaopeng_row_lock_detail:預設OFF,可以設定為ON,用於列印innodb ROW LOCK獲取日誌、等待日誌、隱含鎖轉換日誌等到errlog,並且可以在show engine中獲取詳細鎖連結串列資訊(注意
沒有行的詳細資訊需要開啟innodb_show_verbose_locks) 到errlog(GLOBAL)。但是沒有做表級印象鎖輸出。 -
保留原有引數
innodb_show_verbose_locks:預設為0,設定為1,可以在show engine中獲取鎖定的行詳細資訊。
二、測試概要
-
MySQL MDL LOCK
也就是如果要MDL LOCK測試設定如下:
set global gaopeng_mdl_detail=1;
重新登陸後每次獲取MDL LOCK資訊會得到日誌,下面是一個select語句獲取MDL LOCK和釋放的日誌:
2018-09-01T20:32:07.090351+08:00 11 [Note] [Call Acquire_lock] THIS MDL LOCK acquire [OK]: 2018-09-01T20:32:07.090503+08:00 11 [Note] (>MDL PRINT) |Thread id is 11|Current_state: Opening tables| 2018-09-01T20:32:07.090542+08:00 11 [Note] (->MDL PRINT) DB_name is:test 2018-09-01T20:32:07.090571+08:00 11 [Note] (-->MDL PRINT) OBJ_name is:kkkpk 2018-09-01T20:32:07.090595+08:00 11 [Note] (--->MDL PRINT) Namespace is:TABLE 2018-09-01T20:32:07.090608+08:00 11 [Note] (---->MDL PRINT) Fast path is:(Y) 2018-09-01T20:32:07.090621+08:00 11 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ(SR) 2018-09-01T20:32:07.090635+08:00 11 [Note] (------->MDL PRINT) Mdl status is:EMPTY 2018-09-01T20:32:07.091077+08:00 11 [Note] [Call release_lock] this MDL LOCK will [RELEASE]: 2018-09-01T20:32:07.091168+08:00 11 [Note] (>MDL PRINT) |Thread id is 11|Current_state: closing tables| 2018-09-01T20:32:07.091197+08:00 11 [Note] (->MDL PRINT) DB_name is:test 2018-09-01T20:32:07.091210+08:00 11 [Note] (-->MDL PRINT) OBJ_name is:kkkpk 2018-09-01T20:32:07.091241+08:00 11 [Note] (--->MDL PRINT) Namespace is:TABLE 2018-09-01T20:32:07.091254+08:00 11 [Note] (---->MDL PRINT) Fast path is:(Y) 2018-09-01T20:32:07.091267+08:00 11 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ(SR) 2018-09-01T20:32:07.091280+08:00 11 [Note] (------->MDL PRINT) Mdl status is:EMPTY
-
Innodb ROW LOCK
如果需要INNODB ROW LOCK加鎖測試可以設定如下:
set global innodb_gaopeng_row_lock_detail=1;
set innodb_show_verbose_locks=1;
重新登陸,下面是一個insert唯一性檢查鎖定的日誌:
2018-09-01T20:26:08.809304+08:00 10 [Note] InnoDB: This TRX help other TRX convert impl lock to expl lock!!!insert often use impl lock!!!! 2018-09-01T20:26:08.809422+08:00 10 [Note] InnoDB: Other TRX: 2018-09-01T20:26:08.809477+08:00 10 [Note] InnoDB: TRX ID:(1294) table:test/kkkpk index:PRIMARY space_id: 28 page_id:3 heap_no:2 row lock mode:LOCK_X|LOCK_NOT_GAP| PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000000050e; asc ;; 2: len 7; hex ae0000001e0110; asc ;; 2018-09-01T20:26:08.809824+08:00 10 [Note] InnoDB: This TRX: 2018-09-01T20:26:08.809851+08:00 10 [Note] InnoDB: TRX ID:(1295) table:test/kkkpk index:PRIMARY space_id: 28 page_id:3 heap_no:2 row lock mode:LOCK_S|LOCK_NOT_GAP| PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000000050e; asc ;; 2: len 7; hex ae0000001e0110; asc ;; 2018-09-01T20:26:08.810401+08:00 10 [Note] InnoDB: Trx(1295) is blocked!!!!!
show engine 也會得到如下記錄:
---TRANSACTION 1295, ACTIVE 101 sec inserting mysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 10, OS thread handle 139670301562624, query id 55 localhost root update insert into kkkpk values(1) ------- TRX HAS BEEN WAITING 101 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 28 page no 3 n bits 72 index PRIMARY of table `test`.`kkkpk` trx id 1295 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT) Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000000050e; asc ;; 2: len 7; hex ae0000001e0110; asc ;; ------------------ TABLE LOCK table `test`.`kkkpk` trx id 1295 lock mode IX RECORD LOCKS space id 28 page no 3 n bits 72 index PRIMARY of table `test`.`kkkpk` trx id 1295 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT) Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000000050e; asc ;; 2: len 7; hex ae0000001e0110; asc ;; ---TRANSACTION 1294, ACTIVE 132 sec2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1MySQL thread id 9, OS thread handle 139670301828864, query id 56 localhost root starting show engine innodb status TABLE LOCK table `test`.`kkkpk` trx id 1294 lock mode IX RECORD LOCKS space id 28 page no 3 n bits 72 index PRIMARY of table `test`.`kkkpk` trx id 1294 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000000050e; asc ;; 2: len 7; hex ae0000001e0110; asc ;;
可以看到等待即輸出一致事物1295 處於等待狀態,鎖模式一致。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7728585/viewspace-2213395/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL:理解MDL LockMySql
- MySQL:MDL LOCK的“穿越行為”MySql
- mysql觀測METADATA LOCK(MDL)鎖MySql
- 【MySQL】select for update 的Row Lock 與Table LockMySql
- mysql innodb lock鎖之record lock之一MySql
- master bug InnoDB Error unlock row could not find a 4 mode lockASTError
- MYSQL中一個特殊的MDL LOCK死鎖案列MySql
- MySQL入門學習之——innodb lockMySql
- MYSQL METADATA LOCK(MDL LOCK)學習(1) 理論知識和加鎖型別測試MySql型別
- MySQL 5.5 -- innodb_lock_wait 鎖 等待MySqlAI
- innodb_lock_monitor解決mysql死鎖MySql
- enq: TX - row lock contentionENQ
- 等待事件之Row Cache Lock事件
- innodb_autoinc_lock_mode
- 等待事件enq: TX - row lock contention事件ENQ
- 【等待事件】-enq: TX - row lock contention事件ENQ
- row lock contention 阻塞程式查詢
- hanganalyze解決row cache lock(ZT)
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- [ORACLE 11G]ROW CACHE LOCK 等待Oracle
- mysql lock操作MySql
- MYSQL INNODB replace into 死鎖 及 next key lock 淺析MySql
- mysql之 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11MySqlError
- 鎖:innodb_lock_wait_timeout和 innodb_rollback_on_timeout?AI
- enq: TX – row lock contention的測試和案例分析ENQ
- 奇異的enq: TX - row lock contentionENQ
- 等待事件enq TX row lock contention分析事件ENQ
- 轉)用hanganalyze解決row cache lock
- (轉)用hanganalyze解決row cache lock
- mysql innodb_autoinc_lock_mode 的與資料庫行為MySql資料庫
- MySQL:簡單記錄一下Waiting for commit lockMySqlAIMIT
- 故障排除:"WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! "AIENQ
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!的分析AIENQ
- 轉貼_用hanganalyze解決row cache lock
- enq: TX - row lock contention等待事件處理ENQ事件
- 用hanganalyze解決row cache lock(轉貼)
- MySQL自增鎖模式innodb_autoinc_lock_mode引數詳解MySql模式
- 【筆記】lock and latch筆記