MySQL锁

Dec 1, 2020 Mysql
Share on:
  1. 表锁 一般在执行 DDL 语句时，譬如 ALTER TABLE 就会对整个表进行加锁
mysql> lock table products read;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from products where id = 100;
 
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

我们会在会话开始的地方使用 lock 命令将后面所有要用到的表加上锁，在锁释放之前，我们只能访问这些加锁的表，不能访问其他的表，最后通过 unlock tables 释放所有表锁。这样的好处是，不会发生死锁！

对于读锁

  • 持有读锁的会话可以读表，但不能写表；
  • 允许多个会话同时持有读锁；
  • 其他会话就算没有给表加读锁，也是可以读表的，但是不能写表；
  • 其他会话申请该表写锁时会阻塞，直到锁释放。

对于写锁

  • 持有写锁的会话既可以读表，也可以写表；
  • 只有持有写锁的会话才可以访问该表，其他会话访问该表会被阻塞，直到锁释放；
  • 其他会话无论申请该表的读锁或写锁，都会阻塞，直到锁释放。

锁的释放规则如下

  • 使用 UNLOCK TABLES 语句可以显示释放表锁；
  • 如果会话在持有表锁的情况下执行 LOCK TABLES 语句，将会释放该会话之前持有的锁；
  • 如果会话在持有表锁的情况下执行 START TRANSACTION 或 BEGIN 开启一个事务，将会释放该会话之前持有的锁；
  • 如果会话连接断开，将会释放该会话所有的锁。
  1. 行锁(InnoDb才有) 常见的增删改（INSERT、DELETE、UPDATE）语句会自动对操作的数据行加写锁，查询的时候也可以明确指定锁的类型，SELECT … LOCK IN SHARE MODE 语句加的是读锁，SELECT … FOR UPDATE 语句加的是写锁。

行锁这个名字听起来像是这个锁加在某个数据行上，实际上这里要指出的是：在 MySQL 中，行锁是加在索引上的。

  1. 主键 主键索引（Primary Index）又被称为聚簇索引（Clustered Index） 非主键索引（Secondary Index，又称为二级索引、辅助索引）被称为 非聚簇索引 如果没有主键，InnoDB 会试着使用一个非空的唯一索引（Unique nonnullable index）代替；如果没有这种索引，会定义一个隐藏的主键。所以 InnoDb 的表一定会有主键索引

  2. 二叉查找树 IMAGE

  3. B树 IMAGE

  4. B+树 在 B+ 树里，内节点（非叶子节点）中不再保存数据，而只保存用于查找的 key，并且所有的叶子节点按顺序使用链表进行连接，这样可以大大的方便范围查询，只要先查到起始位置，然后按链表顺序查找，一直查到结束位置即可。 IMAGE

  5. 加锁

mysql> update students set score = 100 where id = 49;

InnoDb 存储引擎会在 id = 49 这个主键索引上加一把 X 锁。

mysql> update students set score = 100 where name = 'Tom';

InnoDb 存储引擎会在 name = ‘Tom’ 这个索引上加一把 X 锁，同时会通过 name = ‘Tom’ 这个二级索引定位到 id = 49 这个主键索引，并在 id = 49 这个主键索引上加一把 X 锁。 IMAGE

mysql> update students set level = 3 where score >= 60;

IMAGE

  1. 行锁种类
  • LOCK_ORDINARY：也称为 Next-Key Lock，锁一条记录及其之前的间隙，这是 RR 隔离级别用的最多的锁，从名字也能看出来；
  • LOCK_GAP：间隙锁，锁两个记录之间的 GAP，防止记录插入；
  • LOCK_REC_NOT_GAP：只锁记录；
  • LOCK_INSERT_INTENSION：插入意向 GAP 锁，插入记录时使用，是 LOCK_GAP 的一种特例。

使用间隙锁可以防止其他事务在这个范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而不会出现幻读现象。 Next-key 锁 是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

  1. 行锁的模式
  • LOCK_IS：读意向锁；
  • LOCK_IX：写意向锁；
  • LOCK_S：读锁；
  • LOCK_X：写锁；
  • LOCK_AUTO_INC：自增锁；

9.1 读写锁 读锁，又称共享锁（Share locks，简称 S 锁），加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。

