What will happen in PostgreSQL if a cascading delete is attempted on the parent of a locked row?

Question

I have a table foo_bar and another table spam_eggs with a fb foreign key pointing to foo_bar. spam_eggs rows are cascade deleted when their related spam_eggs.fb are deleted.

I'm working with PostgreSQL.

In a transaction I have used SELECT... FOR UPDATE to lock a spam_eggs row. In the duration of this transaction, another transaction has attempted to DELETE FROM... the related foo_bar of my locked row. Will this trigger an error, or will my locked row cause the query to block until the end of my original update transaction?

Answer 1

Try it and see. Open psql and do some setup:

CREATE TABLE foo_bar(id integer primary key);
CREATE TABLE spam_eggs(
     foo_bar_id integer not null references foo_bar(id) on delete cascade
);
INSERT INTO foo_bar (id) VALUES (1),(2),(3),(4);
INSERT INTO spam_eggs(foo_bar_id) VALUES (1),(2),(3),(4);

then open another psql connection. BEGIN a transaction in both of them.

1. In the first (old) session, run SELECT 1 FROM spam_eggs WHERE foo_bar_id = 4 FOR UPDATE;
2. In the second (new) session, run DELETE FROM foo_bar WHERE id = 4;

You will see that the second statement blocks on the first. That's because the DELETE on foo_bar cascades to spam_eggs and attempts to lock the row with the foreign key reference so it can delete it. That lock blocks on the lock held by the SELECT ... FOR SHARE.

In general, try to test in all these circumstances:

• tx's are BEGIN ISOLATION LEVEL READ COMMITTED and first issues a ROLLBACK
• tx's are BEGIN ISOLATION LEVEL READ COMMITTED and first issues a COMMIT
• tx's are BEGIN ISOLATION LEVEL SERIALIZABLE and first issues a ROLLBACK
• tx's are BEGIN ISOLATION LEVEL SERIALIZABLE and first issues a COMMIT

to make sure you know what to expect. It's also good for your learning if you reason through what you expect to happen before testing it.

In this case the READ COMMITTED and SERIALIZABLE isolation levels will behave the same. If you actually do an UPDATE after your SELECT ... FOR UPDATE and then COMMIT then they'll behave differently, though; the READ COMMITTED version will DELETE successfully, while the SERIALIZABLE version will fail with:

regress=# BEGIN ISOLATION LEVEL SERIALIZABLE;
regress=# DELETE FROM foo_bar WHERE id = 4;                                                                                                                                                                                                    
ERROR:  could not serialize access due to concurrent update                                                                                                                                                        
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."spam_eggs" WHERE $1 OPERATOR(pg_catalog.=) "foo_bar_id""