提示:公众号展示代码会自动折行,建议横屏阅读。
订座在现实生活中是一种很常见的场景,比较常见的有火车票席位选择,电影院席位选择等等。那么如何实现订座功能呢?应用程序可能有很多种不同的实现方式,当然,肯定离不开数据库。这里将介绍一种纯数据库的实现方式。
设想我们有一张座位表如下:
CREATE TABLE seats (
seat_no INT PRIMARY KEY,
booked ENUM('YES', 'NO') DEFAULT 'NO') ENGINE=InnoDB;
表中有100个席位,从0到99。例如我们要预定席位2,3,我们可以先开启事务,锁定席位:
START TRANSACTION;SELECT * FROM seats WHERE seat_no IN (2,3) AND booked = 'NO' FOR UPDATE;
SELECT… FOR UPDATE语句返回结果有如下三种情况:
情况3对用户来说,意味着卡死,完全不能接受。为什么会发生等待?在InnoDB的锁系统(lock system)中,席位2如果被一个事务上了X(写锁)锁或者IX锁(意向更新锁),那么下一个事务要对席位2上X锁或者IX锁的事务,就要等待。这是由事务本身的特性(ACID)决定的。
那么是否有一种方法避免等待以及后续可能发生的超时呢?MySQL 8.0 提供的新功能SKIP LOCKED/NOWAIT就可以。 SKIP LOCKED的意思是跳过那些已经被其他事务锁定了的席位。使用如下SKIP LOCKED语句进行席位锁定,那么返回的结果集可能为空,2或3,2和3。当结果集不为空时,返回的席位即被锁定成功。
SELECT * FROM seats WHERE seat_no IN (2,3) AND booked = 'NO'FOR UPDATE SKIP LOCKED;
NOWAIT的意思是如果碰到被其他事务锁定的席位,不等待并直接返回错误。使用如下NOWAIT语句进行席位锁定,那么返回结果集2和3,要么返回错误。
SELECT * FROM seats WHERE seat_no IN (2,3) AND booked = 'NO'FOR UPDATE NOWAIT;
如果返回错误,如下:
ERROR 3572 (HY000): Do not wait for lock.
如果成功锁定两个席位,通过如下语句查询锁系统的状态:
SELECT thread_id, object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;+-----------+-------------+-----------+-----------+-----------+-------------+| thread_id | object_name | lock_type | lock_mode | lock_data | lock_status |
+-----------+-------------+-----------+-----------+-----------+-------------+| 43 | seats | TABLE | IX | NULL | GRANTED |
| 43 | seats | RECORD | X | 2 | WAITING |
| 42 | seats | TABLE | IX | NULL | GRANTED |
| 42 | seats | RECORD | X | 2 | GRANTED |
| 42 | seats | RECORD | X | 3 | GRANTED |
+-----------+-------------+-----------+-----------+-----------+-------------+
SKIP LOCKED还可以很方便的用来进行随机分配席位。例如我们只需要锁定两个空的席位就可以通过如下语句实现。
SELECT * FROM seats WHERE booked = 'NO' LIMIT 2 FOR UPDATE SKIP LOCKED;
SKIP LOCKED/NOWAIT功能只针对行锁(record lock),不包括表锁(table lock),元数据锁(metadata lock/MDL)。因此,带有SKIP LOCKED/NOWAIT的查询语句依然可能会因为表锁或元数据库锁而阻塞。元数据锁是MySQL Server层用来保护数据库对象的并发访问的一致性而创建的,数据库对象不仅包括表,同时包括库,函数,存储过程,触发器,事件等等。表和行锁是InnoDB存储引擎内部为了保证事务的一致性而创建的不同粒度的锁。
另外,SKIP LOCKED/NOWAIT还可以配合FOR SHARE使用,并且可以与单表绑定。例如:
SELECT seat_noFROM seats JOIN seat_rows USING ( row_no )WHERE seat_no IN (2,3) AND seat_rows.row_no IN (12)AND booked = 'NO'FOR UPDATE OF seats SKIP LOCKEDFOR SHARE OF seat_rows NOWAIT;
在InnoDB中,实现SKIP LOCKED/NOWAIT具体实现如下:
SKIP LOCKED/NOWAIT可以非常高效地实现订座这个场景,作为InnoDB部分(WL#8919: InnoDB: Implement NOWAIT and SKIP LOCKED)的原作者,我也期待着大家来分享该功能更多的使用场景。
腾讯数据库技术团队对内支持微信红包,彩票、数据银行等集团内部业务,对外为腾讯云提供各种数据库产品,如CDB、CTSDB、CKV、CMongo, 腾讯数据库技术团队专注于增强数据库内核功能,提升数据库性能,保证系统稳定性并解决用户在生产过程中遇到的问题,并对生产环境中遇到的问题及知识进行分享。