I have a problem to understand, how those two queries produce deadlock (from postgresql log):
2011-09-19 15:00:31 GET 2496 ERROR: deadlock detected
2011-09-19 15:00:31 GET 2496 DETAIL: Process 2496 waits for ShareLock on transaction 6667; blocked by process 1084.
Process 1084 waits for ShareLock on transaction 6674; blocked by process 2496.
Process 2496: update a set value1=$1, parent_id=$2, value2=$3, name=$4, factor=$5, reg_date=$6, reg_ip=$7, reg_user=$8, remote_id=$9, a=$10, value=$11, status=$12 where id=$13
Process 1084: UPDATE a SET value= value- 0.11 WHERE id= 1026357
They not belong to same transaction (ok, I am 90% sure), they are on separate classes, executed in separate threads. Sometimes it's even more confusing, as the second query in deadlock is not updating same table, but another, and using table a just to fetch parent_id like:
update b set value = value+5 where id = (select parent_id from a where id = 123)
I just thinking, perhaps this can be case: sometimes those methods are invoked in one User Transaction - I got big xml to parse and update database and I put one UserTransaction.getTransaction.begin() to wrap all processing inside. But can it lock on table level, not row? And on all the tables I update in the transaction?