PostgreSQL vs MySQL “update” difference

By Adrian Smith26 Mar 2013300 words2 mins to read

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

This article was written by Adrian Smith on 26 Mar 2013

Follow me: Facebook | Twitter | Email

More on: Databases | PostgreSQL | MySQL