Thread: INSERT locking order

INSERT locking order

From
Matthew Woodcraft
Date:
In an INSERT, are the rows guaranteed to be inserted in the order that
the source query returns its rows, for locking purposes?

For example, if have

  CREATE TABLE foo (
    foo_id INTEGER,
    PRIMARY KEY (foo_id)
  );

and I run two concurrent copies of

  INSERT INTO foo (SELECT n FROM ... ORDER BY n);

is there any guarantee that I'll get a unique constraint violation
rather than a deadlock?

-M-



Re: INSERT locking order

From
Tom Lane
Date:
Matthew Woodcraft <matthew@woodcraft.me.uk> writes:
> In an INSERT, are the rows guaranteed to be inserted in the order that
> the source query returns its rows, for locking purposes?

I dunno that we "guarantee" that, but it's hard to see why the
code would behave any differently, at present.

> and I run two concurrent copies of
>   INSERT INTO foo (SELECT n FROM ... ORDER BY n);
> is there any guarantee that I'll get a unique constraint violation
> rather than a deadlock?

Well, the issue there is not about the physical insertion order
but the order in which the uniqueness checks happen.  I think
you'd be all right with a traditional-style PG index, but maybe
not with a deferrable unique constraint.

            regards, tom lane