Process 16121 has asked for, but not acquired, a lock of type AccessExclusiveLock.Let’s go back to the first line of the DETAIL and list the knowns: Process 16121 waits for AccessExclusiveLock on relation “author” of database “test” blocked by process 15866. Therefore, given this information, we can conclude that process 16121 must have already acquired an AccessExclusiveLock on table “book”. Process 15866 is blocked by process 16121.An AccessShareLock is only blocked by an AccessExclusiveLock.The request for an AccessShareLock implies read-only operations will be performed on table “book”.Process 15866 has asked for, but not acquired, a lock of type AccessShareLock.To answer these questions, we’ll start by reviewing the second line of the DETAIL: Process 15866 waits for AccessShareLock on relation “book” of database “test” blocked by process 16121. What lock does process 16121 hold on table “book”?.What lock does process 15866 hold on table “author”?.So, we know what locks are desired by each of these processes, but to really figure out what’s going on we’ll need to answer: Process 15866 is requesting an AccessShareLock on “book” but is not able to acquire one because process 16121 is already holding a conflicting lock on “book”.

Process 16121 is requesting an AccessExclusiveLock on “author” but is not able to acquire one because process 15866 is already holding a conflicting lock on “author”. Process 15866 waits for AccessShareLock on relation “book” of database “test” blocked by process 16121. Let’s take another look at the original error message, substituting these values: ERROR: deadlock detected DETAIL: Process 16121 waits for AccessExclusiveLock on relation “author” of database “test” blocked by process 15866. Putting it all togetherįor the sake of discussion, assume that the problematic relations were identified, using the previously demonstrated queries, as “author” (relation 17389) and “book” (relation 17309), both belonging to database “test” (database 16390).

Acquired by SELECT commands that are read-only (without FOR UPDATE)Ĭombining this knowledge with the process information from the error message, we can now deduce what sequence of operations might have resulted in the deadlock.Acquired by most DDL statements ( ALTER TABLE, DROP TABLE) and maintenance statements ( REINDEX, CLUSTER, VACUUM FULL).Only lock mode that blocks read-only access.Prevents all other transactions from accessing the table in any way for the duration the lock is held (typically until the end of the transaction).Most restrictive lock mode (conflicts with all lock modes).While the entire chapter on Explicit Locking from the PostgreSQL documentation is worth a thorough read, for the purposes of this investigation, we will only focus on the two lock modes specifically indicated in the example error output: ACCESS EXCLUSIVE and ACCESS SHARE. The lock modes control concurrent access to the relation they are acquired on and determine the types of operations that can be performed. Each of these lock types is associated with a corresponding lock mode. In the example, process 16121 requested a lock of type AccessExclusiveLock and process 15866 requested a lock of type AccessShareLock. The error message specifies a lock type that was requested, but not acquired by a given process. Now that we know what relations were involved, let’s examine the lock modes being requested. But, in case it isn’t, you can look that up too: SELECT datname FROM pg_database WHERE oid = 16390 Similarly, the database referenced by database 16390 will be obvious most of the time. This can be accomplished by JOINing on the pg_namespace system catalog : SELECT c.relname AS relation_name, n.nspname AS schema_name FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.oid = 17389 But depending on the complexity of the database, additional context, like a schema, may be required to best narrow down the affected relations.

In most cases, either one of these queries is probably sufficient. We can also get the same result by simply querying the pg_class system table directly : SELECT relname FROM pg_class WHERE oid = 17389 One nifty way of using the OID to retrieve the relation name is by casting it to regclass: SELECT 17389::regclass This isn’t just any integer though, it’s an OID (or “Object Identifier”) - a special type used internally by PostgreSQL for system table primary keys. The error message actually pretty clearly indicates the impacted tables - in this case relation 17389 and relation 17309 - but they’re only identified by an integer. The DETAIL section of the error message above, once deconstructed, takes the following form: Process waits for on relation of database blocked by process.

Postgres deadlock