On Thu, Feb 4, 2010 at 12:40 AM, <david@xxxxxxx> wrote: > I could be wrong in this (if so I know I'll be corrected :-) > > but Postgres doesn't need to lock anything for what you are describing. > > instead there will be multiple versions of the 'b1' row, one version will be > deleted, one version that will be kept around until the first transaction > ends, after which a vaccum pass will remove the data. Just for kicks I tried this out and the behavior is as the OP describes: after a little poking around, it sees that the INSERT grabs a share-lock on the referenced row so that a concurrent update can't modify the referenced column. It's not really clear how to get around this. If it were possible to lock individual columns within a tuple, then the particular update above could be allowed since only the name is being changed. Does anyone know what happens in Oracle if the update targets the id column rather than the name column? Another possibility is that instead of locking the row, you could recheck that the foreign key constraint still holds at commit time. But that seems like it could potentially be quite expensive. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
