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. 行锁种类
  2. LOCK_ORDINARY:也称为 Next-Key Lock,锁一条记录及其之前的间隙,这是 RR 隔离级别用的最多的锁,从名字也能看出来;
  3. LOCK_GAP:间隙锁,锁两个记录之间的 GAP,防止记录插入;
  4. LOCK_REC_NOT_GAP:只锁记录;
  5. LOCK_INSERT_INTENSION:插入意向 GAP 锁,插入记录时使用,是 LOCK_GAP 的一种特例。

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

  1. 行锁的模式
  2. LOCK_IS:读意向锁;
  3. LOCK_IX:写意向锁;
  4. LOCK_S:读锁;
  5. LOCK_X:写锁;
  6. 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