CODEWITHSUNDEEP
postgresqlconcurrencylockingdeadlock
Ed Rahn
Ed Rahn

Reputation: 51

PostgreSQL deadlocks on two table locks

I am locking two different tables that have no interconnected columns, but I still get a deadlock.

Here is the server logs:

2013-10-22 15:16:19 EDT ERROR:  deadlock detected
2013-10-22 15:16:19 EDT DETAIL:  Process 26762 waits for AccessExclusiveLock on relation 39913 of database 39693; blocked by process 26761.
    Process 26761 waits for RowExclusiveLock on relation 40113 of database 39693; blocked by process 26762. 
    Process 26762: lock table par_times in access exclusive mode
    Process 26761: INSERT INTO cached_float (entry_id, figure_type, value) VALUES (33225, 1, 54.759402056277075) RETURNING cached_float.id

Any ideas why?

Upvotes: 1

Views: 3279

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656706

You can debug this by checking the numbers displayed here:

Process 26762 waits for AccessExclusiveLock on relation 39913 of database 39693; blocked by process 26761. Process 26761 waits for RowExclusiveLock on relation 40113 of database 39693; blocked by process 26762.

Run in your database:

SELECT 39913::regclass AS tbl1, 40113::regclass AS tbl2

to see the involved tables. Also consider any triggers and possibly foreign key contraints on involved tables.

Generally: locking tables manually does not necessarily prevent deadlocks. It may be the cause of the the deadlock to begin with.

Upvotes: 3

Related Questions