mysql row level read lock to replace messaging queue

Question

I have a mysql table in which I store jobs to be processed. mainly text fields of raw data the will take around a minute each to process.

I have 2 servers pulling data from that table processing it then deleting.

To manage the job allocation between the 2 servers I am currently using amazon SQS. I store all the row IDS that need processing in SQS, the worker servers poll SQS to get new rows to work on.

The system currently works but SQS adds a layer of complexity and costs that I feel are overkill to achieve what I am doing.

I am trying to implement the same thing without SQS and was wondering if there is any way to read lock a row so that if one worker is working on one row, no other worker can select that row. Or if there's any better way to do it.

Sergio Tulentsev · Accepted Answer · 2012-03-19 18:54:47Z

1

A simple workaround: add one more column to your jobs table, is_taken_by INT.

Then in your worker you do something like this:

select job_id from jobs where is_taken_by is null limit 1 for update;
update jobs set is_taken_by = worker_pid where id = job_id;

SELECT ... FOR UPDATE sets exclusive locks on rows it reads. This way you ensure that no other worker can take the same job.

Note: you have to run those two lines in an explicit transaction.

Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.

answered Mar 19, 2012 at 18:54

Sergio Tulentsev

231k43 gold badges381 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

applechief Over a year ago

the problem with adding a row is that if the worker fails midway, the row will be stuck in the table with a being processed status... I am going to read more about for update. depending on how it locks it row and for how long, it might solve the problem

Sergio Tulentsev Over a year ago

Add another column: locked_at DATETIME. And run periodic cronjob to unlock rows that were locked a long time ago.

Collectives™ on Stack Overflow

mysql row level read lock to replace messaging queue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related