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".
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.
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".