3 ms·
I'm not an expert, but as I understand it, both deletes see row ('a','b'). Session 2 sees that that row is locked so it waits, and when the lock is released it
by skybrian 13y ago
I'm not an expert, but as I understand it, both deletes see row ('a','b'). Session 2 sees that that row is locked so it waits, and when the lock is released it sees that it's already deleted. However, it doesn't redo the query so it never sees ('a','c'). As far as PostgresSQL is concerned, ('a','c') is a completely different row from ('a','b') and it was created too late to have any effect on session 2.
These transactions aren't serializable but you didn't ask for that.
To get intuitive behavior, a delete followed by an insert with the same primary key in the same transaction would have to be treated as a row update, holding onto a single row lock, rather than as two independent rows with different row locks.
- AaronBBrown 13y agoThis violates the promise of read committed. In RC, all transactions are supposed to see the most recently committed version of a record. The fact that it was a delete, then an insert is an implementation detail and "not my problem" as a DBA. At the time the transaction in session 1 commits, the most recently committed version of k='a' is v='c'. PostgreSQL, in the situation, seems to be operating in read uncommitted isolation level...session 2 is treating the uncommitted delete in session 1 as the most recent version. I'd like to give PostgreSQL the benefit of the doubt here, but this behavior seems fundamentally incorrect to me.
- skybrian 13y agoThat's the subtle part: they aren't two versions of the same record. They're two entirely different records, so all bets are off. (In your example, they don't even have the same primary key because no primary key is declared. PostgresSQL doesn't know that k is supposed to be a key; it's just another column.)
- AaronBBrown 13y agoThat is an implementation problem. The behavior is still incorrect. MySQL manages to do this correctly, so it's clearly not impossible. I can't come up with any other conclusion except that transaction isolation in pg is fundamentally broken when I see this type of case show up.