Re: 9.1/9.2 SERIALIZABLE: expected serialization failure between INSERT and SELECT not encountered

On Fri, Oct 19, 2012 at 1:22 AM, Kevin Grittner <kgrittn(at)mail(dot)com> wrote:
> Now, if no records are inserted or deleted by another connection, how
> many rows will be deleted by this statement?:
>
> delete from rc where id = (select min(id) from rc);
>
> It's a trick question; the answer depends on a race condition.
>
> Before you run that delete, on a separate connection run this:
>
> begin;
> update rc set id = id - 1;
>
> Run your delete on the first connection and it will block. Commit
> the transaction on the second connection, and go look at the results
> on the first, and select from the table.

Ah yes, I believe that one can be best explained with the Magic: The
Gathering rules about triggers and the "intervening if" clause. In
M:TG, a trigger might say "At the beginning of your upkeep, if you
have 40 or more life, you win the game". It checks the condition
twice, once when it triggers, and again when it resolves. (Yeah, I'm a
nerd. I know detaily rules to a nerd's game. So sue me.)

In the first pass, Postgres decides which row(s) should be deleted.
Okay, let's find the one that's the lowest ID. Lowest ID is 1, that
means this record. Hey, mind if I delete this? Oh, it's locked. Hold
on.

Commit the other one.

Okay, let's go delete this one. Check to see that it still matches the
WHERE clause. Does it have ID=1? Nope, ID=0. Don't delete anything!

Yes, it's slightly odd. But really, if you're doing sweeping changes
like that, a table-level massively exclusive lock is probably the best
way to do it. I haven't seen any issues with READ COMMITTED that have
caused problems; although that's partly because I usually have an
immutable ID 'serial primary key' on every row. Helps a lot.

ChrisA