Re: Confusing deadlock report

Tom Lane schrieb am 16.03.2016 um 14:45:
>> 2016-03-12 13:51:29.305 CET [23912]: [2-1] user=arthur,db=prod,app=[unknown] DETAIL: Process 23912 waits for
ShareLockon transaction; blocked by process 24342.  
>>         Process 24342 waits for ShareLock on transaction 39632974; blocked by process 23912.
>>         Process 23912: UPDATE alpha SET some_flag = $1 WHERE (id = $2)
>>         Process 24342: INSERT INTO bravo (..., alpha_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
>
>> Can the foreign key between bravo and alpha play a role here?
>
> Absolutely.  The insert will need a sharelock on whatever alpha row the
> new bravo row references.  Perhaps the newly-inserted row references some
> row that 23912 previously updated (in the same transaction) while the
> alpha row 23912 is currently trying to update was previously share-locked
> by 24342 as a side effect of some previous insert?

Hmm, I tried a very simple setup like this:

  create table master (id integer primary key, data text);
  create table child (id integer primary key, master_id integer not null references master on update set null);

  insert into master (id, data)
   values
  (1,'one'),
  (2,'two'),
  (3,'three');

then in one transaction I do:

  update master
    set data = 'bar'
  where id = 1;

and in a second transaction I run:

  insert into child
    (id, master_id)
  values
    (1, 1);

But the second transaction does not wait for the UPDATE to finish.
So I guess it must be a bit more complicated then that.

Thomas