Do UPDATEs block SELECTs?

Question

I need to query a frequently updated record, does it wait until all updates finish?

/updateUser/1

Many requests are sent

UPDATE users SET count = count + 1 WHERE id = 1 LIMIT 1

Does this answer your question? MySQL get a lock to update certain rows and prevent other sessions to read that row — mustaccio, Mar 5, 2022 at 23:35
Of course. The locks that these two users want to acquire are not compatible. — Meyssam Toluie, Mar 6, 2022 at 16:25

Bill Karwin · Accepted Answer · 2022-03-06 19:07:43Z

0

By default, writers do not block readers. So no, the SELECT would not have to wait for the UPDATE to finish.

Exception cases:

If you use explicit syntax to make the SELECT a locking read query.
If you set the transaction isolation level to SERIALIZABLE, which has the effect that all SELECTs are implicitly locking reads.

answered Mar 6, 2022 at 19:07

Bill Karwin

13.9k1 gold badge30 silver badges42 bronze badges

Interesting. I suggest you explain that, because "writers do not block readers" seems to imply READ UNCOMMITTED
– Charlieface
Mar 9, 2022 at 20:45
@Charlieface, Notwithstanding our deleted comment exchange, I think it's now fair to ask you to test what you assert.
– Bill Karwin
Mar 10, 2022 at 3:36
No I agreed with you, which is why I deleted it, I don't know MySQL well enough. I was just saying that a cursory reading of your answer, at least coming from a SQL Server perspective, sounds more like READ UNCOMMITTED than it does SNAPSHOT (and you've said it is effectively snapshot isolation), so I just thought you could write that a bit clearer.
– Charlieface
Mar 10, 2022 at 10:36
Well in fact, writers do not block readers in READ-COMMITTED isolation level either. In both RR and RC levels, readers use a snapshot. But in RC, the snapshot is updated to include most recently committed data. Whereas in RR, the snapshot is based on the first read done during the user's transaction and it does not change for the duration of the transaction.
– Bill Karwin
Mar 10, 2022 at 15:22

Add a comment |

1 Answer 1