I am using Postgres 9.3.
I have 2 tables :
authn_session belongs to a
customer (and hence has a
customer_id as a column which is a FK to customer's
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 :
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
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?