2

I'm trying to understand how a deadlock happened in our database and I'm wondering if the culprit could have been an update that did not run in a transaction.

If I have two tables

CREATE TABLE user(
   id SERIAL PRIMARY KEY,
);

CREATE TABLE posts(
   id SERIAL PRIMARY KEY,
   user_id INT,
   
   CONSTRAINT fk_author
      FOREIGN KEY(user_id) 
      REFERENCES users(id)
);

If I update the posts table and change the user_id, I understand that Postgres will take out an implicit KEY SHARE lock on the referenced row of the users table.

My question is, is this lock taken out at the same time as the LOCK FOR NO KEY UPDATE that's taken out on the posts row, or is it possible that on a high traffic service, that another process could sneak in and take out a lock on the users table?

eg If I do

UPDATE posts SET user_id = 2 WHERE post_id = 1;

I know that behind the scenes postgres will do

  1. Lock the post for NO KEY UPDATE
  2. Lock the referenced user row for KEY SHARE

Is it possible for another session to sneak in between steps 1 & 2? eg

  1. Session 1: Lock the post for NO KEY UPDATE
  2. Session 2: (does something that locks users row for UPDATE)
  3. Session 1: Lock the referenced user row for KEY SHARESHARE
  4. Session 2: locks posts row for UPDATE = deadlock

Is this possible?

I initially assumed no, but the more I read about how FK constraints work and that they're essentially triggers on a row, I'm thinking maybe the answer is yes...?

2

No, taking multiple locks is not atomic. Yes you can get a deadlock. It is easy to demonstrate this by reversing steps 1 and 2.

With the steps in the order you give, it would be hard to get the timing exactly right to hit it. But it is the same principle so it must also be possible.

1
  • Thanks @jjanes, I've updated my example. I wanted to clarify that in the example I'm not manually taking out the lock but this is the implicit locking during an UPDATE. Is it still the case that these two locks could become deadlocked?
    – ChrisJ
    Sep 5 '21 at 23:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.