写锁，又称排他锁（Exclusive locks，简称 X 锁），或独占锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。（注意：这里说的读都是当前读，快照读是无需加锁的，记录上无论有没有锁，都可以快照读）

9.2 读写意向锁 意向锁为表级锁，也可分为读意向锁（IS 锁）和写意向锁（IX 锁）。当事务试图读或写某一条记录时，会先在表上加上意向锁，然后才在要操作的记录上加上读锁或写锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。

下面是各个表锁之间的兼容矩阵： IMAGE 总结

  • 意向锁之间互不冲突；
  • S 锁只和 S/IS 锁兼容，和其他锁都冲突；
  • X 锁和其他所有锁都冲突；
  • AI 锁只和意向锁兼容；

为啥存在？用处~~ 表锁锁定了整张表，而行锁是锁定表中的某条记录，它们俩锁定的范围有交集，因此表锁和行锁之间是有冲突的。譬如某个表有 10000 条记录，其中有一条记录加了 X 锁，如果这个时候系统需要对该表加表锁，为了判断是否能加这个表锁，系统需要遍历表中的所有 10000 条记录，看看是不是某条记录被加锁，如果有锁，则不允许加表锁，显然这是很低效的一种方法，为了方便检测表锁和行锁的冲突，从而引入了意向锁。

9.3 AUTO_INC 锁 AUTO_INC 锁又叫自增锁（一般简写成 AI 锁），它是一种特殊类型的表锁，当插入的表中有自增列（AUTO_INCREMENT）的时候可能会遇到。

innodb_autoinc_lock_mode = 0 使用AUTO_INC 锁,并发性比较差,自增值可能会出现中断; mysql默认innodb_autoinc_lock_mode = 1,既平衡了并发性，又能保证同一条 INSERT 语句分配的自增值是连续的。 innodb_autoinc_lock_mode = 2,并发性能最高，按顺序依次分配自增值，不会预分配,同一条 INSERT 语句内获得的自增值可能不连续，主从数据集会出现数据不一致。

参考https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

  1. 在 MySQL 中观察行锁
mysql> select * from information_schema.innodb_locks;

10.1 记录锁 记录锁和记录锁或 Next-key 锁冲突，所以想观察到记录锁，可以让两个事务都对同一条记录加记录锁，或者一个事务加记录锁另一个事务加 Next-key 锁。 事务 A 执行：

mysql> begin;
mysql> select * from accounts where id = 5 for update;
+----+----------+-------+
| id |     name | level |
+----+----------+-------+
|  5 | zhangsan |     7 |
+----+----------+-------+
1 row in set (0.00 sec)

事务 B 执行：

mysql> begin;
mysql> select * from accounts where id = 5 lock in share mode;

事务 B 阻塞，出现锁竞争，查看锁状态：

mysql> select * from information_schema.innodb_locks;
+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 3108:53:3:4 | 3108        | S         | RECORD    | `accounts` | PRIMARY    |         53 |         3 |        4 | 5         |
| 3107:53:3:4 | 3107        | X         | RECORD    | `accounts` | PRIMARY    |         53 |         3 |        4 | 5         |
+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

10.2 间隙锁 间隙锁只和插入意向锁冲突，而且是先加间隙锁，然后加插入意向锁时才会冲突。 事务 A 执行（id 为主键，且 id = 3 这条记录不存在）：

mysql> begin;
mysql> select * from accounts where id = 3 lock in share mode;
Empty set (0.00 sec)

事务 B 执行：

mysql> begin;
mysql> insert into accounts(id, name, level) value(3, 'lisi', 10);

事务 B 阻塞，出现锁竞争，查看锁状态：

mysql> select * from information_schema.innodb_locks;
+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id     | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 3110:53:3:4 | 3110        | X,GAP     | RECORD    | `accounts` | PRIMARY    |         53 |         3 |        4 | 3         |
| 3109:53:3:4 | 3109        | S,GAP     | RECORD    | `accounts` | PRIMARY    |         53 |         3 |        4 | 3         |
+-------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)

10.3 Next-key 锁

参考 https://www.aneasystone.com/archives/2017/11/solving-dead-locks-two.html

闽ICP备12003472号-7