连接与线程
查看连接信息 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)
来源: http://www.bubuko.com/infodetail-3397312.html