CODEWITHSUNDEEP
postgresqldeadlockpostgresql-9.3
kev
kev

Reputation: 9715

Postgres: Where does this deadlock come from?

pg_log:

2016-01-20 23:00:31 UTC ERROR:  deadlock detected
2016-01-20 23:00:31 UTC DETAIL:  
    Process 2696 waits for RowShareLock on relation 1849109 of database 1847373; blocked by process 2919.
    Process 2919 waits for AccessExclusiveLock on relation 1848698 of database 1847373; blocked by process 2696.
    Process 2696:
                INSERT INTO install_session (
                    [..]
                ) VALUES (
                    [..]
                )

    Process 2919: DROP TABLE IF EXISTS install_view_2015_11_20
2016-01-20 23:00:31 UTC HINT:  See server log for query details.

Querying the mentioned relations gives me:

select * from pg_class where oid = 1849109;
> install
select * from pg_class where oid = 1848698; 
> app

Questions:

  1. Why does the deadlock happen at all? The INSERT and the DROP are aiming at two different tables.
  2. What do the other two relations/tables (install, app) have to do with it?

Postgres 9.3

UPDATE

I indeed do have triggers:

CREATE OR REPLACE FUNCTION insert_install_session()
        RETURNS TRIGGER AS $$
        BEGIN
            EXECUTE 'INSERT INTO install_session_'
                || to_char(NEW.created, 'YYYY_MM_DD') || 
                ' SELECT ($1).*'
            USING NEW;
            RETURN NULL;
        END;
        $$
        LANGUAGE PLPGSQL

  CREATE TRIGGER trigger_insert_install_session
        BEFORE INSERT ON install_session
        FOR EACH ROW EXECUTE PROCEDURE insert_install_session()

and foreign keys from all partitions (install_session_XXXX_XX_XX and install_view_XXXX_XX_XX in this case) referencing the tables app and install.

That makes things a bit more clear, but I still don't understand why the DROP command needs to lock any tables other than the table that should be dropped.

Upvotes: 0

Views: 4928

Answers (1)

krokodilko
krokodilko

Reputation: 36087

I've done the following experiment:

test=> CREATE TABLE aa( x int primary key );
CREATE TABLE
test=> CREATE TABLE bb( y int primary key, x int references aa(x) );
CREATE TABLE
test=> BEGIN;
BEGIN
test=> DROP TABLE bb;
DROP TABLE
test=>

and in another session:

test=> select locktype, relation, relname,
test->        transactionid, mode, granted
test-> from pg_locks p
test-> left join pg_class c on p.relation = c.oid
test-> where locktype = 'relation'
test-> ;
 locktype | relation |              relname              | transactionid |        mode         | granted
----------+----------+-----------------------------------+---------------+---------------------+---------
  .........
  .........
  .........
  .........
 relation |    24739 | aa                                |               | AccessExclusiveLock | t
 relation |    24744 | bb                                |               | AccessExclusiveLock | t
(13 wierszy)

It appears that dropping table B requires an exclusive lock on both tables 'B' and 'A'.

Why PostgeSQL needs to lock 'A' apart from 'B' ? I really don't know.
I think the best we can do is to reconcile this fact and life with it.


If we know that PostgreSQL needs an exclusive lock of both tables, the reason of the deadlock is now clear:

  • the first insert in the first transaction places a shared lock on table A
  • the second transaction in order to run DROP TABLE B must acquire exclusive lock on table A and must wait for transaction #1
  • the first transaction runs the second insert which also requires a shared lock on table A, and must wait for transaction #2 --> bang: deadlock



To avoid the deadlock place AccessExclusiveLock on table A in the first transaction before executing the first insert.

Upvotes: 1

Related Questions