本篇内容主要讲解“MySQL Innodb怎么让MDL LOCK和ROW LOCK记录到errlog”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL Innodb怎么让MDL LOCK和ROW LOCK记录到errlog”吧!

mysql>showvariableslike'%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:0011[Note][CallAcquire_lock]THISMDLLOCKacquire[OK]:2018-09-01T20:32:07.090503+08:0011[Note](>MDLPRINT)|Threadidis11|Current_state:Openingtables|2018-09-01T20:32:07.090542+08:0011[Note](->MDLPRINT)DB_nameis:test2018-09-01T20:32:07.090571+08:0011[Note](-->MDLPRINT)OBJ_nameis:kkkpk2018-09-01T20:32:07.090595+08:0011[Note](--->MDLPRINT)Namespaceis:TABLE2018-09-01T20:32:07.090608+08:0011[Note](---->MDLPRINT)Fastpathis:(Y)2018-09-01T20:32:07.090621+08:0011[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_READ(SR)2018-09-01T20:32:07.090635+08:0011[Note](------->MDLPRINT)Mdlstatusis:EMPTY2018-09-01T20:32:07.091077+08:0011[Note][Callrelease_lock]thisMDLLOCKwill[RELEASE]:2018-09-01T20:32:07.091168+08:0011[Note](>MDLPRINT)|Threadidis11|Current_state:closingtables|2018-09-01T20:32:07.091197+08:0011[Note](->MDLPRINT)DB_nameis:test2018-09-01T20:32:07.091210+08:0011[Note](-->MDLPRINT)OBJ_nameis:kkkpk2018-09-01T20:32:07.091241+08:0011[Note](--->MDLPRINT)Namespaceis:TABLE2018-09-01T20:32:07.091254+08:0011[Note](---->MDLPRINT)Fastpathis:(Y)2018-09-01T20:32:07.091267+08:0011[Note](----->MDLPRINT)Mdltypeis:MDL_SHARED_READ(SR)2018-09-01T20:32:07.091280+08:0011[Note](------->MDLPRINT)Mdlstatusis: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:0010[Note]InnoDB:ThisTRXhelpotherTRXconvertimpllocktoexpllock!!!insertoftenuseimpllock!!!!2018-09-01T20:26:08.809422+08:0010[Note]InnoDB:OtherTRX:2018-09-01T20:26:08.809477+08:0010[Note]InnoDB:TRXID:(1294)table:test/kkkpkindex:PRIMARYspace_id:28page_id:3heap_no:2rowlockmode:LOCK_X|LOCK_NOT_GAP|PHYSICALRECORD:n_fields3;compactformat;infobits00:len4;hex80000001;asc;;1:len6;hex00000000050e;asc;;2:len7;hexae0000001e0110;asc;;2018-09-01T20:26:08.809824+08:0010[Note]InnoDB:ThisTRX:2018-09-01T20:26:08.809851+08:0010[Note]InnoDB:TRXID:(1295)table:test/kkkpkindex:PRIMARYspace_id:28page_id:3heap_no:2rowlockmode:LOCK_S|LOCK_NOT_GAP|PHYSICALRECORD:n_fields3;compactformat;infobits00:len4;hex80000001;asc;;1:len6;hex00000000050e;asc;;2:len7;hexae0000001e0110;asc;;2018-09-01T20:26:08.810401+08:0010[Note]InnoDB:Trx(1295)isblocked!!!!!

show engine 也会得到如下记录：

---TRANSACTION1295,ACTIVE101secinsertingmysqltablesinuse1,locked1LOCKWAIT2lockstruct(s),heapsize1136,1rowlock(s)MySQLthreadid10,OSthreadhandle139670301562624,queryid55localhostrootupdateinsertintokkkpkvalues(1)-------TRXHASBEENWAITING101SECFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid28pageno3nbits72indexPRIMARYoftable`test`.`kkkpk`trxid1295lockmodeS(LOCK_S)locksrecbutnotgap(LOCK_REC_NOT_GAP)waiting(LOCK_WAIT)Recordlock,heapno2PHYSICALRECORD:n_fields3;compactformat;infobits00:len4;hex80000001;asc;;1:len6;hex00000000050e;asc;;2:len7;hexae0000001e0110;asc;;------------------TABLELOCKtable`test`.`kkkpk`trxid1295lockmodeIXRECORDLOCKSspaceid28pageno3nbits72indexPRIMARYoftable`test`.`kkkpk`trxid1295lockmodeS(LOCK_S)locksrecbutnotgap(LOCK_REC_NOT_GAP)waiting(LOCK_WAIT)Recordlock,heapno2PHYSICALRECORD:n_fields3;compactformat;infobits00:len4;hex80000001;asc;;1:len6;hex00000000050e;asc;;2:len7;hexae0000001e0110;asc;;---TRANSACTION1294,ACTIVE132sec2lockstruct(s),heapsize1136,1rowlock(s),undologentries1MySQLthreadid9,OSthreadhandle139670301828864,queryid56localhostrootstartingshowengineinnodbstatusTABLELOCKtable`test`.`kkkpk`trxid1294lockmodeIXRECORDLOCKSspaceid28pageno3nbits72indexPRIMARYoftable`test`.`kkkpk`trxid1294lock_modeX(LOCK_X)locksrecbutnotgap(LOCK_REC_NOT_GAP)Recordlock,heapno2PHYSICALRECORD:n_fields3;compactformat;infobits00:len4;hex80000001;asc;;1:len6;hex00000000050e;asc;;2:len7;hexae0000001e0110;asc;;

到此，相信大家对“MySQL Innodb怎么让MDL LOCK和ROW LOCK记录到errlog”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！