Обсуждение: Concurrency Question
I'm trying to clearly understand how foreign key constraints work. I still need some help.
The PostgreSQL documentation says:
ROW EXCLUSIVEConflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lockmodes.The commands UPDATE, DELETE, and INSERT acquire this lock mode on the target table (in additionto ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquiredby 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
"Perry Smith" <pedz@easesoftware.com> writes: > 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). The bit you quoted was for tables. The RI trigger does indeed take a share lock on the referenced record in table B which prevents it from being deleted. (In older versions it used to take an exclusive lock because there were no share locks on records.) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com