Forum OpenACS Q&A: transactions again

Collapse
Posted by Jonathan Ellis on
From the verbose log, sometimes transactions of the form
update minions set...
update parties set...
deadlock with each other. There's no subselects involved, and all are updating minions before parties. Sometimes, there is more updates to minions after the update to parties, but I thought that didn't matter -- once a table was exclusively locked during a transaction, it stayed locked.

Here's an example, involving threads 13325, 17425, 15375, and 21525

[26/Jan/2002:17:25:17][2045.21525][-conn14-] Notice: dbinit: sql
(localhost::bf2): 'begin transaction'
[26/Jan/2002:17:25:17][2045.21525][-conn14-] Notice: Querying 'update 
minions set tp = tp + 2 where id = 1084;'
[26/Jan/2002:17:25:17][2045.13325][-conn6-] Notice: dbinit: sql
(localhost::bf2): 'begin transaction'
[26/Jan/2002:17:25:17][2045.13325][-conn6-] Notice: Querying 'update 
minions set hp = hp_max where party_id = 633;'
[26/Jan/2002:17:25:17][2045.21525][-conn14-] Notice: dbinit: sql
(localhost::bf2): 'update minions set tp = tp + 2 where id = 1084'
[26/Jan/2002:17:25:17][2045.21525][-conn14-] Notice: Querying 'update 
minions set tp = tp + 2 where id = 1222;'
[26/Jan/2002:17:25:18][2045.17425][-conn10-] Notice: dbinit: sql
(localhost::bf2): 'begin transaction'
[26/Jan/2002:17:25:18][2045.17425][-conn10-] Notice: Querying 'update 
minions set tp = tp + 2 where id = 248;'
[26/Jan/2002:17:25:18][2045.15375][-conn8-] Notice: dbinit: sql
(localhost::bf2): 'begin transaction'
[26/Jan/2002:17:25:18][2045.15375][-conn8-] Notice: Querying 'update 
minions set tp = tp + 1 where id = 1182;'
[26/Jan/2002:17:25:18][2045.21525][-conn14-] Notice: dbinit: sql
(localhost::bf2): 'update minions set tp = tp + 2 where id = 1222'
[26/Jan/2002:17:25:18][2045.21525][-conn14-] Notice: Querying 'update 
minions set tp = tp + 2 where id = 1228;'
[26/Jan/2002:17:25:18][2045.13325][-conn6-] Error: Ns_PgExec: result 
status: 7 message: ERROR:  Deadlock detected.
        See the lock(l) manual page for a possible cause.
Here, none of the threads involved had even gotten to issuing an update on parties...
Collapse
Posted by Don Baccus on
Remember that PG implements row-level, not table-level, locking for writes as well as reads.  You've undoubtably got intersecting rowsets being updated.  There's not a one-to-one correspondance between updates and locks (row-level locks, right?!?).  The "update in same order" paradigm for avoiding deadlock would only work if there was a one-to-one correspondance (i.e. as in table locking) because in effect you'd be executing the same locks in the same order.

You need to use "select for update" on the rows you're going to update.  That sets exclusive locks on those rows and will block the select until it can get locks on each row.  Then you can update knowing that the changing rows belong to you and no one else until the end of your transaction.

Alternatively you could do an exclusive lock operation on the table (check the documentation on the LOCK command) using your ordering technique.  But that's a more severe level of locking than you need.  You'll get higher concurrency with "select for update" unless you're updating a big huge bunch of rows at once. "select for update" has to write a lock bit for each row so it can be expensive if there are lots of rows, and trivially if you "select * for update from foo", i.e. the whole table, you've done the same as "LOCK EXCLUSIVE" but with a lot more work, i.e. touching every row in the table.

But typically updates hit a small percentage of rows and normally separate updates don't intersect so everyone can play concurrently.  Since you're not deadlocking frequently it would appear you're not issuing a high number of intersecting, interfering, concurrent updates so "select for update" is for you!  An exclusive lock blocks everyone, even non-intersecting updates, so really is overkill for this case IMO.

"select for update" is explicitly there for transactions that update multiple tables.  "update" itself grabs the right locks (essentially a "select for update", touching the rows is fine because they're getting updated anyway) so transactions that update a single table work fine.  The problem is that it releases them at the end of the statement.  An explicit "select for update" holds them for the entire transaction.

Collapse
Posted by Stan Kaufman on
Don, your explanation was *very* helpful and clear (as usual). Thanks for clarifying something I also have been looking into!
Collapse
Posted by Jonathan Ellis on
ah... a light dawns!  thanks a million :)