Can simultaneous updates and deletes on a set of tables lead to AccessExclusiveLock being taken?

I am using Postgres 9.3.

I have 2 tables : authn_session and customer. Every authn_session belongs to a customer (and hence has a customer_id as a column which is a FK to customer's id).

Note : These tables contain additional FK references to other table(s) and indexes.

Now, I start 2 different transactions which do the following in the below mentioned order :

Tx :1

BEGIN; 
UPDATE customer 
    SET customer__created_by = 
            (case when customer__created_by = 1 then 5 
             else customer__created_by end), 
        customer__modified_by = 
            (case when customer__modified_by = 1 then 5 
             else customer__modified_by end);
UPDATE authn_session 
    SET authn_session__created_by = 
            (case when authn_session__created_by = 1 then 5
             else authn_session__created_by end),
        authn_session__modified_by = 
            (case when authn_session__modified_by = 1 then 5
             else authn_session__modified_by end);

Tx : 2

BEGIN;
DELETE FROM authn_session 
    WHERE authn_session__guid IN ('abc3344-ab12-4444-9fdd-f4c5a6f7f210');
DELETE FROM customer 
    WHERE customer__id != 0 
      AND customer__id = 3 
      AND customer__name = 'C2' 
      AND customer__domain_name = 'a2.com';

Now, When I look at the locks, using the query mentioned below, one particular row interests me :

locktype | relation     |mode                |tid| vtid| pid |  granted
tuple    | authn_session|AccessExclusiveLock |   | 11/5| 47894| TRUE

Query :

SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE (db.datname = 'mY-db' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();

Now, according to the postgres documentation AccessExclusiveLocks are granted only in case of :


Acquired by the ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUST

You are quoting from the wrong section of the manual. That passage is from the Table-level Locks section. The lock type you are interested in is specified to be tuple. That means you need to refer to the manual's Row-level Locks section in order to find out when that kind of lock is acquired.

And in the beginning of Row-level Locks it says (original emphasis preserved):


In addition to table-level locks, there are row-level locks, which can be exclusive or shared locks. An exclusive row-level lock on a specific row is automatically acquired when the row is updated or deleted. The lock is held until the transaction commits or rolls back, just like table-level locks. Row-level locks do not affect data querying; they block only writers to the same row.

Therefore, the lock could be acquired either by Tx 1 or by Tx 2, because one is updating rows and the other is deleting rows.

Moreover, as pointed out by ypercubeᵀᴹ in a comment, your update statements are not restricted by a filter, thus each running on the entire table. When run in parallel with a delete, even a filtered one, such an update can naturally be expected to cause collisions acquiring exclusive locks.

StackExchange Database Administrators Q#192644, answer score: 8