Implement SELECT ... FOR UPDATE NOWAIT (sort of) for mysql 5.7 in python?

Question

I need to implement a way to allow concurrent read to check whether a record has set or not. If set they just return. If not the first read process/thread will then set it based on the result it gets. The purpose of that is to run an init script only once.

If I use mysql 8.0 I think I can use SELECT ... FOR UPDATE NOWAIT so if a process/thread fails to get the lock, that means another process/thread has got it, so it can just return. The one got the lock will decide whether it needs to run the init script and update the record.

But I am using mysql 5.7, no NOWAIT and if I use SELECT ... FOR UPDATE, concurrent read become waiting for the lock one by one. I feel it is not good enough. So is it possbile to implement a home-made NOWAIT （in python） ?

My goal is to make sure the init script run just once, so if no NOWAIT, is there other way to do that ?

Qiulang · Accepted Answer · 2021-06-04 02:39:43Z

1

I got an answer from a similar question I asked at https://softwareengineering.stackexchange.com/questions/426902/how-do-i-make-sure-a-task-run-only-once-in-a-dockerized-environment

I summarized what I learned regarding how to implement nowait, all the credits go to the answers there, especially for Phil's answer.

UPDATE your_table
SET running_update = 1
WHERE running_update = 0

then check the count of rows updated - the first run of the UPDATE statement will actually update the row so get 1. That is the one continues to run the task.

While all subsequent runs will not make any change and get 0 so they can just return.

So basically this is the same as using setnx in redis for lock.

edited Jun 4, 2021 at 2:39

answered Jun 3, 2021 at 10:01

Qiulang

12.9k22 gold badges99 silver badges158 bronze badges

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

Collectives™ on Stack Overflow

Implement SELECT ... FOR UPDATE NOWAIT (sort of) for mysql 5.7 in python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related