Geek Answers Handbook

What happens in PostgreSQL if a cascading delete attempt is made on the parent of a locked row?

I have a foo_bar table and another spam_eggs table with a foreign key fb pointing to foo_bar . The spam_eggs lines are cascaded when the associated spam_eggs.fb are spam_eggs.fb .

I work with PostgreSQL.

In a transaction, I used SELECT... FOR UPDATE to lock the spam_eggs line. During this transaction, another transaction tried DELETE FROM... to bind foo_bar my locked row. Will this result in an error, or will my locked row block the request until the end of my initial update transaction?

+4
source share
1 answer

Try and see. Open psql and make some settings:

 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 transaction in both of them.

  • In the first (old) session, execute 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 the first. This is because DELETE on foo_bar cascades to spam_eggs and tries to block the line using a foreign key reference so that it can delete it. This lock is locked by the lock held by SELECT ... FOR SHARE .

In general, try testing in all of these circumstances:

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

to make sure you know what to expect. It is also good for your training if you reason what you expect before testing it.

In this case, the isolation levels READ COMMITTED and SERIALIZABLE will behave the same. If you actually do UPDATE after SELECT ... FOR UPDATE and then COMMIT , then they will behave differently; the READ COMMITTED version will be DELETE successful, and the SERIALIZABLE version will fail:

 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""
+11
source

More articles:


All Articles