内容简介：标签： Mysql、Next-KeyLock、插入意向锁查看连接信息mysql 非企业版本只支持一个线程一个链接
标签： Mysql、Next-KeyLock、插入意向锁
连接与线程
查看连接信息 show processlist
+----+------+------------------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+------------------+------+---------+------+----------+------------------+ | 3 | root | 172.17.0.1:60542 | test | Query | 0 | starting | show processlist | | 5 | root | 172.17.0.1:60546 | test | Sleep | 4168 | | <null> | | 8 | root | 172.17.0.1:60552 | test | Sleep | 4170 | | <null> | +----+------+------------------+------+---------+------+----------+------------------+
mysql 非企业版本只支持一个线程一个链接
查看线程模型 show variables like 'thread_handling'
+-----------------------------------------+---------------------------+ | Variable_name | Value | +-----------------------------------------+---------------------------+ | thread_handling | one-thread-per-connection | +-----------------------------------------+---------------------------+
【 事务提交策略】
有两个隐藏事务提交时间点需要注意，第一个是 autocommit=1
Mysql session
级别的自动提交变量，所有 ORM
框架中的事务提交控制都会受到这个字段影响，默认情况下当前语句会自动提交，但是如果是显示 begin transaction
开启事务需要自行手动提交。有些时候 ORM 框架会根据一些设置或者策略，将 autocommit
设置为0。
第二个就是，DDL操作前都会隐式提交当前事务，有些脚本将DML和DDL混合在一起使用，这样会有一致性问题。DDL会自动提交当前事务。因为DDL在5.7之前都是不支持事务原则操作的。（Mysql8.0已经支持DDL事务性）
Next-Key Lock 排查
Next-Key Lock 只发生在 RR（REPEATABLE-READ） 隔离级别下。
Mysql 有很多类型对种锁， 表锁
、 record lock
、 gap lock
、 意向共享/排他锁
、 插入意向锁
、 元数据锁
、 Auto_Incr自增锁
，排除掉 元数据锁
、Auto_Incr自增锁 之后，剩下的锁组合使用最多的就是在RR隔离级别下。
RR隔离级别是默认事务隔离级别，也是 Mysql 的强项之一，在RR隔离级别下事务有最大的吞吐量，而且不会出现幻读问题。Next-Key Lock 就是为了解决这个问题，简单讲 record lock+gap lock 就是 Next-Key Lock 。
_幻读_的根本问题就是出现在记录的边界值上，比如我们统计年龄大于30岁的人数： select count(1) peoples where age>30
这个语句有可能每次查询得到的结果集都是不一样的，因为只要符合 age>30
的记录进到我们的 peoples
表中就会被查询条件命中。
所以要想解决幻读不仅不允许记录的空隙被插入记录外，还要防止两遍记录被修改，因为如果前后两条记录被修改了那区间就会变大，就会有幻读出现。
我们看个例子。
CREATE TABLE `peoples` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_peoples_age` (`age`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
+----+-----+ | id | age | +----+-----+ | 1 | 20 | | 2 | 30 | | 3 | 35 | | 4 | 40 | +----+-----+
为了方便调试，将 innodb 获取锁的超时时间调大点
show variables like '%innodb_lock_wait%' set innodb_lock_wait_timeout=600
开启两个会话。
session A id=8: begin select count(1) from peoples where age>30 for update;
session B id=5: begin insert into peoples(age) values(31)
show processlist
找到连接的id。
***************************[ 1. row ]*************************** Id | 3 User | root Host | 172.17.0.1:60542 db | test Command | Query Time | 0 State | starting Info | show processlist ***************************[ 2. row ]*************************** Id | 5 User | root Host | 172.17.0.1:60546 db | test Command | Query Time | 394 State | update Info | insert into peoples(age) values(31) ***************************[ 3. row ]*************************** Id | 8 User | root Host | 172.17.0.1:60552 db | test Command | Sleep Time | 396 State | Info | <null>
- 事务
select * from information_schema.innodb_trx \G
查看事务执行情况。
***************************[ 1. row ]*************************** trx_id | 457240 trx_state | LOCK WAIT trx_started | 2020-01-27 06:08:12 trx_requested_lock_id | 457240:131:4:4 trx_wait_started | 2020-01-27 06:09:25 trx_weight | 6 trx_mysql_thread_id | 5 trx_query | insert into peoples(age) values(31) trx_operation_state | inserting trx_tables_in_use | 1 trx_tables_locked | 1 trx_lock_structs | 5 trx_lock_memory_bytes | 1136 trx_rows_locked | 4 trx_rows_modified | 1 trx_concurrency_tickets | 0 trx_isolation_level | REPEATABLE READ trx_unique_checks | 1 trx_foreign_key_checks | 1 trx_last_foreign_key_error | <null> trx_adaptive_hash_latched | 0 trx_adaptive_hash_timeout | 0 trx_is_read_only | 0 trx_autocommit_non_locking | 0 ***************************[ 2. row ]*************************** trx_id | 457239 trx_state | RUNNING trx_started | 2020-01-27 06:07:59 trx_requested_lock_id | <null> trx_wait_started | <null> trx_weight | 3 trx_mysql_thread_id | 8 trx_query | <null> trx_operation_state | <null> trx_tables_in_use | 0 trx_tables_locked | 1 trx_lock_structs | 3 trx_lock_memory_bytes | 1136 trx_rows_locked | 5 trx_rows_modified | 0 trx_concurrency_tickets | 0 trx_isolation_level | REPEATABLE READ trx_unique_checks | 1 trx_foreign_key_checks | 1 trx_last_foreign_key_error | <null> trx_adaptive_hash_latched | 0 trx_adaptive_hash_timeout | 0 trx_is_read_only | 0 trx_autocommit_non_locking | 0
457240 事务状态是 LOCK WAIT
在等待锁，457239事务状态是 RUNNING
执行中，正在等待事务提交。
- 锁
select * from information_schema.innodb_locks \G
查看锁的占用情况。
***************************[ 1. row ]*************************** lock_id | 457240:131:4:4 lock_trx_id | 457240 lock_mode | X,GAP lock_type | RECORD lock_table | `test`.`peoples` lock_index | idx_peoples_age lock_space | 131 lock_page | 4 lock_rec | 4 lock_data | 35, 7 ***************************[ 2. row ]*************************** lock_id | 457239:131:4:4 lock_trx_id | 457239 lock_mode | X lock_type | RECORD lock_table | `test`.`peoples` lock_index | idx_peoples_age lock_space | 131 lock_page | 4 lock_rec | 4 lock_data | 35, 7
innodb_locks 表包含了已经获取到的锁信息和请求锁的信息。lock_index字段表示锁走的索引，record锁都是基于索引完成。
根据上面事务457240状态是获取锁， lock_data | 35, 7
，表示请求的数据。而事务457239占用了当前X锁。
- 锁等待
select * from information_schema.innodb_lock_waits
查看锁等待信息。
***************************[ 1. row ]*************************** requesting_trx_id | 457240 requested_lock_id | 457240:131:4:4 blocking_trx_id | 457239 blocking_lock_id | 457239:131:4:4
457240 事务需要获取131:4:4锁，457239 事务占用了131:4:4锁。
-
innodb 监视器
show engine innodb status
LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422032240994144, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 457240, ACTIVE 394 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1 MySQL thread id 5, OS thread handle 140556966967040, query id 105 172.17.0.1 root update insert into peoples(age) values(31) ------- TRX HAS BEEN WAITING 165 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 131 page no 4 n bits 72 index idx_peoples_age of table `test`.`peoples` trx id 457240 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000023; asc #;; 1: len 4; hex 00000007; asc ;; ------------------ ---TRANSACTION 457239, ACTIVE 407 sec 3 lock struct(s), heap size 1136, 5 row lock(s) MySQL thread id 8, OS thread handle 140556966696704, query id 104 172.17.0.1 root
MySQL thread id 5 正在准备上插入意向锁， 插入意向锁
本质上是加间隙锁，是为了保证最大并发插入，不相关的行插入不受到互斥。thread id 5 需要保证在插入前加上间隙锁，主要是防止并发插入带来的一致性问题。
session 5 和 session 8 都没有操作到 id=3，age=35的记录，但是却被X+Gap Lock 锁住，只有这样才能解决幻读问题。
作者：王清培（趣头条 Tech Leader）
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网
猜你喜欢:
- 认识绝对定位，相对定位
- 移动端页面头部固定定位的绝对定位实现
- webgl(three.js)实现室内定位,楼宇bim、实时定位三维可视化解决方案——第五课
- IP 地址怎么定位？
- # CSS 绝对定位释义
- 如何定位渲染耗时瓶颈
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Reversing
艾拉姆（Eilam,E.） / 韩琪、杨艳、王玉英、李娜 / 电子工业出版社 / 2007-9 / 79.00元
本书描述的是在逆向与反逆向之间展开的一场旷日持久的拉锯战。作者Eldad Eilam以一个解说人的身份为我们详尽地评述了双方使用的每一招每一式的优点与不足。 书中包含的主要内容有：操作系统的逆向工程；.NET平台上的逆向工程；逆向未公开的文件格式和网络协议；逆向工程的合法性问题；拷贝保护和数字版权管理技术的逆向工程；防止别人对你的代码实施逆向工程的各种技术；恶意程序的逆向工程；反编译器的基本......一起来看看 《Reversing》 这本书的介绍吧!
