Solved

Why it is an deadlock?

Posted on 2011-09-19
2
374 Views
Last Modified: 2012-05-12
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.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?


0
Comment
Question by:szczecin
2 Comments
 
LVL 14

Expert Comment

by:nishant joshi
ID: 36562207
A deadlock is a situation where in two or more competing actions are each waiting for the other to finish, and thus neither ever does.


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.


0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 36563157
The link below explains it all AS IS under the "Table-level lock modes"and you could also write your own code easily to see how it works - but CAUTION do not do it in PROD servers.


http://www.postgresql.org/docs/9.0/static/explicit-locking.html
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question