113 reputation
2
bio website
location
age
visits member for 1 year, 9 months
seen Oct 15 at 21:46

Mar
18
awarded  Scholar
Mar
18
awarded  Student
Mar
18
comment Minimize locking on UPDATE with multiple WHERE columns
It would be really helpful though if you had an idea about those specific questions: 1. Is there a syntax to check if a specific row is currently locked something like Update x set y=z where is_locked=false? (I've been researching for while and there doesn't seem to be such a thing but just checking. AND 2. How does Innodb lock when there are multiple columns in the where clause, does it lock all rows that satisfy ANY condition or only the ones that satisfy ALL conditions
Mar
18
accepted Minimize locking on UPDATE with multiple WHERE columns
Mar
18
comment Minimize locking on UPDATE with multiple WHERE columns
Nice idea thank you! I was thinking of something similar but I wasn't aware that the LIMIT syntax can allow the "n'th" 100 rows. Pretty close to what I need, thank you!
Mar
17
comment Minimize locking on UPDATE with multiple WHERE columns
9 servers are accessing the db
Mar
17
comment Minimize locking on UPDATE with multiple WHERE columns
I posted a similar question here
Mar
17
comment Minimize locking on UPDATE with multiple WHERE columns
I'll take a step back because probably my question wasn't clear enough. Optimally no locking should occur at all since the idea behind this query is each server is trying to assign one record to itself. row1 being null means it hasn't been assigned to any server yet so pick it up and assign it to yourself. If all servers look at the same 100 rows at the same time this would be very innefficient that's why my question was how to avoid that locking (maybe some condition in the where clause) or keep it to the minimum (probably by picking random rows which satisfy the condition row1=null)
Mar
17
comment Minimize locking on UPDATE with multiple WHERE columns
Thank you for your reply. However I don't see how that would solve my problem since all servers executing the query you provided will put a lock on the same rows, ending up with a timeout again.
Mar
17
asked Minimize locking on UPDATE with multiple WHERE columns