3 ms·
The DBA in me is having a difficult time understanding PostgreSQL's behavior with regard to the delete/insert within a transaction? In MySQL, I get the exact b
by AaronBBrown 13y ago
The DBA in me is having a difficult time understanding PostgreSQL's behavior with regard to the delete/insert within a transaction?
In MySQL, I get the exact behavior that I expect with anything above RC isolation level (which is the default for PostgreSQL):
session 1> create table foo ( k varchar(10) not null, v varchar(10) not null ) ;
session 1> set transaction isolation level read committed;
session 2> set transaction isolation level read committed;
session 1> insert into foo (k,v) values ('a','b');
session 1> begin;
session 2> begin;
session 1> delete from foo where k = 'a';
session 2> delete from foo where k = 'a';
-- transaction hangs here as expected since session 1 has a lock on this record
session 1> insert into foo (k,v) values ('a','c');
session 1> commit;
-- session 2 executes the delete
session 2> insert into foo (k,v) values ('a','e');
session 2> commit;
session 2> select * from foo;
+---+---+
| k | v |
+---+---+
| a | e |
+---+---+
When I do this in PostgreSQL, I end up with 2 rows in the table, which seems fundamentally incorrect and to me implies that PostgreSQL's implementation of read committed is flawed and should be fixed. The transaction in session 2, at RC isolation, should see the most recently committed result, which is the single record from session 1. I'm not CJ Date, though so am very likely missing some subtlety.
PostgreSQL after the sequence above:
test=# select * from foo;
k | v
---+---
a | c
a | e
(2 rows)
What am I missing? Why is this considered correct behavior?
- skybrian 13y agoI'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.