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:
- PostgreSQL: Row has id=5 (Session B's update affected the deleted row)
- MySQL InnoDB: Row has id=6 (Session B's update affected the inserted row)
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: