mysql 队列 实现并发读
程序员文章站
2024-02-13 08:08:28
一个 mysql 表可以看作是一个队列,每一行为一个元素。每次查询得到满足某个条件的最前面的一行,并将它从表中删除或者改变它的状态,使得下次查询不会得到它。在没有并发访问的...
一个 mysql 表可以看作是一个队列,每一行为一个元素。每次查询得到满足某个条件的最前面的一行,并将它从表中删除或者改变它的状态,使得下次查询不会得到它。在没有并发访问的情况下,简单地用 select 得到一行,再用update(或者delete)语句修改之,就可以实现。
select * from targets where status='c' limit 1;
update targets set status='d' where id='id';
如果有并发访问,在select和update语句之间可能会存在其他地select查询,导致同一行被取出多次。为了保证在并发情况下仍然能正常工作,一种思路是使用数据库地锁来防止,就像在多线程环境下所做地一样。总之,要是的查询和修改为一个原子操作,不被其它的访问干扰。mysql 5 支持存储过程,可以用它来实现。
单条 update 语句应该原子操作的,可以利用这个特性来保证并发访问情况下队列的正常工作。每次取元素时,先用 update 修改符合条件的第一行,然后再得到该行。可惜 update 语句没有返回值,重新用普通的select的话又很难找到刚被改过的那条记录。
这里用到一个小技巧:在 update 时加上 id=last_insert_id(id),再用 select last_insert_id() 即可得到刚修改的那条记录的id。还有一个问题,当表中不存在符合条件的记录,导致 update 失败时,last_insert_id() 会保留原来地值不变,因而不能区分队列中是否还有元素。
row_count() 返回上一个语句影响的行数,把它作为 select 的一个条件,可以帮助解决这个问题。
最后,支持并发访问的完整解决方案为:
update targets set status='d', id=last_insert_id(id) where status='c' limit 1;
select * from targets where row_count()>0 and id=last_insert_id();
更新:在实现带优先级的队列时这种方法有问题,带有 order by ... 条件的 update 语句非常慢,例如:
而单独查询和更新则是很快的:
select id from targets where status='c' order by schedule asc limit 1;
update targets set status='d' where id='id';
原来这是mysql的bug-12915,一年多以前提出来的,虽然关闭了,却只解决了部分问题,尚不支持where,见mysql 5.0.15 的 changlog。无奈,上面这种巧妙的方法也没有实用价值了。
最后采用了一种折衷方案,如下:
update targets, (select id from targets where status='c' and schedule<current_timestamp order by schedule asc limit 1) tmp set status='d' where targets.id=last_insert_id(tmp.id);
select * from targets where row_count()>0 and id=last_insert_id();
复制代码 代码如下:
select * from targets where status='c' limit 1;
update targets set status='d' where id='id';
如果有并发访问,在select和update语句之间可能会存在其他地select查询,导致同一行被取出多次。为了保证在并发情况下仍然能正常工作,一种思路是使用数据库地锁来防止,就像在多线程环境下所做地一样。总之,要是的查询和修改为一个原子操作,不被其它的访问干扰。mysql 5 支持存储过程,可以用它来实现。
单条 update 语句应该原子操作的,可以利用这个特性来保证并发访问情况下队列的正常工作。每次取元素时,先用 update 修改符合条件的第一行,然后再得到该行。可惜 update 语句没有返回值,重新用普通的select的话又很难找到刚被改过的那条记录。
这里用到一个小技巧:在 update 时加上 id=last_insert_id(id),再用 select last_insert_id() 即可得到刚修改的那条记录的id。还有一个问题,当表中不存在符合条件的记录,导致 update 失败时,last_insert_id() 会保留原来地值不变,因而不能区分队列中是否还有元素。
row_count() 返回上一个语句影响的行数,把它作为 select 的一个条件,可以帮助解决这个问题。
最后,支持并发访问的完整解决方案为:
复制代码 代码如下:
update targets set status='d', id=last_insert_id(id) where status='c' limit 1;
select * from targets where row_count()>0 and id=last_insert_id();
更新:在实现带优先级的队列时这种方法有问题,带有 order by ... 条件的 update 语句非常慢,例如:
复制代码 代码如下:
update targets set status='d' where status='c' order by schedule asc limit 1;
而单独查询和更新则是很快的:
复制代码 代码如下:
select id from targets where status='c' order by schedule asc limit 1;
update targets set status='d' where id='id';
原来这是mysql的bug-12915,一年多以前提出来的,虽然关闭了,却只解决了部分问题,尚不支持where,见mysql 5.0.15 的 changlog。无奈,上面这种巧妙的方法也没有实用价值了。
最后采用了一种折衷方案,如下:
复制代码 代码如下:
update targets, (select id from targets where status='c' and schedule<current_timestamp order by schedule asc limit 1) tmp set status='d' where targets.id=last_insert_id(tmp.id);
select * from targets where row_count()>0 and id=last_insert_id();