Innodb中为什么lockinsharemode在showengine看不到行锁信息
这篇文章主要介绍“Innodb中为什么lock in share mode在show engine看不到行锁信息”，在日常操作中，相信很多人在Innodb中为什么lock in share mode在show engine看不到行锁信息问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Innodb中为什么lock in share mode在show engine看不到行锁信息”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：空间域名、网页空间、营销软件、网站建设、翁源网站维护、网站推广。
一、问题提出
不知道有没有朋友和我一样用lock in share mode做加锁实验，但是却在show engine innodb status中看不到加锁信息，今天刚好有朋友在问@在树枝上吹风，今天就做了一下简单的debug，因为我也挺纳闷的。(我喜欢多问一个为什么也挺累的)
问题如下：
首先我开启了我的打印行锁参数，让加锁输出到日志中
MySQL> show variables like '%gaopeng%'; +--------------------------------+-------+| Variable_name | Value | +--------------------------------+-------+ | gaopeng_mdl_detail | OFF || innodb_gaopeng_row_lock_detail | ON | +--------------------------------+-------+
然后跑如下语句
mysql> show create table t \G *************************** 1. row *************************** Table: t Create 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 | 5 || 10 | 10 | 10 || 15 | 15 | 15 || 20 | 20 | 20 || 25 | 25 | 25 |+----+------+------+6 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> select * from t where id=0 lock in share mode; +----+------+------+| id | c | d |+----+------+------+| 0 | 0 | 0 |+----+------+------+1 row in set (4.21 sec)
按理说这个时候应该在主键ID=0这一行上了LOCK_S，但是show engine innodb却看不到加锁信息如下：
------------ TRANSACTIONS ------------ Trx id counter 241482 Purge done for trx's n:o < 241482 undo n:o < 0 state: running but idle History list length 182 Total number of lock structs in row lock hash table 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422211785606640, not started 0 lock struct(s), heap size 1160, 0 row lock(s) ---TRANSACTION 422211785605248, not started 0 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; ##这个语句我就显示的开始了一个读写事物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>
再来看看
---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 root TABLE LOCK table `test`.`t2` trx id 241482 lock mode IX RECORD 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 32 0: len 6; hex 00000000451d; asc E ;; 1: len 6; hex 00000003af4a; asc J;; 2: len 7; hex 3c000000453040; asc < E0@;; 3: len 4; hex 80000001; asc ;; TABLE LOCK table `test`.`t` trx id 241482 lock mode IS RECORD 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 the
caller holds the lock mutex and more importantly it will release the lock
mutex 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);//建立一个结构体，目的是做not start 事物的打印
ut_list_map(trx_sys->mysql_trx_list, print_not_started); //这个地方打印出那些事物状态是no start的事物，但是这里存在一个问题，等会看代码在看。mysql_trx_list是全事物。
const trx_t* trx;
TrxListIterator trx_iter; //这个迭代器是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) { //通过迭代器进行迭代 ，显然这里不会有只读事物的信息。
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());
}这个函数是调用的逻辑。
结构体PrintNotStarted括号重载
void operator()(const trx_t* trx)
{
ut_ad(trx->in_mysql_trx_list);
ut_ad(mutex_own(&trx_sys->mutex)); /* See state transitions and locking rules in trx0trx.h */
if (trx_state_eq(trx, TRX_STATE_NOT_STARTED)) {//这里我们发现只有状态为TRX_STATE_NOT_STARTED才会进行输出
fputs("---", m_file);
trx_print_latched(m_file, trx, 600);
}
}我们这里可以看到只有状态为TRX_STATE_NOT_STARTED才会输出为not start状态。
TrxListIterator迭代器初始化代码
TrxListIterator() : m_index()
{ /* We iterate over the RW trx list first. */
m_trx_list = &trx_sys->rw_trx_list;
}我们这里可以看到只有读写事物才会进行锁结构的输出。
到此，关于“Innodb中为什么lock in share mode在show engine看不到行锁信息”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注创新互联网站，小编会继续努力为大家带来更多实用的文章！
名称栏目：Innodb中为什么lockinsharemode在showengine看不到行锁信息
文章路径：http://scyanting.com/article/iidoic.html
