mysql skip locked,NOWAIT AND SKIP LOCKED

mysql8.0 对 select ...from ... for update进行了扩展，支持 for update nowait 以及 for update skip locked；

另外，新增了 select ...from ... for share ，用来替代 select ...from ... lock in share mode，且保留 lock in share mode，以便向下兼容。

同样的 select ...from ... for share 也支持 for share nowait 以及 for share skip locked 选项。

语法如下：

[FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] | LOCK IN SHARE MODE]]

NOWAIT：表示无法获取到锁时直接返回错误，不用等待 innodb_lock_wait_timeout 的时间，再返回报错。

SKIP LOCKED：表示跳过那些被其他session锁住的记录，返回符合条件的剩余记录。

OF tablename,tablename：表示只锁某些表。

PS:

NOWAIT 和 SKIP LOCKED 对基于 statement 的复制是不安全的.

NOWAIT 和 SKIP LOCKED 仅适用于行级锁。

场景一和场景二验证NOWAIT和SKIP LOCKED，场景三验证 OF tablename。

场景一，建表t1不带主键。

### session1：

mysql> use test ;

Database changed

mysql> create table t1 ( id int , name VARCHAR(100));

Query OK, 0 rows affected (0.07 sec)

mysql> insert into t1 values(1,'a'),(2,'b'),(3,'c');

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> begin ;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id =1 for update ;

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

| id | name |

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

| 1 | a |

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

1 row in set (0.00 sec)

### session2：

mysql> use test ;

Database changed

mysql> select * from t1 for update ; ### 等待innodb_lock_wait_timeout的时间后返回报错。

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

mysql> select * from t1 for update nowait; ## 因为有NOWAIT，加锁失败，直接返回报错。

ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

## 利用SKIP LOCKED跳过加锁的行，返回0条。因为t1没有索引，故session1的for update是对整张表加了锁。

mysql> select * from t1 for update skip locked;

Empty set (0.00 sec)

## 通过下面的sql查看t1表的哪些行被锁住。

mysql> SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;

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

| object_name | lock_type | lock_mode | lock_data | lock_status |

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

| t1 | TABLE | IX | NULL | GRANTED |

| t1 | RECORD | X | supremum pseudo-record | GRANTED |

| t1 | RECORD | X | 0x000000000209 | GRANTED |

| t1 | RECORD | X | 0x00000000020A | GRANTED |

| t1 | RECORD | X | 0x00000000020B | GRANTED |

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

5 rows in set (0.00 sec)

场景二，建表t2含主键。

### session1：

mysql> use test ;

Database changed

mysql> create table t2 ( id int PRIMARY key , name VARCHAR(100));

Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2 values(1,'a'),(2,'b'),(3,'c');

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> begin ;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2 where id =1 for update ;

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

| id | name |

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

| 1 | a |

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

1 row in set (0.00 sec)

### session2：

mysql> use test

Database changed

mysql> select * from t2 for update ; ## 同场景一

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

mysql> select * from t2 for update nowait; ## 同场景一

ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

## 利用SKIP LOCKED跳过加锁的行，返回2条。因为t1.id是主键，故session1的for update只对id=1的记录加一条记录锁。于是就把剩下的两条记录返回给了session2.

mysql> select * from t2 for update skip locked;

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

| id | name |

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

| 2 | b |

| 3 | c |

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

2 rows in set (0.00 sec)

场景三，基于上面的t1和t2表

session1：

mysql> use test;

Database changed

mysql> begin ;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * from t1 , t2 where t1.id = t2.id for update of t2 ; ### 只锁住 t2 表满足条件的记录，这里是全部记录

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

| id | name | id | name |

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

| 1 | a | 1 | a |

| 2 | b | 2 | b |

| 3 | c | 3 | c |

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

3 rows in set (0.00 sec)

session2：

mysql> use test ;

Database changed

mysql> select * from t2 for update skip locked ; ## 都锁住了，返回0行

Empty set (0.00 sec)

mysql> select * from t1 for update skip locked ; ## t1表没加锁，正常访问

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

| id | name |

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

| 1 | a |

| 2 | b |

| 3 | c |

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

3 rows in set (0.00 sec)