Sql - Next-key locks

在开始之前我们先复习下幻读的概念

• Phantom Rows
  • The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.
  • 当同一个查询在不同的时间产生不同的行集时，事务中就会出现 所谓的幻读问题。例如，如果 a SELECT执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻读”行。

行锁的算法

• Record Locks 记录锁
  • 记录锁是对索引记录的锁，例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;阻止任何其它事务插入、删除、更新 c1=10的行，记录锁总是锁定索引记录
  • 可以看到记录锁锁的是索引，因此当不存在索引或者主键时就会使用默认的聚簇索引，但是此时行锁会将整张表锁住
• Gap Locks 间隙锁
  • 间隙锁是索引间隙之间的锁，或在第一条索引记录之前或最后一条索引之后的间隙上加锁，例如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;，此时阻止其它事务插入15，无论该列中是否已经存在该值，因为该范围的间隙与所有的现有值之间的间隙都被锁定
• Next-Key Lock
  • 锁定一个范围，并且锁定记录本身，用来解决幻读问题，也就是记录锁+间隙锁

举个栗子

• 表结构如下

    mysql> desc users;
    +------------+-------------+------+-----+---------+-------+
    | Field      | Type        | Null | Key | Default | Extra |
    +------------+-------------+------+-----+---------+-------+
    | id         | int         | YES  | MUL | NULL    |       |
    | name       | varchar(25) | YES  |     | NULL    |       |
    | age        | int         | YES  |     | NULL    |       |
    | created_at | timestamp   | YES  |     | NULL    |       |
    +------------+-------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

    // session1 和 session2同时开启事务
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    // session2 使用当前读
    mysql> select * from users where id > 22 for update;
    +------+------+------+------------+
    | id   | name | age  | created_at |
    +------+------+------+------------+
    |   34 | xxx  | NULL | NULL       |
    +------+------+------+------------+
    1 row in set (0.00 sec)

    // session1 插入数据，由于锁定的是22以后的数据，因此可以插入成功
    mysql> insert into users(id,name) values(13,45);
    Query OK, 1 row affected (0.00 sec)
  
    // session1 此时如果插入数据或者更新数据在记录锁的范围之内，此时会发现事务处于阻塞的状态，这是由于next-key-locks的存在
    mysql> insert into users(id,name) values(25,45); // 此时处于blocked状态
    mysql> update users set name='uuu' where id=34; // 此时处于blocked状态

  在此时当session2提交之后，session1才会继续提交，这也就说明了next-key-locks锁的存在

通过上面的栗子我们可以发现事务会发生阻塞的现象 我们借此拓展下造成死锁的情况

• 死锁

    // 表结构如下
    mysql> desc users;
    +------------+-------------+------+-----+---------+-------+
    | Field      | Type        | Null | Key | Default | Extra |
    +------------+-------------+------+-----+---------+-------+
    | id         | int         | YES  | MUL | NULL    |       |
    | name       | varchar(25) | YES  |     | NULL    |       |
    | email      | varchar(25) | YES  |     | NULL    |       |
    | created_at | timestamp   | YES  |     | NULL    |       |
    | updated_at | timestamp   | YES  |     | NULL    |       |
    +------------+-------------+------+-----+---------+-------+
    5 rows in set (0.01 sec)

    // session1
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
  
    mysql> update users set name='lock1' where id=1;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
  
    // session2
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
  
    mysql> update users set name='lock2' where id =2;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    // session1
    mysql> update users set name='lock1' where id=2; //处于阻塞状态
  
    // session2
    // 产生了死锁
    mysql> update users set name='lock2' where id =1;
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

• 死锁时导致事务没有正常提交，此时可以通过下面的sql查看当前的事务

    mysql> SELECT * from information_schema.INNODB_TRX\G
    *************************** 1. row ***************************
                        trx_id: 4643
                     trx_state: RUNNING
                   trx_started: 2022-09-06 14:32:15
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 6
           trx_mysql_thread_id: 8
                     trx_query: NULL
           trx_operation_state: NULL
             trx_tables_in_use: 0
             trx_tables_locked: 1
              trx_lock_structs: 5
         trx_lock_memory_bytes: 1128
               trx_rows_locked: 6
             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
           trx_schedule_weight: NULL
    1 row in set (0.00 sec)

本作品采用《CC 协议》，转载必须注明作者和本文链接