3 ms·

thanks for the explanation, I guess I need to do some extra reading on this...

• kozlovsky 13y ago
  The 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 ago
    Yes, 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.