前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >你可能不知道的MySQL支持的nowait和skip locked特点

你可能不知道的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

代码语言:javascript
复制
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

代码语言:javascript
复制
mysql> START TRANSACTION;
mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# Session 2:

代码语言:javascript
复制
mysql> select * from t where i=2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

代码语言:javascript
复制
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:

代码语言:javascript
复制
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 这个表查询当前锁等待,死锁等情况。

代码语言:javascript
复制
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 删除

本文分享自 MySQLBeginner 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 了解原理
  • 实战测试
  • MySQL 当前锁情况
  • 使用场景
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档