5 ms·

"SELECT ... FOR UPDATE" seems to be the answer to all these issues right? Lock the rows you're going to be updating and suddenly everything works as advertised.

xxpor 3y ago
If you want performance to completely tank, sure.
- hipadev23 3y ago
  Afaik, it only locks the row(s) in question. Do you have a setup where the same row is being updated by multiple clients all the time?
  - jtc331 3y ago
    SELECT FOR UPDATE means two queries can’t _read_ the same row at the same time, right?
    - hipadev23 3y ago
      To my understanding, yes. If you have broad select queries across all the rows, that would slow things down quite a bit.
      - dilyevsky 3y ago
        You can use SKIP LOCKED normally. That’s how popular queue implementations usually work. I believe pg even calls this out in their docs
  - quickthrower2 3y ago
    SQL Server will escalate locks. Because keeping a million rows locked is expensive.
  - dig1 3y ago
    This can easily happen if you have multiple services that can write/update in the same table/rows and rely on database coordination instead of using an external queue like Kafka. Postgres (presumably other databases) can propagate these locks to table locks [1] and cause contention for the whole infra. [1] https://blog.heroku.com/curious-case-table-locking-update-query https://blog.heroku.com/curious-case-table-locking-update-qu...
bcrosby95 3y ago
In general, stuff that locks rows tends to "snap" values into existence regardless of repeatable read. If you want to update a record based upon data in another record, you should do a locking read on that something else and maybe the record you're updating. If you run an sql query to update a record based upon some other record using a single query, MySQL will lock both for you anyways. If you need to update something based upon multiple something elses, in my experience that's very deadlock prone. Instead you should lock some kinda locking record, then do a repeatable read on the data you want, then do an update. The point in time of the repeatable read isn't established until you perform a consistent read. Select... for update isn't a consistent read. So it works perfectly fine in the face of concurrency while not locking dozens or hundreds of rows using a normal SQL update.
- sebmulti21 3y ago
  > you should do a locking read on that something else How to do this tho? Do you mean like this? BEGIN TRANSACTION // all concerned rows in the select statement SELECT * FROM A, B... FOR SHARE; // update relevant rows UPDATE A SET x = true ... FOR UPDATE; COMMIT END
  - bcrosby95 3y ago
    It kinda depends. I might do it like this if its super simple: UPDATE A, B SET A.x = B.x where A.b_id = B.id and A.id = 1; If it's more complex, it might have to be more like this: SELECT * FROM A ... FOR UPDATE; SELECT * FROM B... FOR SHARE; UPDATE A... If B is only ever updated after locking A, you can safely do this: SELECT * FROM A ... FOR UPDATE; SELECT * FROM B...; UPDATE A... Generally speaking, I wouldn't recommend locking "for share" then updating it later. This can result in deadlocks because you're upgrading it to an exclusive lock.

xxpor 3y ago

If you want performance to completely tank, sure.

hipadev23 3y ago
Afaik, it only locks the row(s) in question. Do you have a setup where the same row is being updated by multiple clients all the time?
- jtc331 3y ago
  SELECT FOR UPDATE means two queries can’t _read_ the same row at the same time, right?
  - hipadev23 3y ago
    To my understanding, yes. If you have broad select queries across all the rows, that would slow things down quite a bit.
    - dilyevsky 3y ago
      You can use SKIP LOCKED normally. That’s how popular queue implementations usually work. I believe pg even calls this out in their docs
- quickthrower2 3y ago
  SQL Server will escalate locks. Because keeping a million rows locked is expensive.
- dig1 3y ago
  This can easily happen if you have multiple services that can write/update in the same table/rows and rely on database coordination instead of using an external queue like Kafka. Postgres (presumably other databases) can propagate these locks to table locks [1] and cause contention for the whole infra. [1] https://blog.heroku.com/curious-case-table-locking-update-query https://blog.heroku.com/curious-case-table-locking-update-qu...

bcrosby95 3y ago

In general, stuff that locks rows tends to "snap" values into existence regardless of repeatable read. If you want to update a record based upon data in another record, you should do a locking read on that something else and maybe the record you're updating. If you run an sql query to update a record based upon some other record using a single query, MySQL will lock both for you anyways. If you need to update something based upon multiple something elses, in my experience that's very deadlock prone. Instead you should lock some kinda locking record, then do a repeatable read on the data you want, then do an update. The point in time of the repeatable read isn't established until you perform a consistent read. Select... for update isn't a consistent read. So it works perfectly fine in the face of concurrency while not locking dozens or hundreds of rows using a normal SQL update.

sebmulti21 3y ago
> you should do a locking read on that something else How to do this tho? Do you mean like this? BEGIN TRANSACTION // all concerned rows in the select statement SELECT * FROM A, B... FOR SHARE; // update relevant rows UPDATE A SET x = true ... FOR UPDATE; COMMIT END
- bcrosby95 3y ago
  It kinda depends. I might do it like this if its super simple: UPDATE A, B SET A.x = B.x where A.b_id = B.id and A.id = 1; If it's more complex, it might have to be more like this: SELECT * FROM A ... FOR UPDATE; SELECT * FROM B... FOR SHARE; UPDATE A... If B is only ever updated after locking A, you can safely do this: SELECT * FROM A ... FOR UPDATE; SELECT * FROM B...; UPDATE A... Generally speaking, I wouldn't recommend locking "for share" then updating it later. This can result in deadlocks because you're upgrading it to an exclusive lock.