I'm trying to clearly understand how foreign key constraints work. I
still need some help.
The PostgreSQL documentation says:
ROW EXCLUSIVE
Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and
ACCESS EXCLUSIVE lock
modes.
The commands UPDATE, DELETE, and INSERT acquire this lock mode on
the target table (in addition
to ACCESS SHARE locks on any other referenced tables). In general,
this lock mode will be acquired
by any command that modifies the data in a table.
So if my foreign key constraint is: table A b_id references b(id)
and if table B already has an try for id = 5 and I do an insert into
table A with b_id of 5 how does the database ensure that the entry in
table B will still be there by the time the transaction ends? e.g.
if there is an insert into A and a delete from b of id = 5, if the
delete happens first, then the insert should fail. If the insert
happens first, then the delete should fail. But how is this
accomplished?
Looking at the documentation above, I would expect the insert into A
to get a Row exclusive lock for table A. And, I'm guessing it would
get an ACCESS SHARE lock for table B. But this would not prevent the
delete from B from happening at the same time (if I am reading this
correctly).
Can someone help me out here?
Thank you,
Perry
