Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Why it is an deadlock?

Posted on 2011-09-19
2
Medium Priority
?
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A solution for Fortify Path Manipulation.
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.

704 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