MySQL Innodb怎么让MDL LOCK和ROW LOCK记录到errlog
本篇内容主要讲解“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”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。