MySQL - SELECT FOR UPDATE

Question

MySQL

Share your experience of successful use and problems that arise in connection with the use.

Personally, today I came across the fact that almost the entire table blocked me to hell, despite the fact that I do SELECT id FROM ... FOR UPDATE and the next UPDATE WHERE id = $id operation.

MySQL version is 5.1.31 on ubuntu.

And my workflow is as follows:
I have several console applications that simultaneously take tasks from the queue table. Those. SELECT id FROM queue LIMIT 1 FOR UPDATE and if the record is selected, then UPDATE queue SET locked = 1 WHERE id = $id
The code is written in such a way that it would not hold the lock for more than a few milliseconds, and this particular part does not fall off. Sometimes other parts fell off and for some reason I didn’t understand, it turned out that almost all the records in the table were blocked, although this should not happen.

I haven’t been able to really dig up information on the Internet yet about bugs and features, while I continue to dig.

Thanks in advance for the information.

Answer 1

Those. Is there only this type of requests?
"begin;
select ad from a where ab=c for update;
update a set ad=a.d+1 where ab=c;
commit;
The main thing is that the table supports transactions. InnoDb keeps. „

Answer 2

>UPDATE WHERE id = $id
Why is where here? If 2 separate where then is there any chance that it just updated options not related to SELECT FOR UPDATE?

Answer 3

I also recently encountered the problem of concurrent write access, in my case I will implement it in the following way - each process that will work with the table will have its own ID, I will add the “busy” field to the table by default, it will be 0. Further, each process will do the following
UPDATE tbl SET busy=$ID WHERE id=(SELECT max(id) FROM tbl WHERE busy=0)
and then it will do whatever it wants with this entry.

Answer 4

Same situation! FOR UPDATE really saves. Records are selected as needed and no race-condition occurs. But it’s worth running another long query that accesses this table (I have a JOIN), as all queries start returning the same records competitively. Still trying to find a solution...

Answer 5

SELECT * FROM table t WHERE t.processed = false AND GET_LOCK(CONCAT('process_', t.id), 0) LIMIT 1;
UPDATE table t SET t.processed = true, operator_id = :current WHERE id = :id;
DO RELEASE_LOCK(CONCAT('process_', :id));