MySQL: Prevent race condition - FOR UPDATE or LOCK IN SHARE MODE?

Question

Here is my desired transaction order:

1. User1 select field, perform operation, update with new value.
2. User2 select field, perform operation, update with new value.
3. User3 select field, perform operation, update with new value.

From what I understand the first select only perform a write-lock while the second one perform read and write lock.

Both seems usable but in the first case, what value will User2 read? The initial value before User1 updates, or the value AFTER User1 updates (which is what I want)?

So I am confused, should I use SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE?

Answer 1

You probably want to use FOR UPDATE.

With "LOCK IN SHARE MODE" the second user will still be able to read the value before it has been updated.

From the MySQL docs:

If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. Using LOCK IN SHARE MODE sets a shared lock that allows other transactions to read the examined rows but not to update or delete them.

So even though LOCK IN SHARE MODE prevents an update, if you operation depends on the read value, you may end up in an inconsistent state.