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
​
UniqueConstarintProvidesTxLock720p.mov
<https://drive.google.com/file/d/0B-6GZrKKJFV5OW5hU1dBbm5TZVU/view?usp=drive_web>
​

Schema with data can be found bellow:

[image: Inline image 1]