Solved

Why it is an deadlock?

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
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…
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.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now