Foreign key deadlocks
От | Csaba Nagy |
---|---|
Тема | Foreign key deadlocks |
Дата | |
Msg-id | 96D568DD7FAAAD428581F8B3BFD9B0F604DE59@goldmine.ecircle.de обсуждение исходный текст |
Ответы |
Re: Foreign key deadlocks
|
Список | pgsql-general |
Hi all, I'm currently porting an application from Oracle to Postgres. Most of the things are working fine, but I still couldn't sort the problems with foreign keys. When creating the foreign key constraints as they are in the Oracle schema, the application deadlocks quite often. The reason is AFAICT the update lock placed on the referenced row when inserting in a table with foreign key constraints. In some tables we have lots of foreign key constraints, and the application has some fairly long transactions running concurrently, and even if I order the updates/inserts so that they lock the tables in the same order all the time, I still can't controll the locking done by the foreign key constraints... so I get deadlocks. The fact that an insert will cause an exclusive lock on the referenced row for a foreign key constraint is a severe performance restriction anyway, because no other concurrent transaction can insert a row in any other table referencing the same row... the only way to work this around was not to use foreign key constraints at all, but this is not an acceptable resolution. I will give an example to make things clear. Let's say I have the following (simplified) tables: create table table_1 ( key_1 bigint primary key, description text ); create table table_2 ( key_2 bigint primary key, key_1 bigint references table_1 (key_1), description text ); create table table_3 ( key_3 bigint primary key, key_1 bigint references table_1 (key_1), description text ); The usage scenario is as follows: - table_1 is updated fairly often, with all possible operations (insert/delete/update); - table_2 and table_3 are updated frequently, with all possible operations (insert/delete/update); - the operations have to run in fairly long transactions, affecting many rows in table_2/table_3; - the system is highly concurrent; Any insert in table_2/table_3 will block the referenced row in table_1, and so any other concurrent insert which would reference that row in any of table_2/table_3 will wait until the locking transaction finishes... even if they would not need to (they don't change the referenced row...) This is an unnecessary exclusive lock on that row (a shared lock would do). Using table locks as a workaround is not an option either, as the transactions are fairly long running, and it would be an even bigger performance problem. Now this scenario is a simple one, imagine what happens if there are more tables hierarchically related by foreign key constraints involved in different transactions. I personally was unable to figure out how to avoid deadlocks between rows locked in multiple tables by foreign key constraints... I can't believe this is a deliberate way to handle row locks, it just doesn't make sense. Am I missing some crucial design issue which makes the shared row locks impossible ? I have read the faq, documentation (to a reasonable degree), but I found no viable solution. I've tried also the DEFERRABLE INITIALLY DEFERRED approach but that won't work either, because some of the code relies on getting the referenced key checked immediately on an insert, and abort transaction if it fails (with deferring, the transaction goes on after an insert with broken foreign reference and rolls back on finish - unacceptable given the length of the transactions). Is there any guidline about how to solve this ? Rewriting the code is not an option, it relies too heavily on how foreign key constraints work in Oracle. Thanks in advance, Csaba.
В списке pgsql-general по дате отправления: