Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Why it is an deadlock?

Posted on 2011-09-19
2
Medium Priority
?
382 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 40

Accepted Solution

by:
lcohan earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
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…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses

963 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