15

My function new_customer is called several times per second (but only once per session) by a web application. The very first thing it does is lock the customer table (to do an 'insert if not exists'—a simple variant of an upsert).

My understanding of the docs is that other calls to new_customer should simply queue until all previous calls have finished:

LOCK TABLE obtains a table-level lock, waiting if necessary for any conflicting locks to be released.

Why is it sometimes deadlocking instead?

definition:

create function new_customer(secret bytea) returns integer language sql 
                security definer set search_path = postgres,pg_temp as $$
  lock customer in exclusive mode;
  --
  with w as ( insert into customer(customer_secret,customer_read_secret)
              select secret,decode(md5(encode(secret, 'hex')),'hex') 
              where not exists(select * from customer where customer_secret=secret)
              returning customer_id )
  insert into collection(customer_id) select customer_id from w;
  --
  select customer_id from customer where customer_secret=secret;
$$;

error from log:

2015-07-28 08:02:58 BST DETAIL:  Process 12380 waits for ExclusiveLock on relation 16438 of database 12141; blocked by process 12379.
        Process 12379 waits for ExclusiveLock on relation 16438 of database 12141; blocked by process 12380.
        Process 12380: select new_customer(decode($1::text, 'hex'))
        Process 12379: select new_customer(decode($1::text, 'hex'))
2015-07-28 08:02:58 BST HINT:  See server log for query details.
2015-07-28 08:02:58 BST CONTEXT:  SQL function "new_customer" statement 1
2015-07-28 08:02:58 BST STATEMENT:  select new_customer(decode($1::text, 'hex'))

relation:

postgres=# select relname from pg_class where oid=16438;
┌──────────┐
│ relname  │
├──────────┤
│ customer │
└──────────┘

edit:

I've managed to get a simple-ish reproducible test case. To me this looks like a bug due to some sort of race condition.

schema:

create table test( id serial primary key, val text );

create function f_test(v text) returns integer language sql security definer set search_path = postgres,pg_temp as $$
  lock test in exclusive mode;
  insert into test(val) select v where not exists(select * from test where val=v);
  select id from test where val=v;
$$;

bash script run simultaneously in two bash sessions:

for i in {1..1000}; do psql postgres postgres -c "select f_test('blah')"; done

error log (usually a handful of deadlocks over the 1000 calls):

2015-07-28 16:46:19 BST ERROR:  deadlock detected
2015-07-28 16:46:19 BST DETAIL:  Process 9394 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9393.
        Process 9393 waits for ExclusiveLock on relation 65605 of database 12141; blocked by process 9394.
        Process 9394: select f_test('blah')
        Process 9393: select f_test('blah')
2015-07-28 16:46:19 BST HINT:  See server log for query details.
2015-07-28 16:46:19 BST CONTEXT:  SQL function "f_test" statement 1
2015-07-28 16:46:19 BST STATEMENT:  select f_test('blah')

edit 2:

@ypercube suggested a variant with the lock table outside the function:

for i in {1..1000}; do psql postgres postgres -c "begin; lock test in exclusive mode; select f_test('blah'); end"; done

interestingly this eliminates the deadlocks.

  • 2
    In the same transaction, before entering that function, is customer used in a way that would grab a weaker lock? Then it could be a lock upgrade problem. – Daniel Vérité Jul 28 '15 at 13:20
  • 2
    I can't explain this. Daniel may have a point. Might be worth raising this on pgsql-general. Either way, are you aware of the UPSERT implementation in the upcoming Postgres 9.5? Depesz taking a look at it. – Erwin Brandstetter Jul 28 '15 at 13:33
  • 2
    I mean within the same transaction, not just the same session (as locks are released at tx end). The answer by @alexk is what I was thinking about, but if the tx starts and ends with the function, that can't explain the deadlock. – Daniel Vérité Jul 28 '15 at 15:01
  • 1
    @Erwin you'll doubtless be interested in the answer I got from posting at pgsql-bugs :) – Jack Douglas Jul 28 '15 at 17:57
  • 2
    Very interesting indeed. Makes sense that this works in plpgsql, too, as I remember similar plpgsql cases working as expected. – Erwin Brandstetter Jul 28 '15 at 18:28
4

Assuming you run another statements before calling new_customer, and those acquire a lock that conflicts with EXCLUSIVE (basically, any data modification in the customer table), the explanation is very simple.

One can reproduce the problem with a simple example (not even including a function):

CREATE TABLE test(id INTEGER);

1st session:

BEGIN;

INSERT INTO test VALUES(1);

2nd session

BEGIN;
INSERT INTO test VALUES(1);
LOCK TABLE test IN EXCLUSIVE MODE;

1st session

LOCK TABLE test IN EXCLUSIVE MODE;

When the first session does the insert, it acquires the ROW EXCLUSIVE lock on a table. Meanwhile, session 2 tries also gets the ROW EXCLUSIVE lock, and tries to acquire an EXCLUSIVE lock. At which point it has to wait for the 1st session, since EXCLUSIVE lock conflicts with ROW EXCLUSIVE. At last, 1st session jumps the sharks and tries to get an EXCLUSIVE lock, but since the locks are acquired in order, it queues after the 2nd session. This, in turn, waits for the 1st one, producing a deadlock:

DETAIL:  Process 28514 waits for ExclusiveLock on relation 58331454 of database 44697822; blocked by process 28084.
Process 28084 waits for ExclusiveLock on relation 58331454 of database 44697822; blocked by process 28514

The solution to this problem is to acquire locks as early as possible, usually as a first thing in a transaction. On the other hand, PostgreSQL workload only needs locks in some very rare cases, so I'd suggest rethinking the way you do the upsert (take a look at this article http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/).

  • 2
    This is all interesting, but the message in the db logs would read something like: Process 28514 : select new_customer(decode($1::text, 'hex')); Process 28084 : BEGIN; INSERT INTO test VALUES(1); select new_customer(decode($1::text, 'hex')) While Jack just got: Process 12380: select new_customer(decode($1::text, 'hex')) Process 12379: select new_customer(decode($1::text, 'hex')) - indicating that the function call is the first command in both transactions (unless I am missing something). – Erwin Brandstetter Jul 28 '15 at 15:02
  • Thanks, and I agree with what you say, but this doesn't appear to be the cause in this case. That's clearer in the more minimal test case I've added to the question (which you could try yourself). – Jack Douglas Jul 28 '15 at 16:01
  • 2
    Actually it turns out you were right about lock escalation - though the mechanism is subtle. – Jack Douglas Jul 28 '15 at 18:11
10

I posted this to pgsql-bugs and the reply there from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, the lock generated by the insert is obtained before the exclusive lock on the table:

I believe the issue with this is that a SQL function will do parsing (and maybe planning too; don't feel like checking the code right now) for the entire function body at once. This means that due to the INSERT command you acquire RowExclusiveLock on the "test" table during function body parsing, before the LOCK command actually executes. So the LOCK represents a lock escalation attempt, and deadlocks are to be expected.

This coding technique would be safe in plpgsql, but not in a SQL-language function.

There have been discussions of reimplementing SQL-language functions so that parsing occurs one statement at a time, but don't hold your breath about something happening in that direction; it doesn't seem to be a high priority concern for anybody.

regards, tom lane

This also explains why locking the table outside the function in a wrapping plpgsql block (as suggested by @ypercube) prevents the deadlocks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.