在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;

表数据如下:

P_availability

在测试之前,我们首先通过例子来介绍下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输出结果 :

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

MySQLthread id 8, OS thread handle 4467200, 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 |

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

|                | mydb.product |p_cost     |         3 |

|                | 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 |

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

|                | mydb.product |p_cost     |         3 |

|                | mydb.product |PRIMARY    |         2 |

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

2 rowsin set (0.04 sec)

mysql>SELECT ENGINE_TRANSACTION_ID as ENG_TRX_ID,

object_name,

index_name,

FROMperformance_schema.data_locks WHERE object_name = 'product';

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

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

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

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

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

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

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

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

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

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

6 rowsin set (0.00 sec)

最后提交session1的事务

Session1:

mysql>COMMIT;

QueryOK, 0 rows affected (0.00 sec)

下面我们来测试下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)

Session1:

mysql>COMMIT;

QueryOK, 0 rows affected (0.00 sec)

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.

Session1:

mysql>COMMIT;

QueryOK, 0 rows affected (0.00 sec)

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)

Session1:

mysql>COMMIT;

QueryOK, 0 rows affected (0.00 sec)

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

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

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