Search Postgresql Archives

Concurrency Question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux