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

Chris Angelico wrote:

> Correct me if I'm wrong, but wouldn't:
>
> update some_table set last_used=last_used+1 returning last_used
>
> simply block until the other transaction is committed/rolled back?

Only at the READ COMMITTED transaction isolation level.

> That avoids all the issues of serialization AND retrying
> transactions.

If you can tolerate the occasional oddities of READ COMMITTED
transactions, it makes this quite simple, yes.

> Or is the delay itself a problem?

The delay isn't a problem per se; it's hard to get around some form
of blocking if you want transactional integrity and gap-free
assignment of numbers.  If you can do this sort of assignment near
the front of the transaction in REPEATABLE READ or SERIALIZABLE
transactions, it doesn't cost that much more than in READ COMMITTED.

And the manifestations of weirdness in READ COMMITTED can be
daunting.  The simplest example looks pretty contrived, but simple
examples usually do.

Create and populate a simple table:

create table rc (id int primary key);
insert into rc select generate_series(1, 5);

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.

Rather than make sure that every programmer writing code to go
against your database knows exactly where all the problems are and
consistently codes around them, it can be a lot easier to use
serializable transactions.

-Kevin

On Fri, Oct 19, 2012 at 1:22 AM, Kevin Grittner <kgrittn@mail.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