PostgreSQL vs MySQL “update” difference

While one session is doing a "delete then insert", and another session doing an "update", the update waits until the "delete then insert" is finished. But does the "update" then affect the "inserted" row or the "deleted" row?

Create a table foo(id) and insert one row with id=5 into it. Then:

Session A Session B
START TRANSACTION START TRANSACTION
DELETE FROM foo WHERE id=5
UPDATE foo SET id=6 WHERE id=5 (blocks)
INSERT INTO foo VALUES (5)
COMMIT (unblocks)
SELECT id FROM foo

What does Session B see?

The result depends on your database vendor:

I don't think I'd say either of these is the "right" approach, I think they are both valid. If two things happen at the same time, who's to say which should win?

I would actually advise against deleting then inserting. I would insert then, if a unique constraint violation is triggered, do an update. But this doesn't change the fact that, if you're doing two things at the same time, you can't attach meaning to which one should win.

BTW if you're using an ORM like Hibernate, which claims to abstract away from the database, and allow you to use any vendor, do you think it takes differences like this into account?

Originally posted here:

https://news.ycombinator.com/item?id=5435786

P.S. I recently created a nerdy privacy-respecting tool called When Will I Run Out Of Money? It's available for free if you want to check it out.

This article is © Adrian Smith.
It was originally published on 26 Mar 2013
More on: Databases | PostgreSQL | MySQL