Shulou(Shulou.com)05/31 Report--

This article mainly explains how MySQL Innodb allows MDL LOCK and ROW LOCK to record to errlog. Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "MySQL Innodb how to make MDL LOCK and ROW LOCK record to errlog"!

1. Newly added parameters and reserved parameters mysql > show variables like'% gaopeng%' +-- +-+ | Variable_name | Value | +-+-+ | gaopeng_mdl_detail | OFF | | innodb_gaopeng_row_lock_ Detail | ON | +-+-+

Gaopeng_mdl_detail: default OFF, you can set ON to print MDL LOCK to get, wait, upgrade, downgrade, release logs to errlog (GOBAL), and can be obtained in show engine

Innodb_gaopeng_row_lock_detail: default OFF, which can be set to ON, which is used to print innodb ROW LOCK to get logs, wait for logs, implicit lock conversion logs and wait for errlog, and you can get detailed chain list information in show engine (note

No line details need to be opened from innodb_show_verbose_locks) to errlog (GLOBAL). But there is no table-level impression lock output.

Retain the original parameters

Innodb_show_verbose_locks: default to 0, set to 1, and you can get the row details of the lock in show engine.

II. Test outline

MySQL MDL LOCK

That is, if you want the MDL LOCK test settings to be as follows:

Set global gaopeng_mdl_detail=1

After re-logging in, you will get the log every time you get the MDL LOCK information. Here is a select statement to obtain the MDL LOCK and the released log:

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

If you need an INNODB ROW LOCK lock test, you can set it as follows:

Set global innodb_gaopeng_row_lock_detail=1

Set innodb_show_verbose_locks=1

Log in again, here is a log locked by insert uniqueness check:

2018-09-01T20:26:08.809304+08:00 10 [Note] InnoDB: This TRX help other TRX convert impl lock to expl lockboxes insert often use impl lockouts InnoDB: Other TRX:2018-09-01T20:26:08.809477+08:00 10 [01T20:26:08.809477+08:00] 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 00: 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 00: len 4; hex 80000001; asc;; 1: len 6; hex 000000050e; asc;; 2: len 7; hex ae0000001e0110 Asc;; 2018-09-01T20:26:08.810401+08:00 10 [Note] InnoDB: Trx (1295) is blocked!

Show engine will also get the following record:

-TRANSACTION 1295, ACTIVE 101sec insertingmysql 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 updateinsert 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`.`kkkp K`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 00: 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 IXRECORD LOCKS space id 28 page no 3 n bits 72 index PRIMARY of table `test`.`kkpk` 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 00: len 4; hex 80000001; asc; 1: len 6; hex 000000050e; asc;; 2: len 7 Hex ae0000001e0110; asc;-TRANSACTION 1294, ACTIVE 132sec2 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 startingshow engine innodb statusTABLE LOCK table `test`.`kkpkk`trx id 1294 lock mode IXRECORD 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 00: len 4; hex 800000001; asc; 1: len 6; hex 000000050e; asc;; 2: len 7; hex ae0000001e0110; asc;; at this point, I believe you have a better understanding of "how MySQL Innodb allows MDL LOCK and ROW LOCK to record errlog". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.