本篇內(nèi)容主要講解“MySQL Innodb怎么讓MDL LOCK和ROW LOCK記錄到errlog”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習(xí)“MySQL Innodb怎么讓MDL LOCK和ROW LOCK記錄到errlog”吧!
成都創(chuàng)新互聯(lián)主營臺江網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,app軟件定制開發(fā),臺江h(huán)5小程序設(shè)計搭建,臺江網(wǎng)站營銷推廣歡迎臺江等地區(qū)企業(yè)咨詢
mysql> show variables like '%gaopeng%'; +--------------------------------+-------+| Variable_name | Value | +--------------------------------+-------+ | gaopeng_mdl_detail | OFF || innodb_gaopeng_row_lock_detail | ON | +--------------------------------+-------+
gaopeng_mdl_detail:默認(rèn)OFF，可以設(shè)置ON 用于打印MDL LOCK獲取、等待、升級、降級、釋放日志到errlog(GOBAL)，并且可以在show engine中獲取
innodb_gaopeng_row_lock_detail:默認(rèn)OFF，可以設(shè)置為ON，用于打印innodb ROW LOCK獲取日志、等待日志、隱含鎖轉(zhuǎn)換日志等到errlog，并且可以在show engine中獲取詳細(xì)鎖鏈表信息(注意
沒有行的詳細(xì)信息需要開啟innodb_show_verbose_locks) 到errlog(GLOBAL)。但是沒有做表級印象鎖輸出。
保留原有參數(shù)
innodb_show_verbose_locks:默認(rèn)為0，設(shè)置為1，可以在show engine中獲取鎖定的行詳細(xì)信息。
MySQL MDL LOCK
也就是如果要MDL LOCK測試設(shè)置如下:
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加鎖測試可以設(shè)置如下:
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 ;;
到此，相信大家對“MySQL Innodb怎么讓MDL LOCK和ROW LOCK記錄到errlog”有了更深的了解，不妨來實際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
重慶分公司
重慶分公司
