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

This article mainly introduces "Why lock in share mode can not see row lock information in show engine in Innodb". In daily operation, I believe that many people have doubts about why lock in share mode can not see row lock information in show engine in Innodb. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the question of "Why lock in share mode can not see row lock information in show engine in Innodb". Next, please follow the editor to study!

I. asking questions

I don't know if there are any friends who do locking experiments with lock in share mode like me, but I can't see the locking information in show engine innodb status. Today, a friend happened to ask @ to blow on the branches. Today, I did a simple debug, because I was also very puzzled. (I like to ask one more why I'm tired.)

The questions are as follows:

First of all, I turned on my print line lock parameter and let the lock output to the log.

Mysql > show variables like'% gaopeng%' +-- +-+ | Variable_name | Value | +-+-+ | gaopeng_mdl_detail | OFF | | innodb_gaopeng_row_lock_ Detail | ON | +-+-+

Then run the following statement

Mysql > show create table t\ gateway * 1. Row * * Table: tCreate Table: CREATE TABLE `t` (`id` int (11) NOT NULL, `c` int (11) DEFAULT NULL, `d` int (11) DEFAULT NULL, PRIMARY KEY (`id`) KEY `c` (`c`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) mysql > select * from t +-+ | id | c | d | +-- + | 0 | 0 | 0 | 5 | 5 | 10 | 10 | 10 | 15 | 15 | 20 | 20 | 20 | 25 | 25 | 25 | + 6 rows in set (0.00 sec) mysql > begin Query OK, 0 rows affected mysql > select * from t where id=0 lock in share mode;+----+ | id | c | d | +-+ | 0 | 0 | 0 | +-+ 1 row in set (4.21 sec)

LOCK_S should be on the primary key ID=0 at this time, but show engine innodb will not see the lock information as follows:

-TRANSACTIONS-Trx id counter 241482Purge done for trx's NRO

< 241482 undo n:o < 0 state: running but idleHistory list length 182Total number of lock structs in row lock hash table 1LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 422211785606640, not started0 lock struct(s), heap size 1160, 0 row lock(s)---TRANSACTION 422211785605248, not started0 lock struct(s), heap size 1160, 0 row lock(s)--------FILE I/O-------- 根本看不到加锁信息。但是我的日志中却有输出如下： 2019-03-20T14:37:41.980845+08:00 10 [Note] InnoDB: TRX ID:(0) table:test/t index:PRIMARY space_id: 95 page_id:3 heap_no:2 row lock mode:LOCK_S|LOCK_NOT_GAP|PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000000; asc ;; 1: len 6; hex 00000003676a; asc gj;; 2: len 7; hex d8000000330110; asc 3 ;; 3: len 4; hex 80000000; asc ;; 4: len 4; hex 80000000; asc ;; 因此我基本断定加锁肯定是做了的，但是为什么没有输出呢？ 二、分析 我开始怀疑是否是提前释放了或者是打印的时候过滤掉了？后来发现都不是。看了到了一个TRX_ID为422211785605248，这是只读事物的TRX_ID的形式，会不是因为应打印的时候只会打印读写的事物的锁结构信息，因为Innodb中读写事物有一个独立的链表，如果只打印这个链表上的信息就会出现这个问题。接着我做了一个事物先做了一个delete操作然后做lock in share mode语句可以看到LOCK_S结构就可以看到了，如下： mysql>

Begin;Query OK, 0 rows affected (2.43 sec) mysql > delete from T2 limit 1; # # I show the beginning of a read and write transaction Query OK, 1 row affected (3.53 sec) mysql > select * from t where id=0 lock in share mode +-+ | id | c | d | +-+ | 0 | 0 | 0 | +-+ 1 row in set (2.98 sec) mysql >

Let's take another look.

-TRANSACTION 422211785606640, not started0 lock struct (s), heap size 1160, 0 row lock (s)-TRANSACTION 422211785605248, not started0 lock struct (s), heap size 1160,0 row lock (s)-TRANSACTION 241482, ACTIVE 85 sec4 lock struct (s), heap size 1160,2 row lock (s), undo log entries 1MySQL thread id 10, OS thread handle 140737153423104 Query id 391 localhost rootTABLE LOCK table `test`.`t2` trx id 241482 lock mode IXRECORD LOCKS space id 33 page no 19 n bits 624 index GEN_CLUST_INDEX of table `test`.`t2` trx id 241482 lock_mode X (LOCK_X) locks rec but not gap (LOCK_REC_NOT_GAP) Record lock, heap no 447 PHYSICAL RECORD: n_fields 4 Compact format; info bits 320: len 6; hex 000000451d; asc E; 1: len 6; hex 00000003af4a; asc Jten; 2: len 7; hex 3c0000453040; asc

< E0@;; 3: len 4; hex 80000001; asc ;;TABLE LOCK table `test`.`t` trx id 241482 lock mode ISRECORD LOCKS space id 95 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 241482 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP)Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000000; asc ;; 1: len 6; hex 00000003676a; asc gj;; 2: len 7; hex d8000000330110; asc 3 ;; 3: len 4; hex 80000000; asc ;; 4: len 4; hex 80000000; asc ;; 我们看到了 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP)的信息看来没有问题，猜测是实验是一样的，但是还是要源码验证一下。 三、源码验证1、打印函数lock_print_info_all_transactions/*********************************************************************//**Prints info of locks for each transaction. This function assumes that thecaller holds the lock mutex and more importantly it will release the lockmutex on behalf of the caller. (This should be fixed in the future). */voidlock_print_info_all_transactions(/*=============================*/ FILE* file) /*!< in/out: file where to print */{ ut_ad(lock_mutex_own()); fprintf(file, "LIST OF TRANSACTIONS FOR EACH SESSION:\n"); mutex_enter(&trx_sys->

Mutex); / * First print info on non-active transactions * / / * NOTE: information of auto-commit non-locking read-only transactions will be omitted here. The information will be available from INFORMATION_SCHEMA.INNODB_TRX. * / PrintNotStarted print_not_started (file); / / create a structure that aims to print ut_list_map (trx_sys- > mysql_trx_list, print_not_started) of not start things; / / this place prints things whose state is no start, but there is a problem. You'll see the code later. Mysql_trx_list is the whole thing. Const trx_t* trx; TrxListIterator trx_iter; / / this iterator is the iterator of the linked list trx_sys- > rw_trx_list const trx_t* prev_trx = 0; / * Control whether a block should be fetched from the buffer pool. * / bool load_block = true; bool monitor = srv_print_innodb_lock_monitor & & (srv_show_locks_held! = 0); while ((trx = trx_iter.current ())! = 0) {/ / iterate through the iterator, obviously there will be no information about read-only things. Check_trx_state (trx); if (trx! = prev_trx) {lock_trx_print_wait_and_mvcc_state (file, trx); prev_trx = trx; / * The transaction that read in the page is no longer the one that read the page in. We need to force a page read. * / load_block = true;} / * If we need to print the locked record contents then we need to fetch the containing block from the buffer pool. * / if (monitor) {/ * Print the locks owned by the current transaction. * / TrxLockIterator& lock_iter = trx_iter.lock_iter (); if (! lock_trx_print_locks (file, trx, lock_iter, load_block)) {/ * Resync trx_iter, the trx_sys- > mutex and the lock mutex were released. A page was successfully read in. We need to print its contents on the next call to lock_trx_print_locks (). On the next call to lock_trx_print_locks () we should simply print the contents of the page just read in.*/ load_block = false; continue;}} load_block = true; / * All record lock details were printed without fetching a page from disk, or we didn't need to print the detail. * / trx_iter.next ();} lock_mutex_exit (); mutex_exit (& trx_sys- > mutex); ut_ad (lock_validate ());}

This function is the logic of the call.

Structure PrintNotStarted parentheses overload void operator () (const trx_t* trx) {ut_ad (trx- > in_mysql_trx_list); ut_ad (& trx_sys- > mutex) / * See state transitions and locking rules in trx0trx.h * / if (trx_state_eq (trx, TRX_STATE_NOT_STARTED)) {/ / here we find that only the status of TRX_STATE_NOT_STARTED will output fputs ("- -", m_file); trx_print_latched (m_file, trx, 600);}}

We can see here that only the status of TRX_STATE_NOT_STARTED will be output to the status of not start.

TrxListIterator iterator initialization code TrxListIterator (): m_index () {/ * We iterate over the RW trx list first. * / m_trx_list = & trx_sys- > rw_trx_list;}

We can see here that only read and write things will output the lock structure.

At this point, the study on "Why lock in share mode can not see row lock information in show engine in Innodb" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.