Re: Fix FK deadlock, but no magic please
От | Stephan Szabo |
---|---|
Тема | Re: Fix FK deadlock, but no magic please |
Дата | |
Msg-id | 20030116114912.X7758-100000@megazone23.bigpanda.com обсуждение исходный текст |
Ответ на | Re: Fix FK deadlock, but no magic please (Jon Swinth <jswinth@atomicpc.com>) |
Ответы |
Re: Fix FK deadlock, but no magic please
(Jon Swinth <jswinth@atomicpc.com>)
|
Список | pgsql-general |
On Thu, 16 Jan 2003, Jon Swinth wrote: > > Record read locks are not quite as good a solution as dirty reads from a > > performance standpoint, which is why we've been aiming that direction > > first. You'd need column locks pretty much to get equivalent behavior > > afaict. The issue is that with record read locks, you prevent updates to > > rows that do not affect the key values. > > > > >From the standpoint of expected behaviour, I don't think you have any choice > but to use record read locks. When someone does a write lock on a FK table > record they have the expectation that they can do anything they want with the > record including changing the PK or deleting the record. That is as long as > there were no referencing records before the write lock was obtained. This > means that someone else shouldn't be able to insert a record referencing > while the FK table record has a write lock. > > Not being able to get a read lock when someone else has a write lock is > expected behaviour. A single record should be able to have one write lock or > multiple read locks, but not both. If I have a program that checks for > referencing records, deletes them if found, and obtains a write lock on the > FK record then I should reasonably assume that I can change anything about > that record including delete it. If you don't prevent the write lock when a > read lock is there then the person obtaining the write lock to very well get > errors that they wouldn't normally expect. Well, for example (assuming two pk rows with 1 and 2 as keys) T1: begin; T1: insert into fk values (1); T2: begin; T2: insert into fk values (2); T1: update pk set nonkey='a' where key=2; T2: update pk set nonkey='b' where key=1; Should this deadlock? I'd say no, barring triggers/rules, because those two updates shouldn't affect the constraint and so whenever possible the constraint's behavior shouldn't interfere. But the obvious record read lock solution would afaics. The inserts would grab a read lock on the two pk rows, neither transaction would be able to get the write lock it wants and it'd deadlock. Or, what about T1: begin; T1: insert into fk values (1); T2: begin; T2: insert into fk values (1); T1: update pk set nonkey='a' where key=1; T2: update pk set nonkey='a' where key=1; Without those inserts this would be okay, one would wait for the other and everything would be okay, but with them I think this deadlocks as well. Maybe I'm misunderstanding the scheme you'd expect the foreign keys to use with the read locks.
В списке pgsql-general по дате отправления: