MySQL实现SQL Server中UPDLOCK与READPAST组合功能

        碰到一位同事求助解决消息中台一个线上的bug，具体描述如下：

        首先有一张主表记录消息待发送的内容，一张子表记录本条消息的发送状态。若发送成功则将此条消息的发送状态修改为已发送并做逻辑删除。代码通过定时任务每2s轮询子表，如果状态是未发送且没有被删除的记录，则进行发送。发送前代码会对当前消息更新加入UPDLOCK与READPAST，期望下一次轮询时能够自动跳过被锁（发送中）的数据，发送完成后更新状态提交事务，行锁自动释放。

        现在碰到的问题是子表整张表发生死锁，导致消息无法发送。经过代码review，也无法找到互相持有资源的情况，并且即便行锁一直没有释放，也不会影响新生成的消息发送。一开始以为是消息渠道的问题，但是在重启数据库后，bug消失了，所以还是认为由死锁引起的问题。

        机缘巧合下发现有这样一个说法，Sql Server会自动升级锁，这是一个比较玄学的问题，因为对Sql Server用的不是很多，不甚了解。不过这也好解释，发生死锁的原因：

       在sendFeishuMsgJob方法中，对数据表上锁，extracted方法中，需要等待表锁释放才能更新，然而数据表的锁需要extracted执行完才能释放，因此造成了死锁。

       虽然不知道这个解释对不对，但是想到一个问题，如果数据库换成比较常用的MySQL如何实现Sql Server中UPDLOCK与READPAST组合功能。

        首先从事务的隔离级别考虑，MySQL的事务隔离级别分为4种：

1. READ UNCOMMITTED(读未提交)事务A会读到事务B还未提交的数据
2. READ COMMITTED(读已提交)事务A不会读到事务B还未提交的数据
3. REPEATABLE READ (可重复读)(默认隔离级别)事务开启时读到的数据，在事务提交前，是一致的，不会因为外面事务的修改提交而改变开启事务前读到的值
4. SERIALIZABLE(可串行化)严格按照串行序列排队执行事务，一个事务A执行提交结束以后，事务B才会开启

        由此可见，仅通过事务隔离想要达到UPDLOCK和READPAST的效果，需要使用第4种事务隔离级别——串行化。其他3种隔离级别不管修改是否提交，都能读到数据，就很有可能造成消息的重复发送。但是从串行化的描述可知，所有的事务都是排队执行，如果在执行过程中业务处理速度慢，就会造成其他事务等待的情况，成为性能瓶颈。

        好在MySQL 8.0.22 及更高版本中提供了SELECT ... FOR UPDATE SKIP LOCKED方法，可以支持REPEATABLE READ事务隔离级别中，跳过添加行锁的数据行读取。

        示例如下：

事务A

START TRANSACTION;
SELECT * FROM lock_test.for_update_test_2 WHERE id = 1 FOR UPDATE;
SELECT SLEEP(15); 
COMMIT;

事务B

START TRANSACTION;
SELECT * FROM lock_test.for_update_test_2 FOR UPDATE SKIP LOCKED;
COMMIT;

运行结果