Yes, that's what happens in serializable. I guess my understanding was that
by nature, transactions are atomic, so that the first transaction's DELETE
and INSERT happened both before the second transaction gets back to
running. And according to documentation, if there's a conflict in READ
COMMITTED, the waiting transaction will rescan for changes made by the
transaction it was waiting for. So my assumption was that re-scan occurred
after the entire first transaction was complete, not immediately after the
particular operation that locked the row
Here's the relevant documentation:
However, such a target row might have already been updated (or deleted or
locked) by another concurrent transaction by the time it is found. In this
case, the would-be updater will wait for the first updating transaction to
commit or roll back (if it is still in progress)... If the first updater
commits, the second updater will ignore the row if the first updater
deleted it, otherwise it will attempt to apply its operation to the updated
version of the row. The search condition of the command (the WHERE clause)
is re-evaluated to see if the updated version of the row still matches the
search condition. If so, the second updater proceeds with its operation
using the updated version of the row
On Wed, May 14, 2014 at 8:06 AM, Michael Paquier
<michael.paquier@gmail.com>wrote:
>
> Yep because session 1 has done a concurrent update on the row session
> 2 is trying to delete. You would actually get something like that:
> =# DELETE FROM test WHERE id = 2;
> ERROR: 40001: could not serialize access due to concurrent update
> LOCATION: ExecDelete, nodeModifyTable.c:447
> --
> Michael
>
