szczecin
asked on
Why it is an deadlock?
hi,
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.getTransac
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.getTransac
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can avoid the deadlock in the example in several ways:
Release all locks held before switching contexts.
Change the code so that context 1 performs its commit before switching to context 2.
Do not access a given object from more than one context at a time.
Change the code so that both the update and the select are done from the same context.
Set the locktimeout database configuration parameter to a value other than -1.
While a value other than -1 will not prevent the deadlock, it will allow execution to resume. Context 2 is eventually rolled back because it is unable to obtain the requested lock. When context 2 is rolled back, context 1 can continue executing (which releases the locks) and context 2 can retry its work.