0

I'm working with a PostgreSQL statement. My goal is to modify the statement to avoid the deadlock problem. Currently, there are 2 statements updating the same records. This must happen in some times and cannot be avoided because it's the real operation in the production.

What I want to do is to create the UPDATE statement that will obtain lock to all records that will be updated. I have tried SELECT FOR UPDATE, LOCK TABLE as exclusive mode , UPDATE in a LOOP". Neither has worked for me. The deadlock still happened. You guys who have any ideas for this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Sathapanic Sriprom
  • 355
  • 2
  • 8
  • 15
  • 4
    You'll need to provide an example. I assume you're updating related rows in multiple tables? – Richard Huxton Sep 17 '12 at 18:36
  • Please show sample code and a timeline that produces the deadlock. See this answer I wrote recently for an example of what I mean: http://stackoverflow.com/questions/12455962/calculate-next-primary-key-of-specific-format/12456645#12456645 – Craig Ringer Sep 17 '12 at 23:16
  • The key things with deadlock avoidance is that you need to acquire your locks in the same order from each transaction. Annoyingly `UPDATE` doesn't have `ORDER BY`, so `SELECT ... ORDER BY ... FOR UPDATE` is often needed. – Craig Ringer Sep 18 '12 at 00:05

1 Answers1

1

Mind using advisory locking, in case you cannot figure out the exact relations or records to lock.

Kouber Saparev
  • 7,637
  • 2
  • 29
  • 26