1

I noticed that concurrent execution of simple and identical query:

BEGIN; 
SELECT items.item_id AS items_item_id
FROM items
WHERE items.item_id = 1 FOR UPDATE;
COMMIT;

lead to deadlock which is surprising to me since it looks like such queries should not create a deadlock.

Table and data for this test case defined as:

CREATE TABLE items (
    item_id serial PRIMARY KEY
);
insert into items  Values (1);

Python code that execute the above SQL statement through SQLAlchemy is:

item = DB.query(Item).filter( Item.item_id == 1).with_for_update().one()
time.sleep(0.001)
DB.commit()

SQLALchemy Item table definition:

class Item(Base):
    __tablename__ = 'items'
    item_id = Column(Integer, primary_key=True)

During such deadlock situation if I run:

SELECT blocked_locks.pid     AS blocked_pid,
 blocking_locks.pid     AS blocking_pid,
 blocked_activity.query    AS blocked_statement,
 blocking_activity.query   AS current_statement_in_blocking_process
FROM  pg_catalog.pg_locks         blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks         blocking_locks 
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid

JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

I am getting circular dependency when same SELECT statement depend on each other. Here is typical output (note that 24869 and 24868 depend on each other):

 blocked_pid | blocking_pid |           blocked_statement            | current_statement_in_blocking_process
-------------+--------------+----------------------------------------+----------------------------------------
       24867 |        24865 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24868 |        24867 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24870 |        24867 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24871 |        24867 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24869 |        24867 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24870 |        24868 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24871 |        24868 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24869 |        24868 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24868 |        24869 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24870 |        24869 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24871 |        24869 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24868 |        24870 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24871 |        24870 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24869 |        24870 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24868 |        24871 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24870 |        24871 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
       24869 |        24871 | SELECT items.item_id AS items_item_id +| SELECT items.item_id AS items_item_id +
             |              | FROM items                            +| FROM items                            +
             |              | WHERE items.item_id = 1               +| WHERE items.item_id = 1               +
             |              |  LIMIT 1 FOR UPDATE                    |  LIMIT 1 FOR UPDATE
(17 rows)

So my questions is:

  • is such behavior normal and should be expected or there is a bug in my code (or possibly in SQLAlchemy/PostgreSQL)?

  • if such behavior is normal and expected: why this is happened? Particularly, I was under impression that FOR UPDATE should be atomic and can not depend on each other (is this correct assumption?)

  • is it possible to somehow modify application code to avoid such deadlocks without using NO WAIT or SKIP LOCKED in FOR UPDATE?

EDIT: adding missed BEGIN; ... COMMIT; into code

Yatima
  • 119
  • 7
  • @ Laurenz Albe Could you please elaborate? in table above i see 24869 to be blocked by 24868 and next row shows 24868 being blocked by 24869, how that is not a deadlock? – Yatima Jan 23 '19 at 20:21
  • Re quarry: i was using standard quarry for this from PostgreSQL wiki: https://wiki.postgresql.org/wiki/Lock_Monitoring (second one). Could you please elaborate why `AND blocking_locks.granted` would be useful in this case? – Yatima Jan 23 '19 at 23:56
  • I have deleted my previous comments because they were ill-researched. Please answer: does the deadlock resolver start after a second and kill one of the processes? That is, did you manage to run your query during the one second when there was actually a deadlock in the database? If no, and the deadlock persists indefinitely, there is probably some bug involved. If yes, then things are as expected, and there must have been some prior statements in the same transaction, because statements that only lock a single row can never deadlock with each other. – Laurenz Albe Jan 24 '19 at 05:43
  • @Laurenz Albe from what i can tell deadlock persist indefinitely (certainly at least a few minutes, if not more) – Yatima Jan 24 '19 at 17:09
  • Scary. Could you get a plain `SELECT * FROM pg_stat_activity` AND `SELECT * FROM pg_locks` during such a deadlock and add it to the question? – Laurenz Albe Jan 24 '19 at 17:31

0 Answers0