3 ms·
He is incorrect about isolation levels, at least in PostgreSQL. Serializable with DELETE + INSERT will make a concurrent serialiable update fail with serializat
by jeltz 13y ago
He is incorrect about isolation levels, at least in PostgreSQL. Serializable with DELETE + INSERT will make a concurrent serialiable update fail with serialization error. I just tested this.
1=# CREATE TABLE my_table (key text primary key, value text not null);
1=# INSERT INTO my_table VALUES ('key', 'foo');
1=# BEGIN ISOLATION LEVEL SERIALIZABLE;
1=# DELETE FROM my_table WHERE key = 'key';
2=# BEGIN ISOLATION LEVEL SERIALIZABLE;
2=# UPDATE my_table SET value = 'bar';
1=# INSERT INTO my_table (key, value) VALUES ('key', 'value');
1=# COMMIT;
2 ERROR: could not serialize access due to concurrent update
- ash 13y agoHow is it different from other isolation levels? It looks like it still fails compared to what upsert would do.
- jeltz 13y agoOther isolation levels will make the concurrent update find zero rows. Serializable gives an error instead which you reliable can retry the transaction on. They fail in different ways (for serializable a in this case more useful way) which should have been pointed out by the author. It does not seem like he actually tested what isolation levels does or he tested it in a database which does not provide serializable isolation.
- ash 13y agoI think Armin didn't go into isolation levels details because he didn't see a meaningful distinction in the context of emulating upsert.