Hi, I hit a UPDATE/LOCK issue in my application and the result has surprised me somewhat… And for the repro, it boils down into this: --- CREATE TABLE x (a int, b bool); INSERT INTO x VALUES (1, TRUE); COMMIT; _THREAD 1_: BEGIN; UPDATE x SET b=FALSE; INSERT INTO x VALUES (2, TRUE); _THREAD 2_: BEGIN; SELECT * FROM x WHERE b=TRUE FOR UPDATE; -- It will be blocked, as expected _THREAD 1_: COMMIT; _THREAD 2_ will be unblocked. It will return no rows. I expect it to return (2, TRUE) instead, when I design the program. If I issue the same SELECT query in THREAD 2 right now, it does indeed return (2, TRUE). For the same SQL statement, in MySQL-InnoDB, it does return (2, TRUE) in the first SELECT. I understand why this happens in PgSQL, (because it first limited the selection and locked the row, upon unlock it recheck the condition)… I don’t like THERAD 2 only see half of the fact of the committed transaction (it see the effect of the update but not the insert), is there anything I could do? I considered: * ISOLATION serialization – but the thread 2 would abort as deadlock… * Both thread do: LOCK TABLE x AS SHARE ROW EXCLUSIVE MODE – does resolve my issue but it creates a big lock contention problem, and relies on app to do the right thing. * Advisory lock – pretty much the same, except that I could unlock earlier to make the locking period shorter, but nevertheless it’s the whole table lock… Thoughts? Thanks, Sam |
