Thread: foreign key constraint lock behavour in postgresql
foreign key constraint lock behavour :
The referenced FK row would be added some exclusive lock , following is the case:
CREATE TABLE tb_a
( id character varying(255) NOT NULL, "name" character varying(255), b_id character varying(255) NOT NULL, CONSTRAINT tb_a_pkey PRIMARY KEY (id), CONSTRAINT fk_a_1 FOREIGN KEY (b_id) REFERENCES tb_b (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE TABLE tb_b
( id character varying(255) NOT NULL, "name" character varying(255), CONSTRAINT tb_b_pkey PRIMARY KEY (id)
)
before these two transaction begin ,the tb_b has one rows: {id:"b1",name:"b1"}
transaction 1:
begin transaction;
insert into tb_a(id,b_id) values('a1','b1');
//block here;
end transaction;
-----------------
transaction 2:
begin transaction;
// if transaction 1 first run , then this statement would be lock untill transaction1 complete.
update tb_b set name='changed' where id='b1';
end transction;
-----------------
transaction 3:
begin transaction;
delete tb_b where id='b1';
end transaction;
-------------
result:
in postgresql8.4 , transaction 2 and transaction 3 would be block until transaction 1 complete.
in oracle10g , transaction 2 would ne be block ,but transaction 3 would be block .
in mysql5 with innoDB, same behavour with postgresql5
my analyze:
For the FK constraints ,this is reasonable , there is this case may happen:
when one transaction do insert into tb_a with the fk reference to one row ('b1') on tb_b,
simultaneously , another transaction delete the 'b1' row, for avoid this concurrency confliction , then need to lock the 'b1' row.
from this point ,I think i can find some magic why mysql take so better performance for bulk update or delete on concurrency transactions .
oracle use better level lock to avoid block when do update
On Thu, 4 Feb 2010, wangyuxiang wrote:
> foreign key constraint lock behavour :
>
>
> The referenced FK row would be added some exclusive lock , following is the case:
>
> CREATE TABLE tb_a
> (
> id character varying(255) NOT NULL,
> "name" character varying(255),
> b_id character varying(255) NOT NULL,
> CONSTRAINT tb_a_pkey PRIMARY KEY (id),
> CONSTRAINT fk_a_1 FOREIGN KEY (b_id)
> REFERENCES tb_b (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
>
> CREATE TABLE tb_b
> (
> id character varying(255) NOT NULL,
> "name" character varying(255),
> CONSTRAINT tb_b_pkey PRIMARY KEY (id)
> )
>
> before these two transaction begin ,the tb_b has one rows: {id:"b1",name:"b1"}
>
>
> transaction 1:
>
> begin transaction;
> insert into tb_a(id,b_id) values('a1','b1');
>
> //block here;
>
> end transaction;
> -----------------
> transaction 2:
>
> begin transaction;
> // if transaction 1 first run , then this statement would be lock untill transaction1 complete.
> update tb_b set name='changed' where id='b1';
>
> end transction;
> -----------------
>
> transaction 3:
>
> begin transaction;
>
> delete tb_b where id='b1';
>
> end transaction;
> -------------
>
> result:
> in postgresql8.4 , transaction 2 and transaction 3 would be block until transaction 1 complete.
> in oracle10g , transaction 2 would ne be block ,but transaction 3 would be block .
> in mysql5 with innoDB, same behavour with postgresql5
>
>
> my analyze:
>
> For the FK constraints ,this is reasonable , there is this case may happen:
>
> when one transaction do insert into tb_a with the fk reference to one row ('b1') on tb_b,
> simultaneously , another transaction delete the 'b1' row, for avoid this concurrency confliction , then need to lock
the'b1' row.
>
> from this point ,I think i can find some magic why mysql take so better performance for bulk update or delete on
concurrencytransactions .
>
> oracle use better level lock to avoid block when do update
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.
David Lang
On Thu, Feb 4, 2010 at 12:40 AM, <david@lang.hm> 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
Robert Haas wrote:
> 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?
I have investigated what Oracle (10.2) does in this situation.
First the original sample as posted by wangyuxiang:
insert into tb_a(id,b_id) values('a1','b1');
will place a ROW EXCLUSIVE lock on tb_a, an EXCLUSIVE lock
on the row that was inserted and a ROW SHARE lock on tb_b.
No lock on any row in the parent table is taken.
update tb_b set name='changed' where id='b1';
will place a ROW EXCLUSIVE lock on tb_b and an EXCLUSIVE
lock on the modified column.
Since ROW EXCLUSIVE and ROW SHARE do not conflict, both statements
will succeed.
Now to your question:
update tb_b set id='b2' where id='b1';
This will place a ROW EXCLUSIVE lock on tb_b, an EXCLUSIVE lock
on the updated row and a SHARE lock on tb_a.
This last lock is only held for the duration of the UPDATE statement
and *not* until the end of the transaction.
So this update will block, because the SHARE and the ROW EXCLUSIVE
lock on tb_a are incompatible.
So it seems that Oracle handles this quite differently.
I was particularly surprised that it uses locks that are not held
until end-of-transaction.
Yours,
Laurenz Albe
On Fri, Feb 5, 2010 at 4:00 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Robert Haas wrote:
>> 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?
>
> I have investigated what Oracle (10.2) does in this situation.
>
> First the original sample as posted by wangyuxiang:
>
> insert into tb_a(id,b_id) values('a1','b1');
>
> will place a ROW EXCLUSIVE lock on tb_a, an EXCLUSIVE lock
> on the row that was inserted and a ROW SHARE lock on tb_b.
> No lock on any row in the parent table is taken.
>
> update tb_b set name='changed' where id='b1';
>
> will place a ROW EXCLUSIVE lock on tb_b and an EXCLUSIVE
> lock on the modified column.
>
> Since ROW EXCLUSIVE and ROW SHARE do not conflict, both statements
> will succeed.
>
>
> Now to your question:
>
> update tb_b set id='b2' where id='b1';
>
> This will place a ROW EXCLUSIVE lock on tb_b, an EXCLUSIVE lock
> on the updated row and a SHARE lock on tb_a.
> This last lock is only held for the duration of the UPDATE statement
> and *not* until the end of the transaction.
>
> So this update will block, because the SHARE and the ROW EXCLUSIVE
> lock on tb_a are incompatible.
>
>
> So it seems that Oracle handles this quite differently.
> I was particularly surprised that it uses locks that are not held
> until end-of-transaction.
Yeah, that seems odd. I assume they know what they're doing; they're
Oracle, after all. It does sound, too, like they have column level
locks based on your comment about "an EXCLUSIVE lock on the modified
column". I doubt we're likely to implement such a thing, but who
knows. Another interesting point is that a statement that involves
only tb_b can trigger a share lock on tb_a; presumably that means they
know they need to take a share lock on every table that references the
updated column, which seems like it could be fairly expensive in the
worst case.
One idea that occurs to me is that it might be possible to add to PG
some tuple lock modes that are intended to cover updates that don't
touch indexed columns. So, say:
SHARED NONINDEX - conflicts only with EXCLUSIVE locks
SHARED - conflicts with EXCLUSIVE or EXCLUSIVE NONINDEX locks
EXCLUSIVE NONINDEX - conflicts with any lock except SHARED NONINDEX.
must have this level or higher to update tuple.
EXCLUSIVE - conflicts with any other lock. must have this to update
any indexed column of a tuple.
Then a foreign key constraint could take a SHARED NONINDEX lock on the
target tuple, because any column that's the target of a foreign key
must be indexed; and so we don't care if the nonindexed columns get
updated under us. I think. Also, I believe you'd also need to
duplicate any SHARED NONINDEX locks for any new versions of the tuple
that got created while the lock was held, which might be sticky.
...Robert
Robert Haas wrote:
[explanation of how Oracle locks on Updates involving foreign keys]
>
> Yeah, that seems odd. I assume they know what they're doing; they're
> Oracle, after all. It does sound, too, like they have column level
> locks based on your comment about "an EXCLUSIVE lock on the modified
> column". I doubt we're likely to implement such a thing, but who
> knows.
Sorry, that was a mistake. I meant "an EXCLUSIVE lock on the modified
row". Oracle works quite like PostgreSQL in locking modified rows.
> Another interesting point is that a statement that involves
> only tb_b can trigger a share lock on tb_a; presumably that means they
> know they need to take a share lock on every table that references the
> updated column, which seems like it could be fairly expensive in the
> worst case.
Yes, that's the only way Oracle's method makes sense, by taking out
a shared lock on every table that references the updated table.
It may be expensive, but as the example shows, it also allows concurrency
in a way that PostgreSQL doesn't, so maybe it's worth the pain.
On the other hand, Oracle has some problems that PostgreSQl doesn't.
If you run the following example, assuming the original setup of
wangyuxiang:
SESSION 2:
BEGIN;
UPDATE tb_b SET id='b2' WHERE id='b1';
SESSION 1:
INSERT INTO tb_a (id,b_id) VALUES ('a1','b1');
SESSION 2:
UPDATE tb_b SET id='b1' WHERE id='b2';
COMMIT;
it will succeed just fine on PostgreSQL (with SESSION 1 blocking until
SESSION 2 COMMITs), but on Oracle it will cause a deadlock aborting
SESSION 1.
So, according the the principle of preservation of difficulties, both
implementations have their snags, and I wouldn't say that PostgreSQL
is worse off.
> One idea that occurs to me is that it might be possible to add to PG
> some tuple lock modes that are intended to cover updates that don't
> touch indexed columns. So, say:
>
> SHARED NONINDEX - conflicts only with EXCLUSIVE locks
> SHARED - conflicts with EXCLUSIVE or EXCLUSIVE NONINDEX locks
> EXCLUSIVE NONINDEX - conflicts with any lock except SHARED NONINDEX.
> must have this level or higher to update tuple.
> EXCLUSIVE - conflicts with any other lock. must have this to update
> any indexed column of a tuple.
>
> Then a foreign key constraint could take a SHARED NONINDEX lock on the
> target tuple, because any column that's the target of a foreign key
> must be indexed; and so we don't care if the nonindexed columns get
> updated under us. I think. Also, I believe you'd also need to
> duplicate any SHARED NONINDEX locks for any new versions of the tuple
> that got created while the lock was held, which might be sticky.
That should work and improve concurrency in PostgreSQL!
Yours,
Laurenz Albe
I wrote: > > One idea that occurs to me is that it might be possible to add to PG > > some tuple lock modes that are intended to cover updates that don't > > touch indexed columns. So, say: > > > > SHARED NONINDEX - conflicts only with EXCLUSIVE locks > > SHARED - conflicts with EXCLUSIVE or EXCLUSIVE NONINDEX locks > > EXCLUSIVE NONINDEX - conflicts with any lock except SHARED NONINDEX. > > must have this level or higher to update tuple. > > EXCLUSIVE - conflicts with any other lock. must have this to update > > any indexed column of a tuple. > > > > Then a foreign key constraint could take a SHARED NONINDEX lock on the > > target tuple, because any column that's the target of a foreign key > > must be indexed; and so we don't care if the nonindexed columns get > > updated under us. I think. Also, I believe you'd also need to > > duplicate any SHARED NONINDEX locks for any new versions of the tuple > > that got created while the lock was held, which might be sticky. > > That should work and improve concurrency in PostgreSQL! Even more if EXCLUSIVE NONINDEX is also used for updates that change indexed columns where the index is not UNIQUE. Yours, Laurenz Albe