Thread: [BUGS] Unique Constraint Provides Lock in Transaction
Bug reproducible 100% PostgreSQL 9.5, 9.6
Important:
Unique constraint assigned on column 'name' of table 'table_a'
In PgAdmin Auto Commit should be disabled
It is important to have 2 foreign keys from table_b to table_a
1. Within 1st Transaction was executed following query(Important For Each Execution value for 'name' should be unique):
update table_a SET name = 'n10' where id = '1';
2. Within 2nd Transaction was executed following queries(It is Important: Should be executed at once both queries):
update table_b SET name = 't1' where id = '1';
update table_b SET name = 't1' where id = '1';
3. Observe 2nd transaction hanged until we did not commit 1st transaction
4. Transaction 2 does not hang:
- if we remove unique constraint,
- If we run in first Transaction same query several times(without actual update name column)
- If we run in second transaction on update instead of two
- if we run in second transaction both updates separately
- if we remove all foreign keys from table_b
- if we remove one foreign key from table_b
Schema with data can be found bellow: