Skip to content

MySQL Locking

锁机制

表锁

  1. Regular locks

    LOCK TABLES kittens READ/WRITE;

  2. intention locks

    IS: Intention shared. IX: Intention exclusive. Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t. Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.

    IS/IX锁的目的是为了与表锁之间互斥，比如有个记录正在更新，别的操作要修改表结构，这样的操作会阻塞。

    intention-lock

    注意这个表里面的S、X指的是表锁，不是行锁。

  3. auto-increment locks

    全表计数器。

行锁

  1. record lock

    记录锁。锁在index上，如果列没有index，会自动创建一个隐藏的index加锁，但是需要先做全表扫描。

    SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE。会锁住所有c1 = 10的记录，其他session不能插入、删除、更新c1 = 10的任何记录。

  2. gap lock

    gap lock目的是为了解决幻读问题。

    gap-lock

  3. next key lock

    record lock加上record两侧的gap lock，一起合称next key。

  4. insert intent locks

    插入前对gap的意向锁，跟gap锁不同，不会阻塞同样gap的不同记录插入。

SQL语句锁

  1. 一致性读，锁读

    一致性读（快照读）：每个事务中从首次select开始，后面其他事务对数据的改变就不会被自己看到了。使用mvcc的方式，实际上每次读的都是快照。普通select就是一致性读。

    锁读（当前读）：每次读的都是最新数据库中提交的数据。select for update, select lock in share mode, update, delete, insert等看到的数据都是锁读。

  2. select ... from

    低于SERIALIZABLE级别：无锁(innodb 默认是repeatable read) SERIALIZABLE级别 table: IS lock index record：s next-key locks unique index record：record lock

  3. SELECT ... FROM ... LOCK IN SHARE MODE

    table: IS lock index record：s next-key locks unique index record：record lock

  4. SELECT ... FROM ... FOR UPDATE

  5. UPDATE ... WHERE ...

  6. DELETE FROM ... WHERE ...

    table: IX lock index record：x next-key locks unique index record：record lock

  7. INSERT

    插入前：insert intention gap lock，在这条记录即将要插入的那个gap上。 插入后：x record lock 当发生duplicate-key error：加s index record lock 某些请下会导致deadlock

  8. REPLACE 没有记录：与insert相同 duplicate-key：x next-key lock

next lock举例

表结构：

mysql> desc ttest;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | NO   | MUL | NULL    |       |
| b     | int(11) | NO   | PRI | NULL    |       |
| memo  | text    | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

插入数据：

mysql> select * from ttest;
+----+----+-----------+
| a  | b  | memo      |
+----+----+-----------+
|  2 |  2 | record 2  |
|  5 |  5 | record 5  |
| 10 | 10 | record 10 |
+----+----+-----------+
3 rows in set (0.00 sec)

对普通索引（非唯一索引）的锁读操作，会产生next key lock。

假设对a=5的记录做锁读操作（a是非唯一索引）。我们用update举例，select for update 和 delete会产生同样的效果。

client 1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> update ttest set memo='record 5 updating' where a=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

client 2:
mysql>
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> update ttest set memo='record 5 updating in session 2' where a=5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
mysql> insert into ttest values(3, 3, 'record 3');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
mysql>
mysql> insert into ttest values(6, 6, 'record 6');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
mysql> insert into ttest values(11, 11, 'record 11');
Query OK, 1 row affected (0.01 sec)
mysql>
可以看到，a=3和a=5都插入不了，阻塞了。a=11可以插入。

对唯一所以字段b，不会产生gap lock，只有record lock。

隔离级别

隔离级别

  • 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
  • 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
  • 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
  • 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

隔离级别是针对一致性读定义的，只影响普通的select ... from产生查出的结果，不影响select for update、delete，update，insert这些操作看到的数据，后面这几个操作永远读的是最新提交的数据。

提交读(Read Committed)

read-commited

上面这种情况，就是不可重复读，也就是常说的幻读（Phantom Rows）。

可重复读(Repeated Read)

repeatable-read1 repeatable-read2

解决了幻读（Phantom Rows）。

Read Uncommitted

别的回话未提交的修改也可以读到，实际中没什么意义。

Serializable

直接表锁串行读写，实际中也意义不大。

参考

  1. https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html
  2. https://tech.meituan.com/innodb-lock.html