4 ms·
It depends on isolation level. Default isolation level in MySQL is REPEATABLE READ and not SERIALIZABLE. Without locks, lost updates are sometimes possible.
by kozlovsky 13y ago
It depends on isolation level. Default isolation level in MySQL is REPEATABLE READ and not SERIALIZABLE. Without locks, lost updates are sometimes possible.
- ivanhoe 13y agothanks for the explanation, I guess I need to do some extra reading on this...
- kozlovsky 13y agoThe basic scenario where lost update is possible looks this way: session_1: BEGIN session_1: SELECT data from T1 where pk = 123 session_2: BEGIN session_2: SELECT data from T1 where pk = 123 session_2: UPDATE T1 set data = "value_2" where pk = 123 session_2: COMMIT session_1: UPDATE T1 set data = "updated_value" where pk = 123 session_1: COMMIT In this scenario, session_1 does not see "value_2", so "updated_value" does not take "value2" into account, and update of session_2 becomes lost. There are two ways to prevent this problem: 1) Using SELECT FOR UPDATE instead of SELECT. This way session_1 take exclusive lock on the corresponding row, and session_2 execution is suspended until session_1 will commit. 2) Using more strict SERIALIZABLE isolation level. In this mode, session_1 in MySQL will take shared lock on each selected row, and each SELECT effectively becomes SELECT ... LOCK IN SHARE MODE. This way session_2 also blocks until session_1 will commit. In PostgreSQL, SERIALIZED mode implemented the other way - SELECT works as usual (without locks), but during UPDATE session_1 will detect that the row was changed by concurrent transaction, and transaction of session_1 will be terminated with the exception "could not serialize access due to concurrent update".
- Roboprog 13y agoYes, yes, yes! Having the "late" commit (session 1) fail in this case is exactly what I would expect as "correct" behavior. However, I (now) see what MJB meant about needing an explicit (table/row) lock so that if you can start such a sequence, you should be able to complete it.