mysql: exclusive select like SQS?
Is there any way to simulate Amazon SQS message selection mechanism on mysql (exclusive selects)? I need this to use o开发者_运维技巧ne mysql table for multiple ec2 instances that will process and delete rows from it.
select ... for update
doesn't quite what I want because it locks a thread on select, and not returns not locked instances.
Eventually I came to this idea (don't implemented now):
alter target table, add columns lock_name, lock_time
on each iteration:
2.1. update target_table set lock_time = now(), lock_name = 'controller_instance_name' where lock_time < now() - visibilityTimeout
2.2. select * from target_table where lock_name = 'controller_instance_name' and lock_time > now() - visibilityTimeout
2.3 for each item in select
2.3.1 handle it
2.3.2 remove it from table: delete from target_table where id = ?
Each controller instance will be able to select row after it was locked more than on visibilityTimeout value.
精彩评论