> ## Content Index
> Fetch the complete content index at: https://blog.hacking.pub/llms.txt
> Use this file to discover other available public pages before exploring further.

# mysql 一些知识点记录
- URL: https://blog.hacking.pub/2019/02/28/mysql-jian-xi-suo/
- Published: 2019-02-28T14:01:56.000Z
- Updated: 2019-02-28T14:20:18.000Z
- Author: chainhelen
- Tags: 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锁

```js
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 主键索引 或者干脆就是索引  
[范围会导致组合索引失效](https://zhuanlan.zhihu.com/p/29118331?ref=blog.hacking.pub)

#### 幻读

正常的读写锁，为了`可重复读`，只能做到`读读并发`，并不能做到`读写`并发  
`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`数据，报主键冲突   

```

> 参考  
> [何登成的技术博客](http://hedengcheng.com/?p=771&ref=blog.hacking.pub)  
> [InnoDB并发插入，居然使用意向锁？](https://www.itcodemonkey.com/article/8000.html?ref=blog.hacking.pub)  
> [InnoDB并发如此高，原因竟然在这？](https://mp.weixin.qq.com/s?%5F%5Fbiz=MjM5ODYxMDA5OQ==&mid=2651961444&idx=1&sn=830a93eb74ca484cbcedb06e485f611e&chksm=bd2d0db88a5a84ae5865cd05f8c7899153d16ec7e7976f06033f4fbfbecc2fdee6e8b89bb17b&scene=21&ref=blog.hacking.pub#wechat%5Fredirect)  
> [InnoDB行锁，如何锁住一条不存在的记录？](https://mp.weixin.qq.com/s?%5F%5Fbiz=MjM5ODYxMDA5OQ==&mid=2651961431&idx=1&sn=4f46fbada3d99ca6cf74b305d06c1ac6&chksm=bd2d0d8b8a5a849d8cb5a616c957abde7a6485cd2624372b84a5459eed081bd95429a09572f8&scene=21&ref=blog.hacking.pub#wechat%5Fredirect)  
> [解决死锁之路 - 常见 SQL 语句的加锁分析](https://www.aneasystone.com/archives/2017/12/solving-dead-locks-three.html?ref=blog.hacking.pub)  
> [MySQL 加锁处理分析](http://hedengcheng.com/?p=771&ref=blog.hacking.pub#%5FToc374698312)  
> [一分钟深入Mysql的意向锁——《深究Mysql锁》](https://juejin.im/entry/5adca48df265da0b9c1037c8?ref=blog.hacking.pub)  
> [一分钟深入Mysql的意向锁——《深究Mysql锁》](https://blog.csdn.net/zcl%5Flove%5Fwx/article/details/82015281?ref=blog.hacking.pub)  
> [ICP 索引条件下推](https://www.cnblogs.com/thrillerz/p/4166720.html?ref=blog.hacking.pub)