Solved

Why it is an deadlock?

Posted on 2011-09-19
2
376 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
put constant from select to insert into field  Posgresql 2 121
HSSFWorkbook cannot be resolved error 10 140
custom annotations 9 46
jsp insert to database example 2 59
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…
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.

740 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