在MySQL8.0中，关于锁的处理，新增了两个新的特性，NOWAIT和SKIP LOCKED。这篇文章将为大家介绍MySQL8.0如何处理热数据查询中锁等待的问题。

在MySQL8.0之前，大家是怎么处理热数据的查询呢，大概过程就是应用访问到热数据，发现数据被其他事务锁定了，那么就会一直等待，然后最终锁等待超时，然后重新尝试执行该事务。在MySQL8.0中，NOWAIT和SKIPLOCKED这两个新特性可以帮助大家来处理热数据查询里锁的问题，从而更好的处理锁超时并提供更高的数据库并发性能。

MySQL版本：

mysql>select @@version;

+-----------+

|@@version |

+-----------+

|8.0.11    |

+-----------+

1 row inset (0.00 sec)

测试表结构：

CREATETABLE `product` (

`p_id`int(11) NOT NULL AUTO_INCREMENT,

`p_name`varchar(255) DEFAULT NULL,

`p_cost`decimal(19,4) NOT NULL,

`p_availability`enum('YES','NO') DEFAULT 'NO',

PRIMARYKEY (`p_id`),

KEY`p_cost` (`p_cost`),

KEY`p_name` (`p_name`)

)ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;

表数据如下：

MySQL行级锁

在测试之前，我们首先通过例子来介绍下MySQL的行级锁，我们知道MySQL的事务是自动提交，所以我们首先通过start transaction来开启一个新事务，然后执行下面的SQL，下面的事务会锁定第2行和第3行，直到我们commit或者rollback时，锁才会释放。

Session1：

mysql>START TRANSACTION;

mysql>SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE;

QueryOK, 0 rows affected (0.00 sec)

+------+--------+---------+----------------+

| p_id |p_name | p_cost  | p_availability |

+------+--------+---------+----------------+

|    2 | Item2 | 20.0000 | YES            |

|    3 | Item3 | 30.0000 | YES            |

+------+--------+---------+----------------+

2 rowsin set (0.00 sec)

我们先了解下InnoDB执行行级锁定的方式：MySQL通过查询条件扫描表数据的索引时，它会在遇到的索引记录上设置共享锁或排它锁。因此，行级锁实际上是索引记录锁。

我们可以使用命令show engine innodb status或通过查询performance_schema.data_locks表来获取事务的详细信息，例如事务ID，锁定行的信息等。然而，查询到的锁的结果可能会令人有点困惑，如下所示。我们的查询仅锁定了第2行和第3行，但查询的输出显示锁定了5行记录（锁定PRIMARY的行数+锁定查询列二级索引的行数+伪记录行数）。我们可以看到我们查询条件的行旁边的行也被加锁了。为什么这样呢，这其实是MySQL内部设计的加锁方式。由于表只有5行数据，因此表的完整扫描比索引搜索快得多。所以MySQL会任务对表的大部分数据或者全部数据加锁效率更高。

Innodb Engine Status输出结果 :

---TRANSACTION205338, ACTIVE 22 sec

3 lockstruct(s), heap size 1136, 5 row lock(s)

MySQLthread id 8, OS thread handle 140220824467200, query id 28 localhost root

performance_schema.data_locks(8.0.1里面的另一个新特性):

mysql>SELECT ENGINE_TRANSACTION_ID,

 CONCAT(OBJECT_SCHEMA, '.',

 OBJECT_NAME)TBL,

 INDEX_NAME,count(*) LOCK_DATA

FROMperformance_schema.data_locks

whereLOCK_DATA!='supremum pseudo-record'

GROUP BYENGINE_TRANSACTION_ID,INDEX_NAME,OBJECT_NAME,OBJECT_SCHEMA;

+-----------------------+--------------+------------+-----------+

|ENGINE_TRANSACTION_ID | TBL          |INDEX_NAME | LOCK_DATA |

+-----------------------+--------------+------------+-----------+

|                205338 | mydb.product |p_cost     |         3 |

|                205338 | mydb.product |PRIMARY    |         2 |

+-----------------------+--------------+------------+-----------+

2 rowsin set (0.04 sec)

 

mysql>SELECT ENGINE_TRANSACTION_ID,

 CONCAT(OBJECT_SCHEMA, '.',

 OBJECT_NAME)TBL,

 INDEX_NAME,count(*) LOCK_DATA

FROMperformance_schema.data_locks

where LOCK_DATA!='supremumpseudo-record'

GROUP BYENGINE_TRANSACTION_ID,INDEX_NAME,OBJECT_NAME,OBJECT_SCHEMA;

+-----------------------+--------------+------------+-----------+

|ENGINE_TRANSACTION_ID | TBL          |INDEX_NAME | LOCK_DATA |

+-----------------------+--------------+------------+-----------+

|                205338 | mydb.product |p_cost     |         3 |

|                205338 | mydb.product |PRIMARY    |         2 |

+-----------------------+--------------+------------+-----------+

2 rowsin set (0.04 sec)

 

mysql>SELECT ENGINE_TRANSACTION_ID as ENG_TRX_ID,

 object_name,

 index_name,

 lock_type,

 lock_mode,

 lock_data

