using deferred initially deferred to solve foreign key checking issues
От | Philip de Nier |
---|---|
Тема | using deferred initially deferred to solve foreign key checking issues |
Дата | |
Msg-id | 6.0.1.1.2.20060821192017.02787b90@pop3 обсуждение исходный текст |
Ответы |
Re: using deferred initially deferred to solve foreign key checking issues
|
Список | pgsql-general |
Hi I'm having problems with concurrent transactions that involve foreign key constraints that use "SELECT ... FOR UPDATE". These constraints result in one transaction waiting for another (failing to get a ShareLock on a transaction which already has an ExclusiveLock). The solutions I could find in the mailing lists were either to upgrade to version 8.1 which uses "SELECT ... FOR SHARE" (I'm currently using 8.0), stop using foreign keys or add "DEFERRABLE INITIALLY DEFERRED" to the constraints. For now I'd prefer to use the last option. My questions are as follows: Does using "DEFERRABLE INITIALLY DEFERRED" completely solve the problem of transactions waiting for each other to release locks resulting from "SELECT ... FOR UPDATE"? How are the deferred foreign key constraints checked - are they checked one at a time? If they are checked one at a time then transactions will no longer be waiting on each other to release locks because each transaction will only have 1 lock. Thanks Philip
В списке pgsql-general по дате отправления: