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

postgresql, select, sql, transaction-isolation

Solution

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.

- In the first (old) session, run `SELECT 1 FROM spam_eggs WHERE foo_bar_id = 4 FOR UPDATE;`

- 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""

Problem

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?

Original source