Postgresql deadlock lock with lock table in exclusive mode

I'm getting this deadlock error in my database:

```
2017-02-22 20:57:48 GMT ERROR: deadlock detected
2017-02-22 20:57:48 GMT DETAIL: Process 7372 waits for ExclusiveLock on relation 18856 of database 18347; blocked by process 5724.
Process 5724 waits for ExclusiveLock on relation 18856 of database 18347; blocked by process 7372.
Process 7372: insert into Transaction (attribute1, attribute2, attribute3) values ($1, $2, $3) RETURNING *
Process 5724: insert into Transaction (attribute1, attribute2, attribute3) values ($1, $2, $3) RETURNING *
2017-02-22 20:57:48 GMT HINT: See server log for query details.
2017-02-22 20:57:48 GMT CONTEXT: SQL statement "LOCK TABLE niv IN EXCLUSIVE MODE"
PL/pgSQL function updateniv() line 8 at SQL statement
2017-02-22 20:57:48 GMT STATEMENT: insert into Transaction (attribute1, attribute2, attribute3) values ($1, $2, $3) RETURNING *
2017-02-22 20:57:48 GMT ERROR: current transaction is aborted, commands ignored until end of transaction block
2017-02-22 20:57:48 GMT STATEMENT: SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' ELSE NULL END ELSE NULL END AS TABLE_TYPE, d.description AS REMARKS FROM pg_catalog.pg_namesp

It looks to me like both transactions acquired a weaker lock (probably ROW EXCLUSIVE (but now clarified in a comment to be the more rare ROW SHARE)) on NIV earlier in their transaction. Now both want to upgrade that lock to EXCLUSIVE, but neither can do so until the other gives up their weaker lock. But neither is willing to give up weaker lock until they obtain the stronger one.

The error messages only tell you the current query of each process (because that info is what is available to the logging system) but you really need to know all queries that were previously run in the currently-open transaction to get a full picture.

In your comment, you say the weaker lock is actually a ROW SHARE. This is automatically acquired by the commands SELECT FOR UPDATE and SELECT FOR SHARE see documentation. You would have to identify where in your code these types of queries are issued (in the same transaction as the other queries which later trigger the deadlock are issued) and pre-emptively take an EXCLUSIVE lock before issuing those queries.

StackExchange Database Administrators Q#165231, answer score: 4