Deadlock on same table when creating partition in PostgreSQL


  • QA Engineer

    PostgreSQL 14.1. I have a partitioned table liked this:

    create table dataset_session
    (
        dataset_id integer not null references dataset (id)
        session_id integer not null,
        external_id uuid not null references session (external_id)
    ) partition by list (dataset_id);
    

    When multiple clients simultaneously try to create a partition of it this often fails with a deadlock, like this:

        Detail: Process 21929 waits for AccessExclusiveLock on relation 173796 (dataset_session) of database 173691; blocked by process 26983.
    Process 26983 waits for AccessExclusiveLock on relation 173796 (dataset_session) of database 173691; blocked by process 21929.
    
    Where: SQL statement "create table dataset_session_50 partition of dataset_session (
            constraint dataset_session_50_pkey primary key (session_id),
            constraint dataset_session_50_external_id_uq unique (external_id)
        ) for values in (50);
    

    Note that both processes apparently have an exclusive lock on the same table (dataset_session, the parent table) - how is this possible? And how do I fix this?

    My understanding was always that you need multiple statements in a transaction to cause a deadlock, but I'm only running that one statement in the transaction! (It's done by a procedure which calls "commit;" before and after.)

    Edit: Interestingly, the deadlock does not happen with 2 other partitioned tables that seem very similar to this one.



  • While I never figured out the cause of this, I figured out the workaround: create the two constraints on the partition separately from the partition itself:

        execute format('create table dataset_session_%1$s partition of dataset_session
            /*(
                constraint dataset_session_%1$s_pkey primary key (session_id), -- deadlocks!
                constraint dataset_session_%1$s_external_id_uq unique (external_id) -- deadlocks!
            )*/
            for values in (%1$s);
    
        alter table dataset_session_%1$s add constraint dataset_session_%1$s_pkey primary key (session_id); -- does not deadlock!
        alter table dataset_session_%1$s add constraint dataset_session_%1$s_external_id_uq unique (external_id);  -- does not deadlock!',
            dataset_internal_id);
    

    Note that these still all run in the same execute statement and therefore in the same transaction, so I wouldn't have thought it would make a difference, but it does.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2