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