你可能不知道的MySQL支持的nowait和skip locked特点

wubx

发布于 2021-06-07 11:39:12

2.2K0

发布于 2021-06-07 11:39:12

文章被收录于专栏：MySQLBeginner

MySQL 8.0 也支持 nowait, skip locked 语法了。在 09 年飞信项目中看到 SQL Server 的这个语法时内心充满了羡慕。现在 MySQL8.0 也可以支持了，不过和其它 DB 实现的有点不一样。我们下面一起看看 MySQL 8.0 对 nowait 和 skip locked 的使用。

了解原理

首先我们需要知道 MySQL 中 nowait, skip locked 是 select ... for update|share 锁定读的语法糖。而且只作用row lock 的锁定。

NOWAIT

该query立即执行，获取不到锁就返回失败

skip locked

该 query 立即执行，获取不到锁的跳过该行。结果中就没有该行记录了。

实战测试

接下来，我们直接看例子:

#初始化数据 session1

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;mysql> INSERT INTO t (i) VALUES(1),(2),(3);
mysql> SELECT * FROM t;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
+---+

#初始化 3 条数据就行了，多了数不过来。

#session1

mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# Session 2:

mysql> select * from t where i=2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

从上面可以看到 for update 执行需要等待锁超时才能退出。接下来我们看一下 for update nowait 执行：

mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

从这里可以看到在 for update nowait 直接执行返回，从错误提示上可以出来因为设置了 nowait 因为获取不到锁直接退出了。

# Session 2:

mysql> START TRANSACTION;
mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+

Query 直接执行返回，但从结果上可以看出来少了一条记录：2，原因是 2 这条记录正处在锁 X 锁状态下，因为使用了 skip locked,直接跳过该条记录。

MySQL 当前锁情况

可以利用 sys.innodb_lock_waits 这个表查询当前锁等待，死锁等情况。

mysql> select * from sys.innodb_lock_waits\G;
*************************** 1. row ***************************
                wait_started: 2021-06-07 00:56:24
                    wait_age: 00:00:10
               wait_age_secs: 10
                locked_table: `wubx`.`t`
         locked_table_schema: wubx
           locked_table_name: t
      locked_table_partition: NULL
   locked_table_subpartition: NULL
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 1633
         waiting_trx_started: 2021-06-07 00:56:24
             waiting_trx_age: 00:00:10
     waiting_trx_rows_locked: 1
   waiting_trx_rows_modified: 0
                 waiting_pid: 345
               waiting_query: select * from t where i=2 for update
             waiting_lock_id: 139804827496688:2:4:3:139804742333008
           waiting_lock_mode: X,REC_NOT_GAP
             blocking_trx_id: 1630
                blocking_pid: 344
              blocking_query: NULL
            blocking_lock_id: 139804827495832:2:4:3:139804742326848
          blocking_lock_mode: X,REC_NOT_GAP
        blocking_trx_started: 2021-06-07 00:54:21
            blocking_trx_age: 00:02:13
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 0
     sql_kill_blocking_query: KILL QUERY 344
sql_kill_blocking_connection: KILL 344
1 row in set (0.00 sec)

使用场景

nowait, skip locked 应该说在人为临时统计时特别常用，但我们一般使用是加 for share 锁，不是 for update。

例如，在读数据时尝试 for share nowait,如果有获取不到锁直接退出，不要影响其它 SQL，再尝试是不是可以用 for share skip locked 执行，获取一个近似值。这个语法给后台人为的临时统计，临时数据抽样增加了更多的灵活性。这方面你有什么好的用法，也欢迎在评论中给大家分享一下。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-06-07，如有侵权请联系 cloudcommunity@tencent.com 删除

sql