CODEWITHSUNDEEP
postgresqllocking
Aliaksei Stadnik
Aliaksei Stadnik

Reputation: 1948

Does postgres "SELECT FOR UPDATE" restrict to create new foreign key references ?

There are two tables, one is referenced by another with foreign key constraint.

CREATE TABLE record
(
  id                    UUID PRIMARY KEY  NOT NULL
);    

CREATE TABLE task
(
  id                    UUID PRIMARY KEY  NOT NULL,
  record_id       UUID,
  CONSTRAINT fk_task_record_id FOREIGN KEY (record_id) 
  REFERENCES record (id)
);

The question is : if lock on record row is aquired by 

SELECT * FROM record where id = 'A' FOR UPDATE OF record

Could I create new foreign key in task table and reference this very record row ? 

INSERT INTO task VALUES ('someId', 'A');

Does Postgres prevent creating new foreign key references on tables locked by SELECT FOR UPDATE OF?

Upvotes: 1

Views: 1195

Answers (2)

Abhinav Manchanda
Abhinav Manchanda

Reputation: 6641

Yes, it does.

Connect to your postgres database in 2 different terminals, and run the following queries in terminal 1 -

BEGIN;
SELECT * FROM record where id = 'A' FOR UPDATE OF record;

In terminal 2, run -

INSERT INTO task VALUES ('someId', 'A');

Again in terminal 1, run -

COMMIT;

You will notice that the query in terminal 2 does not return till you do a commit in Terminal 1. This is because the for update keyword makes the query wait even for tables that reference it using a foreign key.

Upvotes: 2

user330315
user330315

Reputation:

No, it does not.

select .. for update only blocks changes (update, delete) to that row.

It does not prevent other transactions from reading that row and that's what is required to insert a row referencing the locked row.

Upvotes: 0

Related Questions