mysql 一些知识点记录
当前读与快照读
1. 快照度(snapshot read)
简单的select操作，属于快照读，不加锁
select * from TABLE where ?;
2. 当前读(Current Read)
特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
意向锁（Intention Lock）
(1) 意向锁是表锁，通常是由数据库自己控制上锁的，InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁
(2) 意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向
- 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
- 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁
select ... lock in share mode，要设置IS锁；
select ... for update，要设置IX锁；
(3) 意向锁协议(intention locking protocol)并不复杂：
- 事务要获得某些行的S锁，必须先获得表的IS锁
- 事务要获得某些行的X锁，必须先获得表的IX锁
(4) 由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行，其兼容互斥表如下：
IS IX
IS 兼容 兼容
IX 兼容 兼容
既然意向锁之间都相互兼容，那其意义在哪里呢？它会与共享锁/排它锁互斥，其兼容互斥表如下：
S X
IS 兼容 互斥
IX 互斥 互斥
共享/排它锁(Shared and Exclusive Locks)
共享/排它锁：
- 事务拿到某一行记录的共享S锁，才可以读取这一行
- 事务拿到某一行记录的排它X锁，才可以修改或者删除这一行
从并发的角度来看就是（类似读写锁）：
- 多个事务可以拿到一把S锁，读读可以并行
- 而只有一个事务可以拿到X锁，写写/读写必须互斥
共享/排它锁的潜在问题是，不能充分的并行，解决思路是数据多版本，也就上面提到的快照
插入意向锁（Insert Intention Lock）
插入意向锁是这些锁中最坑的一个，因为它虽然包含意向两个字，但它并不是意向锁
对于对已有数据行的修改与删除，必须加强互斥锁X锁，但那对于数据的插入，并不需要加这么强的锁机制，所以引入了插入意向锁
官方文档上来说，多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。
Insert Intention Lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.
插入意向锁与间隙锁是不兼容的
所以当select ... for update，select 没有命中 某行的时候产生间隙锁，这个时候往该间隙insert 就会失败
===============================
SELECT * FROM information_schema.innodb_trx
trx_id：事务ID。
trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started：事务开始时间。
trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started：事务开始等待的时间。
trx_weight：事务的权重。
trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。
trx_query：事务正在执行的 SQL 语句。
trx_operation_state：事务当前操作状态。
trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked：当前执行 SQL 的行锁数量。
trx_lock_structs：事务保留的锁数量。
trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。
trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified：事务更改的行数。
trx_concurrency_tickets：事务并发票数。
trx_isolation_level：当前事务的隔离级别。
trx_unique_checks：是否打开唯一性检查的标识。
trx_foreign_key_checks：是否打开外键检查的标识。
trx_last_foreign_key_error：最后一次的外键错误信息。
trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。
trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识。
主键索引 和 唯一索引 所以需要 order by 主键索引 或者干脆就是索引
范围会导致组合索引失效
幻读
正常的读写锁，为了可重复读，只能做到读读并发，并不能做到读写并发
mysql通过多版本控制，使用快照做到了读写并发
但是可重复读本身解决不了幻读问题
说几个常见的幻读的场景
mysql root@127.0.0.1:mock_comments> show create table t;
+---------+--------------------------------------+
| Table | Create Table |
|---------+--------------------------------------|
| t | CREATE TABLE `t` ( |
| | `a` int(11) NOT NULL, |
| | `b` int(11) DEFAULT NULL, |
| | PRIMARY KEY (`a`), |
| | KEY `idx_b` (`b`) |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------+
1 row in set
Time: 0.087s
mysql root@127.0.0.1:mock_comments> select * from t;
+-----+-----+
| a | b |
|-----+-----|
| 1 | 1 |
| 3 | 3 |
| 5 | 5 |
| 6 | 5 |
| 9 | 5 |
| 10 | 5 |
| 25 | 25 |
| 26 | 26 |
| 27 | 27 |
| 33 | 33 |
+-----+-----+
10 rows in set
A: set autocommit = 0;
B: set autocommit = 0;
A: select * from t;
B: select * from t;
A: insert into t values(12, 12)
A: commit;
B: select * from t;
这个时候B查到的是快照的数据，没有`a=12`记录
B: insert into t values(12, 12)
然而却插不进去`a=12`数据，报主键冲突
参考
何登成的技术博客
InnoDB并发插入，居然使用意向锁？
InnoDB并发如此高，原因竟然在这？
InnoDB行锁，如何锁住一条不存在的记录？
解决死锁之路 - 常见 SQL 语句的加锁分析
MySQL 加锁处理分析
一分钟深入Mysql的意向锁——《深究Mysql锁》
一分钟深入Mysql的意向锁——《深究Mysql锁》
ICP 索引条件下推