FROMperformance_schema.data_locks WHERE object_name = 'product';

+------------+-------------+------------+-----------+-----------+-------------------------+

|ENG_TRX_ID | object_name | index_name | lock_type | lock_mode | lock_data               |

+------------+-------------+------------+-----------+-----------+-------------------------+

|     205338 | product     | NULL       | TABLE     | IX       | NULL                    |

|     205338 | product     | p_cost     | RECORD   | X         |0x800000000000140000, 2 |

|     205338 | product     | p_cost     | RECORD   | X         |0x8000000000001E0000, 3 |

|     205338 | product     | p_cost     | RECORD   | X         |0x800000000000320000, 5 |

|     205338 | product     | PRIMARY    | RECORD   | X         | 2                       |

|     205338 | product     | PRIMARY    | RECORD   | X         | 3                       |

+------------+-------------+------------+-----------+-----------+-------------------------+

6 rowsin set (0.00 sec)

 

最后提交session1的事务

Session1：

mysql>COMMIT;

QueryOK, 0 rows affected (0.00 sec)

innodb_lock_wait_timeout

下面我们来测试下SELECT FOR UPDATE with innodb_lock_wait_timeout

，这个就是目前8.0之前一直使用的锁处理方式。innodb_lock_wait_timeout这个变量默认值是50秒，也就是说MySQL遇到锁等待时，会等待50秒后然后向应用程序发送超时信息。这个变量的值是可以根据应用实际情况进行修改的。

我们来看个例子：

mysql>select @@innodb_lock_wait_timeout;

+----------------------------+

|@@innodb_lock_wait_timeout |

+----------------------------+

|                         50 |

+----------------------------+

1 row inset (0.00 sec)

 

Session1：

 

mysql>START TRANSACTION;SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost<=30 FOR UPDATE;

QueryOK, 0 rows affected (0.00 sec)

+------+--------+---------+----------------+

| p_id |p_name | p_cost  | p_availability |

+------+--------+---------+----------------+

|    2 | Item2 | 20.0000 | YES            |

|    3 | Item3 | 30.0000 | YES            |

+------+--------+---------+----------------+

2 rowsin set (0.00 sec)

 

Session2：

mysql>select now();SELECT * FROM mydb.product WHERE p_id=3 FOR UPDATE;select now();

+---------------------+

|now()               |

+---------------------+

|2018-06-19 05:29:48 |

+---------------------+

1 row inset (0.00 sec)

ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transaction

+---------------------+

|now()               |

+---------------------+

|2018-06-19 05:30:39 |

+---------------------+

1 row inset (0.00 sec)

mysql>

 

Session1：

mysql>COMMIT;

QueryOK, 0 rows affected (0.00 sec)

NOWAIT

NOWAIT这个特性是指在应用程序查询过程中，发现有锁等待会立即返回信息。可以参考下前面的示例，如果应用程序的要求是不等待锁定被释放或超时，则使用NOWAIT是完美的解决方案。 （在会话中设置innodb_lock_wait_timeout = 1也具有类似的效果）。

Session1：

mysql>START TRANSACTION;

mysql>SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE;

QueryOK, 0 rows affected (0.00 sec)

+------+--------+---------+----------------+

| p_id |p_name | p_cost  | p_availability |

+------+--------+---------+----------------+

|    2 | Item2 | 20.0000 | YES            |

|    3 | Item3 | 30.0000 | YES            |

+------+--------+---------+----------------+

2 rowsin set (0.00 sec)

 

Session2：

mysql>  SELECT * FROM mydb.product WHERE p_id = 3 FORUPDATE NOWAIT;

ERROR3572 (HY000): Statement aborted because lock(s) could not be acquiredimmediately and NOWAIT is set.

mysql>

 

Session1：

mysql>COMMIT;

QueryOK, 0 rows affected (0.00 sec)

SKIP LOCKED

SKIP LOCKED要求MySQL跳过锁定的行并根据where子句处理剩余的行。让我们看看如何使用一些例子：

Session1：

mysql>START TRANSACTION;

mysql>SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE;

QueryOK, 0 rows affected (0.00 sec)

+------+--------+---------+----------------+

| p_id |p_name | p_cost  | p_availability |

+------+--------+---------+----------------+

|    2 | Item2 | 20.0000 | YES            |

|    3 | Item3 | 30.0000 | YES            |

+------+--------+---------+----------------+

2 rowsin set (0.00 sec)

 

Session2：

mysql>SELECT * FROM mydb.product WHERE p_cost = 30 FOR UPDATE SKIP LOCKED;

Emptyset (0.00 sec)

mysql>

 

Session1：

mysql>COMMIT;

QueryOK, 0 rows affected (0.00 sec)

可以看到第一个事务是加锁进行查询数据。 第二个事务使用SKIP LOCKED特性，在查询时跳过了事务1加锁的行并返回剩余的行。

重要说明：由于SELECT... FOR UPDATE子句会影响并发性，因此只应在绝对必要时使用它。 还有确保将where里面的列创建合适的索引，因为FOR UPDATE可能会锁定整个表。

关注我们，就是对我们最好的鼓励。