mysql 锁

mysql 锁与事务隔离级别

---

0 问题

1、mysql中的锁是什么？行锁的概念？

2、mysql中的事务的隔离性是什么？

3、mysql的事务隔离性跟锁有什么关系

1 基本概念

innodb存储引擎中两大重要特性—事务、锁，这两个是有关联的，锁都是基于事务内部的一种机制，而事务的隔离，互不冲突的实现都是基于锁实现的。

1.1 mysql中的锁

mysql中的锁是行锁，是加在表索引上的锁，也就是mysql的锁不会直接对record进行加锁，而是对record对应的index上加锁，例如sql语句涉及到表中的某条行数据的更新等操作，如果是通过索引定位到这条数据，那么mysql会对这条record的index加上X锁（排他锁），那么其他想对这个record进行CRUD操作的事务，则只能阻塞，直到刚才是操作commit掉。

mysql中行锁有两种锁

• 排他锁（X锁）：允许获取排他锁的事务更新数据，阻止其他事务获取相同的数据集共享读锁和排他写锁。
• 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

对应的数据库语句如下：

• 排他锁（X锁）：SELECT * FROM table_name WHERE … FOR UPDATE
• 共享锁（S锁）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE

PS：X锁与S锁跟数据库读写没有直接对应关系， FOR UPDATE 加的就是X锁，LOCK IN SHARE MODE 就是加的S锁，只不过默认我们增加、删除、修改操作加的是X锁，读取加的是S锁。

另外，INNODB存储引擎同样支持表锁，为了实现多粒度锁机制，INNODB内部还有意向锁，这两种意向锁都是表锁

• 意向共享锁（IS锁）：事务打算给数据行共享锁，在加锁之前必须要获取表的IS锁

• 意向排他锁（IX锁）：事务打算给数据行排他锁，在加锁之前必须要获取表的IX锁

  锁的冲突情况

  当前锁模式/是否兼容/请求锁模式	X	IX	S	IS
  X	冲突	冲突	冲突	冲突
  IX	冲突	兼容	冲突	兼容
  S	冲突	冲突	兼容	兼容
  IS	冲突	兼容	兼容	兼容

  先验证下S锁、X锁：

事务A：更新一条数据

mysql> begin;
Query OK, 0 rows affected

mysql> update t set a=5 where a=2;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

事务B，查找这条数据

mysql> begin;
Query OK, 0 rows affected

mysql> select * from t where a=2 lock in share mode;

发现事务B一直阻塞在这边，就是因为事务A还没提交，X行锁没有释放，事务B 阻塞。

可以通过查看事务表infor_schema.INNODB_TRX中查看现在的事务

mysql> select * from INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 3846
                 trx_state: LOCK WAIT
               trx_started: 2018-09-01 18:52:12
     trx_requested_lock_id: 3846:28:3:2
          trx_wait_started: 2018-09-01 18:56:06
                trx_weight: 2
       trx_mysql_thread_id: 7
                 trx_query: select * from t where a=2 lock in share mode
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 3
         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
*************************** 2. row ***************************
                    trx_id: 3844
                 trx_state: RUNNING
               trx_started: 2018-09-01 18:44:49
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 6
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     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 rows in set (0.00 sec)

这个INNODB_TRX表是数据库的事务表，记录的当前mysql中的事务状态，从记录中可以看到有两个事务，一个是RUNNING，一个是WAIT. WAIT 状态的事务对应的SQL语句就是查询语句，从事务表看不出这个数据阻塞在哪个锁上，要结合INNODB_LOCKS表查看

mysql> select * from information_schema.INNODB_LOCKS\G;
*************************** 1. row ***************************
    lock_id: 3846:28:3:2
lock_trx_id: 3846
  lock_mode: S
  lock_type: RECORD
 lock_table: `study`.`t`
 lock_index: GEN_CLUST_INDEX
 lock_space: 28
  lock_page: 3
   lock_rec: 2
  lock_data: 0x000000000200
*************************** 2. row ***************************
    lock_id: 3844:28:3:2
lock_trx_id: 3844
  lock_mode: X
  lock_type: RECORD
 lock_table: `study`.`t`
 lock_index: GEN_CLUST_INDEX
 lock_space: 28
  lock_page: 3
   lock_rec: 2
  lock_data: 0x000000000200
2 rows in set, 1 warning (0.00 sec)

刚才WAIT的事务3846对应的锁3846:28:3:2，是S锁，3844事务上的锁3844:28:3:2，是X锁，然后通过INNODB_LOCK_WAIT可以看到锁之间的竞争关系。

mysql> select * from INNODB_LOCK_WAITS\G;
*************************** 1. row ***************************
requesting_trx_id: 3846
requested_lock_id: 3846:28:3:2
  blocking_trx_id: 3844
 blocking_lock_id: 3844:28:3:2
1 row in set, 1 warning (0.00 sec)

可以看到事务3846上的锁3846:28:3:2 在等待事务3844上的阻塞锁3844:28:3:2。这就可以理解查询语句阻塞在数据更新的上了。

所以遇到数据库中的锁竞争情况，可以通过INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS这三张表查看

