In MySQL 8.0 there are two new features designed to support lock handling: NOWAIT and SKIP LOCKED. In this post, we’ll look at how MySQL 8.0 handles hot rows. Up until now, how have you handled locks that are part of an active transaction or are hot rows? It’s likely that you have the application attempt to access the data, and if there is a lock on the requested rows, you incur a timeout and have to retry the transaction. These two new features help you to implement sophisticated lock handling scenarios allowing you to handle timeouts better and improve the application’s performance.
To demonstrate I’ll use this product table.
1 2 3 4 5 6 7 | mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.11 | +-----------+ 1 row in set (0.00 sec) |
1 2 3 4 5 6 7 8 9 | CREATE TABLE `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', PRIMARY KEY (`p_id`), KEY `p_cost` (`p_cost`), KEY `p_name` (`p_name`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; |
Let’s run through an example. The transaction below will lock the rows 2 and 3 if not already locked. The rows will get released when our transaction does a COMMIT or a ROLLBACK. Autocommit is enabled by default for any transaction and can be disabled either by using the START TRANSACTION clause or by setting the Autocommit to 0.
Session 1:
1 2 3 4 5 6 7 8 9 10 | mysql> START TRANSACTION;SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE; Query OK, 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 rows in set (0.00 sec) |
InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks.
We can get the details of a transaction such as the transaction id, row lock count etc using the command innodb engine status or by querying the performance_schema.data_locks table. The result from the innodb engine status command can however be confusing as we can see below. Our query only locked rows 3 and 4 but the output of the query reports 5 rows as locked (Count of Locked PRIMARY+ locked selected column secondary index + supremum pseudo-record). We can see that the row right next to the rows that we selected is also reported as locked. This is an expected and documented behavior. Since the table is small with only 5 rows, a full scan of the table is much faster than an index search. This causes all rows or most rows of the table to end up as locked as a result of our query.
Innodb Engine Status :-
1 2 3 | ---TRANSACTION 205338, ACTIVE 22 sec 3 lock struct(s), heap size 1136, 5 row lock(s) MySQL thread id 8, OS thread handle 140220824467200, query id 28 localhost root |
performance_schema.data_locks (another new feature in 8.0.1):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> SELECT ENGINE_TRANSACTION_ID, CONCAT(OBJECT_SCHEMA, '.', OBJECT_NAME)TBL, INDEX_NAME,count(*) LOCK_DATA FROM performance_schema.data_locks where LOCK_DATA!='supremum pseudo-record' GROUP BY ENGINE_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 rows in set (0.04 sec) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> SELECT ENGINE_TRANSACTION_ID as ENG_TRX_ID, object_name, index_name, lock_type, lock_mode, lock_data FROM performance_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 rows in set (0.00 sec) |
Session 1:
1 2 | mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) |
SELECT FOR UPDATE with innodb_lock_wait_timeout:
The innodb_lock_wait_timeout feature is one mechanism that is used to handle lock conflicts. The variable has default value set to 50 sec and causes any transaction that is waiting for a lock for more than 50 seconds to terminate and post a timeout message to the user. The parameter is configurable based on the requirements of the application.
Let’s look at how this feature works using an example with a select for update query.
1 2 3 4 5 6 7 | mysql> select @@innodb_lock_wait_timeout; +----------------------------+ | @@innodb_lock_wait_timeout | +----------------------------+ | 50 | +----------------------------+ 1 row in set (0.00 sec) |
Session 1:
1 2 3 4 5 6 7 8 9 | mysql> START TRANSACTION;SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE; Query OK, 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 rows in set (0.00 sec) |
Session 2:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> select now();SELECT * FROM mydb.product WHERE p_id=3 FOR UPDATE;select now(); +---------------------+ | now() | +---------------------+ | 2018-06-19 05:29:48 | +---------------------+ 1 row in set (0.00 sec) ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction +---------------------+ | now() | +---------------------+ | 2018-06-19 05:30:39 | +---------------------+ 1 row in set (0.00 sec) mysql> |
Autocommit is enabled (by default) and as expected the transaction waited for lock wait timeout and exited.
Session 1:
1 2 | mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) |
NOWAIT:
The NOWAIT clause causes a query to terminate immediately in the case that candidate rows are already locked. Considering the previous example, if the application’s requirement is to not wait for the locks to be released or for a timeout, using the NOWAIT clause is the perfect solution. (Setting the innodb_lock_wait_timeout=1 in session also has the similar effect).
Session 1:
1 2 3 4 5 6 7 8 9 | mysql> START TRANSACTION;SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE; Query OK, 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 rows in set (0.00 sec) |
Session 2:
1 2 3 | mysql> SELECT * FROM mydb.product WHERE p_id = 3 FOR UPDATE NOWAIT; ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set. mysql> |
Session 1:
1 2 | mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) |
SKIP LOCKED:
The SKIP LOCKED clause asks MySQL to non-deterministically skip over the locked rows and process the remaining rows based on the where clause. Let’s look at how this works using some examples:
Session 1:
1 2 3 4 5 6 7 8 9 | mysql> START TRANSACTION;SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE; Query OK, 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 rows in set (0.00 sec) |
Session 2:
1 2 3 | mysql> SELECT * FROM mydb.product WHERE p_cost = 30 FOR UPDATE SKIP LOCKED; Empty set (0.00 sec) mysql> |
1 2 3 4 5 6 7 8 | mysql> SELECT * from mydb.product where p_id IN (1,2,3,4,5) FOR UPDATE SKIP LOCKED; +------+--------+---------+----------------+ | p_id | p_name | p_cost | p_availability | +------+--------+---------+----------------+ | 1 | Item1 | 10.0000 | YES | | 5 | Item5 | 50.0000 | YES | +------+--------+---------+----------------+ 2 rows in set (0.00 sec) |
Session 1:
1 2 | mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) |
The first transaction is selecting rows 2 and 3 for update(ie locked). The second transaction skips these rows and returns the remaining rows when the SKIP LOCKED clause is used.
Important Notes: As the SELECT … FOR UPDATE clause affects concurrency, it should only be used when absolutely necessary. Make sure to index the column part of the where clause as the SELECT … FOR UPDATE is likely to lock the whole table if proper indexes are not setup for the table. When an index is used, only the candidate rows are locked.
