Take the tour ×  This is pretty simple, but I'm baffled by what
PG does (v9.0). We start with a simple table:   and a few rows:
Using my favorite JDBC query tool (ExecuteQuery), I connect two
session windows to the db where this table lives. Both of them
are transactional (ie, auto-commit=false). Let's call them S1
and S2.  The same bit of code for each:  1:DELETE FROM test
WHERE id=1;  Now, run this in slow motion, executing one at a
time in the windows.  S1-1 runs (1 row deleted) S2-1 runs (but
is blocked since S1 has a write lock) S1-2 runs (1 row inserted)
S1-3 runs, releasing the write lock S2-1 runs, now that it can
get the lock. But reports 0 rows deleted. HUH??? S2-2 runs,
reports a unique key constraint violation  Now, this works fine
in SQLServer. When S2 does the delete, it reports 1 row deleted.
And then S2's insert works fine.  I suspect that PostgreSQL is
locking the index in the table where that row exists, whereas
SQLServer locks the actual key value.  Am I right? Can this be
made to work?