1.2 一致性非锁定读

上面我们select的时候，加上了lock in share mode. 显式加上S锁，默认我们写SQL不会显示加上，而直接执行查询的话，是可以查询到结果的。

mysql> begin;
Query OK, 0 rows affected

mysql> select * from t where a=2;
+---+
| a |
+---+
| 2 |
+---+
1 row in set

mysql> 

说明查询的时候没有加S锁。这是innodb中一致性非锁定读作用。

一致性的非锁定读： 是指Innodb存储引擎用过多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行在执行DELETE、UPDATE操作，这时读取操作不会因此而等待行上的锁释放，而是会去读取一个快照数据，快照数据上是不需要上锁的，因为快照数据相对于加X锁的原始数据来说，是历史数据，历史数据是没必要修改的。

所以一致性的非锁定读大大提高了数据读取的并发性，这也是默认的INNODB存储引擎的配置

当然一致性非锁定读也是基于事务的隔离级别来的，不同的隔离级别，“快照”的查看方式也不一致，可以参考事务的隔离级别笔记

1.3 行锁算法

行锁算法	锁定内容
Record Lock	记录锁，锁定一个行记录
Gap Lock	间隙锁，锁定一个区间，不包括该行记录
Next-Key Lock	记录锁+间隙锁，锁定一个区间，包括该行记录

虽然介绍有三种算法，但是我感觉Gap Lock 跟Next-key Lock 就是一回事，网上也没有说这两种算法的区别使用场景。

行锁算法是应用在索引上的，不同类型的索引应用不同的行锁算法，索引主要分成两类，聚集索引和非聚集索引

• 聚集索引：索引的排列顺序跟数据行一致，聚集索引其实就是主键索引，主键是唯一的，所以聚集索引的key是唯一的。而且由于数据只能按照一种顺序来排序存储，所以聚集索引是唯一的，一个表只能有一个聚集索引（主键索引）
• 非聚集索引：主键索引之外的都是非聚集索引，比如唯一索引、普通索引等等

PS:聚集索引一定是唯一索引，而唯一索引却不一定是聚集索引，聚集索引可以是联合索引，非聚集索引也可以是联合索引，是否聚集跟是否联合索引没必然联系，不同维度而已

• 间隙锁只是针对insert才有作用，也就是说间隙说只是为了防止插入数据，而允许其他的事务获取间隙锁
• 锁是应用到索引上的，而不是具体的行记录上的，间隙锁不应用在能够唯一确定行记录的索引上，对于唯一索引、主键索引（聚集索引），应用的就是记录锁，而不是间隙锁。比如select * from z where a= 5;由于a是唯一索引/聚集索引，所以这行记录就被锁定了，所以只用记录锁即可；但是不包含联合索引的聚集索引，如果用的是联合索引的聚集索引，除非是所有的联合的列都指定，才会是记录锁，否则也是间隙锁。另外对于select * from z where a>5，虽然能命中的记录也只有一条记录（假设sql种有一条a=10的记录），但是这个锁定区间仍然是间隙锁(5,10],(10,无穷))，因为这句语句虽然命中一条记录，但是命中不等于唯一确定行记录，所以任何插入a>5的事务操作都会阻塞
• 不同的事务级别应用的是不同的锁算法，对于read-committed事务隔离级别，锁就退化成行锁了，而间隙锁的存在是为了消除幻读问题，所以在read-committed隔离级别，由于没有间隙锁，所以会出现幻读，对于repeateable-read隔离级别，应用间隙锁，所以不会出现幻读

间隙锁消除幻读

mysql> select * from z;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 | NULL |
| 2 | 2 |    2 |
| 3 | 1 | NULL |
| 5 | 3 |    3 |
| 9 | 9 |    9 |
+---+---+------+
5 rows in set

事务A加IX锁

mysql> begin;
Query OK, 0 rows affected

mysql> select * from z where b = 3 for update;
+---+---+---+
| a | b | c |
+---+---+---+
| 5 | 3 | 3 |
+---+---+---+
1 row in set

事务B插入数据

mysql> begin;
Query OK, 0 rows affected

mysql> insert into z select 6,4,4;

可以看出事务B阻塞，因为事务A在index上加了间隙锁(2,3),(3,9)如果没有这个间隙锁，则可以插入数据。

PS：插一条个人理解，之所以mysql会区别对待聚集索引与非聚集索引，是因为聚集索引是唯一的，唯一的key能够确定唯一的行数据，所以如果sql语句能够唯一确定行（这里唯一确定而不是命中一条数据），那么锁就会退化成记录锁，否则也是间隙锁；而对于非聚集索引，由于不是唯一索引，所以一个key可能锁定多个记录，所以用间隙锁锁定区间，即便锁定一个具体的key，可能是实现上方便？

所以只有聚集索引的锁才会退化，非聚集索引不会退化

上面提到的幻读问题是在事务中的，而对于一致性非锁定读中，出现的幻读重复读问题倒不是锁能够解决的，而是快照的实现方式不一样，对于已提交读来说，快照取得是最新的快照，所以会出现幻读，而可重复读隔离级别来说，快照是老的快照，所以新的插入数据不会更新进去