sql - Postgres deadlock -

sql - Postgres deadlock -

i seeing unexplained deadlocks in our postgres database. simplifying related queries, 1 of transactions involved in deadlock is:

begin; update set chunk_id=1, status='processing' id in ( select id chunk_id null order o_id limit 1000 update ); commit;

and other 1 is:

begin; update set status='sent' id = 1; update set status='sent' id = 2; update set status='sent' id = 3; ... commit;

my question how possible have deadlock here? cannot think of scenario 1st transaction can result deadlock, regardless of other query running @ same time.

is there such case, i.e. update using nested select ... update can part of deadlock?

thanks

(this conjecture, educated one.)

everything hinges on order in rows locked select ... order o_id ... update. if order of o_id different order of id, possible have situation similar this:

id o_id -- ---- 1 2 2 1 transaction locks row id=2. transaction b locks row id=1. transaction tries lock row id=1, forced wait on transaction b. transaction b tries lock row id=2, forced wait on transaction a.

warning: if o_id order same id order, possible order clause doesn't guarantee order of locking (it guarantees order in results returned). unfortunately, seems poorly documented. it's worth, appears oracle doesn't (always) honor order when comes locking, i'd careful under postgresql well.

generally, solution deadlocks lock in same order. assuming order guarantees order of locking, include select ... order o_id ... update in sec transaction. or alternatively, utilize order id in first transaction.

btw, why explicitly locking in first place? trying accomplish that?

sql postgresql-9.2 database-deadlocks