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

Question

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, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly.

I am not doing any of those things explicitly. Then why is my second process (running the second transaction - I checked it's value in DB) getting an AccessExclusiveLock? And why does the pg_activities_blocked view say that the second process (Delete) is blocked by the first (UPDATE)?

BTW, the result of running these two queries simultaneously (one statement from each query at a time) leads to a deadlock . Am I missing anything that could lead to the second process getting an AccessExclusiveLock on the tuple?

Answer 1

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.

Answer 2

I'm speculating here, but I suspect that the ACCESS EXCLUSIVE row (tuple) lock is taken when a row in customer is being deleted to ensure that its children rows in authn_session, which will be deleted imminently by cascading from the referential integrity constraint, are not read by a concurrent transaction.

If it weren't the case, one could imagine a situation where for a concurrent read-only transaction the parent rows may become not visible, while their children remain visible, unless locked, and that would violate integrity.

Note that the lock you see is at the tuple, not table level, so all those table-level statements listed in the documentation are irrelevant in this case.