背景
最近要做一个批跑服务, 基本逻辑就是定时扫描数据库的记录, 有满足条件的就进行处理(一条记录代表一个任务,以下任务与记录含义相同). 要求支持多机部署批跑服务.
批跑支持多机部署实现方案
要实现多机部署, 只要保证每个批跑服务实例每次只获取一条记录, 处理完再获取下一条即可. 其中最种要的是避免不同的实例获取到同一条记录,即所谓抢任务.
先看表结构设计:
create database if not exists ae;
create table ae.task (
id int primary key,
status int);
-- status为0说明任务可处理,其它不可处理以上是简化的表结构,但足以说明本文试图说明的问题.
要避免抢任务, oracle的做法, 直接
update ae.task set status=1 where status=0 and rownum = 1 returning id即可.
mysql的要啰嗦一点:
select id from ae.task where status=0; -- 得到ID
update ae.task where id = ${id} and status=0;这两个sql,第一个sql用于获取符合条件的任务, 第二个sql用户将任务锁定. 在并发的场景下, 有可能不同的批跑实例的第一个SQL会返回相同的记录, 但第二个sql只有一个会更新成功, 通过判断affected rows即可知道哪个锁定成功. 锁定成功的继续处理本任务, 锁定失败的继续处理其它任务.
问题现象
管理后台提交了一个任务后, 两个批跑实例恰好同时启动, 进入抢任务环节. 结果发现异常, 其中一个实例成功抢到任务, 但另一个实例则挂死了:
抢到任务的实例:
2015-11-23 19:42:01|INFO|exec_task.php|40||get one task: 11
...
2015-11-23 19:42:01|INFO|exec_task.php|107||line_count: 9
2015-11-23 19:42:01|INFO|exec_task.php|147||fork child success: 8346
2015-11-23 19:42:01|INFO|exec_task.php|264||[0] pid: 8346, start: 0, stop: 0
2015-11-23 19:42:01|INFO|exec_task.php|147||fork child success: 8347
2015-11-23 19:42:01|INFO|exec_task.php|264||[1] pid: 8347, start: 1, stop: 1
2015-11-23 19:42:01|INFO|exec_task.php|147||fork child success: 8348
2015-11-23 19:42:01|INFO|exec_task.php|264||[2] pid: 8348, start: 2, stop: 2
2015-11-23 19:42:01|INFO|exec_task.php|147||fork child success: 8349
...没有抢到任务的实例:
2015-11-23 19:42:01|INFO|function.inc.php|100||task_id 11 is locked by another process, get next task
2015-11-23 19:42:01|INFO|function.inc.php|100||task_id 11 is locked by another process, get next task
2015-11-23 19:42:01|INFO|function.inc.php|100||task_id 11 is locked by another process, get next task
2015-11-23 19:42:01|INFO|function.inc.php|100||task_id 11 is locked by another process, get next task
2015-11-23 19:42:01|INFO|function.inc.php|100||task_id 11 is locked by another process, get next task
2015-11-23 19:42:01|INFO|function.inc.php|100||task_id 11 is locked by another process, get next task
2015-11-23 19:42:01|INFO|function.inc.php|100||task_id 11 is locked by another process, get next task
2015-11-23 19:42:01|INFO|function.inc.php|100||task_id 11 is locked by another process, get next task
2015-11-23 19:42:01|INFO|function.inc.php|100||task_id 11 is locked by another process, get next task可以看到没有抢到任务的实例进入了死循环.
原因分析
按照我们之前的设计, 如果第二条SQL锁定任务的时候失败了, 获取下一个任务. 应当不会死循环. 死循环的原因是因为没有抢到任务的实例, 在执行第一个SQL的时候, 一直返回了相同的记录(id=11,实际上当时也只有一条记录)
请注意, 抢到任务的实例抢到任务后, 会把状态更新并提交, 按说抢不到任务的实例会看到此状态更新,并导致第一条sql查不到数据,然后 正常退出.
而事实上抢不到任务的实例看不到此变化, 说明事务隔离级别(Transaction Isolation Level)不是"READ COMMITED", 而是其它. 经确认, 级别是"REPEATABLE-READ"
mysql> select @@TX_ISOLATION;
+-----------------+
| @@TX_ISOLATION |
+-----------------+
| REPEATABLE-READ |"REPEATABLE-READ" 看到的数据是事务启动时的样子,所以看不到抢到任务的实例对任务状态的修改. 进而导致死循环.
请注意执行第一个SQL查询满足条件的任务是在一个事务内进行的. 此事务实际上是业务的需要, 除了获取到任务,还需要获取其它资源,如果获取不到其它 资源, 则rollback任务,以便下次处理.
ORACLE相应的事务隔离级别是"Serializable Isolation Level", 如上描述的这个场景, 在ORACLE下的反应是抢不到任务的实例在试图更新任务状态的 时候,会返回一个"ORA-08177: Cannot serialize access for this transaction"错误, 程序也可以正常退出. 详见<> 第9章"Overview of Oracle Database Transaction Isolation Levels"
mysql在"REPEATABLE-READ"的事务隔离级别上的表现是不能让人满意的. 查询到的数据是事务启动时的样子,但更新的时候看到的数据又是其它事务提交 后的结果,并且update也没有错误提示.
而"SERIALIZABLE"更糟糕, 如果同时开了两个session, 干脆直接锁表了, 谁了更新不了. 这就势必造成另一个问题, 既然大家都更新不了,那就rollback事务, 重试呗. 但是重试也是很有可能大家再同时开了事务,又锁死了, 一直死循环. 为了解决这种情况,可能的做法是, 各自等待一个随机时间再重试,让随机打破这个僵局. 不知道是否有其它办法,欢迎指教.
解决方法
- 修改session的事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;1.不断查询满足条件的任务不要放到一个事务里. 发现"affected rows"为0,更新不到数据时, 事务rollback,重新启动事务. 即在循环里不断开启事务而不是在事务里不断循环.
- 还有一个办法是开事务然后select for update, 但是这种方法会导致锁表, 必须等待其它事务提交后才能返回. 当初我进行设计的时候,是计划使用select for update的方式的, 但是最终没有使用, 现在回想, 可能是没有开事务, 结果两个实例都查询到了相同的记录, 所以被我否定了. 但是看我另一个文章 <>又似乎可能是由于锁表而弃用了, 原因已经不可考了.
但从本个需求来说, 似乎使用select for update来让把表锁住会更简单.
另一个问题
你以为抢到任务的实例就可以高枕无忧了吗, 错了! 等他高高兴兴处理完任务, 要把任务状态置为成功时, 发现这个任务居然被没有抢到任务的实例给锁了, 自已只能得到一个锁超时的错误
2015-11-23 19:42:52|ERR|function.inc.php|113||SQL fail: Lock wait timeout exceeded; try restarting transaction请期待下一个问题分析.
补充说明
今天回来确认了一下, 实际上ORACEL的update task set status = 1 where status = 0 and rownum = 1 returning taskid 这个SQL也会把表锁住.
所以可以用@flygogo 在30楼提出的方法模拟oracle 的returning
SET @update_id := 0;
update ae.task set status=1, id = (SELECT @update_id := id) where status=0 limit 1; SELECT @update_id;
而postgresql的update似乎没有limit 1之类的限定只更新一条的写法?
同时ORACLE和postgresql的select for update 也都会锁表.
所以就本文所讨论的范围来说, 似乎不能说是两个凡是. 叉!
再补充说明
差点被绕晕了. 其实本文所指出的mysql在"REPEATABLE-READ"事务隔离级别下的表现是奇怪的,不直观的,这点值得注意. 明明select出来的数据是可更新, 而更新时候又没有成功, 会让人非常疑惑. 而为oracel在"Serializable"级别下发现数据已经被更新了之后,抛出"ORA-08177"的做法才更直观更合适.
本文另一个意义是分享了一种不锁表实现队列的方法
文章转载自 开源中国社区[https://www.oschina.net]