最近要做一个批跑服务, 基本逻辑就是定时扫描数据库的记录, 有满足条件的就进行处理(一条记录代表一个任务,以下任务与记录含义相同). 要求支持多机部署批跑服务.
要实现多机部署, 只要保证每个批跑服务实例每次只获取一条记录, 处理完再获取下一条即可. 其中最种要的是避免不同的实例获取到同一条记录,即所谓抢任务.
先看表结构设计:
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事务, 重试呗. 但是重试也是很有可能大家再同时开了事务,又锁死了, 一直死循环. 为了解决这种情况,可能的做法是, 各自等待一个随机时间再重试,让随机打破这个僵局. 不知道是否有其它办法,欢迎指教.
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]
