?
Solved

deadlock in same transaction? (URGENT)

Posted on 2005-05-01
5
Medium Priority
?
1,199 Views
Last Modified: 2011-08-18
I apparently have a deadlock right now. The confusing thing is that all the rows with granted=false in pg_locks are all from the same transaction.

Here are the first 101 rows (plus two rows from farther down the results) from the following query:
select relation,database,transaction,pid,mode,(select CASE granted WHEN true THEN '' ELSE 'FALSE' END) from pg_locks order by pid,relation,granted desc;

RELATION  DATABASE TRANSACTION PID MODE GRANTED
17402   17253           208     "AccessShareLock"       ""
17457   17253           208     "AccessShareLock"       ""
17473   17253           208     "AccessShareLock"       ""
17494   17253           208     "RowExclusiveLock"      ""
17494   17253           208     "AccessShareLock"       ""
115096  17253           208     "AccessShareLock"       ""
146526  17253           208     "AccessShareLock"       ""
146538  17253           208     "AccessShareLock"       ""
319229  17253           208     "AccessShareLock"       ""
                1321016 208     "ExclusiveLock" ""
17402   17253           388     "AccessShareLock"       ""
17457   17253           388     "AccessShareLock"       ""
17473   17253           388     "AccessShareLock"       ""
17494   17253           388     "RowExclusiveLock"      ""
17494   17253           388     "AccessShareLock"       ""
17497   17253           388     "AccessShareLock"       ""
17497   17253           388     "RowExclusiveLock"      ""
17499   17253           388     "RowExclusiveLock"      ""
17501   17253           388     "RowExclusiveLock"      ""
115096  17253           388     "AccessShareLock"       ""
314707  17253           388     "RowExclusiveLock"      ""
                1322580 388     "ExclusiveLock" ""
                1321016 388     "ShareLock"             "FALSE"
17402   17253           596     "AccessShareLock"       ""
17457   17253           596     "AccessShareLock"       ""
17473   17253           596     "AccessShareLock"       ""
17494   17253           596     "RowExclusiveLock"      ""
17494   17253           596     "AccessShareLock"       ""
17497   17253           596     "RowExclusiveLock"      ""
17497   17253           596     "AccessShareLock"       ""
17499   17253           596     "RowExclusiveLock"      ""
17501   17253           596     "RowExclusiveLock"      ""
115096  17253           596     "AccessShareLock"       ""
314707  17253           596     "RowExclusiveLock"      ""
                1321748 596     "ExclusiveLock" ""
                1321016 596     "ShareLock"             "FALSE"
17402   17253           608     "AccessShareLock"       ""
17457   17253           608     "AccessShareLock"       ""
17473   17253           608     "AccessShareLock"       ""
17494   17253           608     "RowExclusiveLock"      ""
17494   17253           608     "AccessShareLock"       ""
17497   17253           608     "AccessShareLock"       ""
17497   17253           608     "RowExclusiveLock"      ""
17499   17253           608     "RowExclusiveLock"      ""
17501   17253           608     "RowExclusiveLock"      ""
115096  17253           608     "AccessShareLock"       ""
314707  17253           608     "RowExclusiveLock"      ""
                1321731 608     "ExclusiveLock" ""
                1321016 608     "ShareLock"             "FALSE"
17402   17253           1340    "AccessShareLock"       ""
17457   17253           1340    "AccessShareLock"       ""
17473   17253           1340    "AccessShareLock"       ""
17494   17253           1340    "RowExclusiveLock"      ""
17494   17253           1340    "AccessShareLock"       ""
17497   17253           1340    "AccessShareLock"       ""
17497   17253           1340    "RowExclusiveLock"      ""
17499   17253           1340    "RowExclusiveLock"      ""
17501   17253           1340    "RowExclusiveLock"      ""
115096  17253           1340    "AccessShareLock"       ""
314707  17253           1340    "RowExclusiveLock"      ""
                1323098 1340    "ExclusiveLock" ""
                1321016 1340    "ShareLock"             "FALSE"
17402   17253           1748    "AccessShareLock"       ""
17457   17253           1748    "AccessShareLock"       ""
17473   17253           1748    "AccessShareLock"       ""
17494   17253           1748    "AccessShareLock"       ""
17494   17253           1748    "RowExclusiveLock"      ""
17497   17253           1748    "AccessShareLock"       ""
17497   17253           1748    "RowExclusiveLock"      ""
17499   17253           1748    "RowExclusiveLock"      ""
17501   17253           1748    "RowExclusiveLock"      ""
115096  17253           1748    "AccessShareLock"       ""
314707  17253           1748    "RowExclusiveLock"      ""
                1321783 1748    "ExclusiveLock" ""
                1321016 1748    "ShareLock"             "FALSE"
17402   17253           2120    "AccessShareLock"       ""
17457   17253           2120    "AccessShareLock"       ""
17473   17253           2120    "AccessShareLock"       ""
17494   17253           2120    "RowExclusiveLock"      ""
17494   17253           2120    "AccessShareLock"       ""
17497   17253           2120    "AccessShareLock"       ""
17497   17253           2120    "RowExclusiveLock"      ""
17499   17253           2120    "RowExclusiveLock"      ""
17501   17253           2120    "RowExclusiveLock"      ""
115096  17253           2120    "AccessShareLock"       ""
314707  17253           2120    "RowExclusiveLock"      ""
                1322571 2120    "ExclusiveLock" ""
                1321016 2120    "ShareLock"             "FALSE"
17402   17253           2280    "AccessShareLock"       ""
17457   17253           2280    "AccessShareLock"       ""
17473   17253           2280    "AccessShareLock"       ""
17494   17253           2280    "RowExclusiveLock"      ""
17494   17253           2280    "AccessShareLock"       ""
17497   17253           2280    "AccessShareLock"       ""
17497   17253           2280    "RowExclusiveLock"      ""
17499   17253           2280    "RowExclusiveLock"      ""
17501   17253           2280    "RowExclusiveLock"      ""
115096  17253           2280    "AccessShareLock"       ""
314707  17253           2280    "RowExclusiveLock"      ""
                1322479 2280    "ExclusiveLock" ""
                1321016 2280    "ShareLock"             "FALSE"
16839    17253            5656      "AccessShareLock"      ""
            1328908      5656      "ExclusiveLock"      ""


Here are the corresponding rows from the pg_stat_activity table:

DATID  DATNAME  PROCPID  USESYSID  USENAME  CURRENT_QUERY  QUERY_START
17253      "KnowlistDevPGS"      208      1      "root"      "<IDLE> in transaction"      "2005-05-01 18:46:43.453-07"
17253      "KnowlistDevPGS"      388      1      "root"      "update know.customobjfieldmeta set visibility=$1, versionId=$2, objType=$3, versionOf=$4, shortDesc=$5, longDesc=$6, fieldType=$7 where id=$8"      "2005-05-01 19:59:54.015-07"
17253      "KnowlistDevPGS"      596      1      "root"      "update know.customobjfieldmeta set visibility=$1, versionId=$2, objType=$3, versionOf=$4, shortDesc=$5, longDesc=$6, fieldType=$7 where id=$8"      "2005-05-01 19:28:03.453-07"
17253      "KnowlistDevPGS"      608      1      "root"      "update know.customobjfieldmeta set visibility=$1, versionId=$2, objType=$3, versionOf=$4, shortDesc=$5, longDesc=$6, fieldType=$7 where id=$8"      "2005-05-01 19:25:16.062-07"
17253      "KnowlistDevPGS"      1340      1      "root"      "update know.customobjfieldmeta set visibility=$1, versionId=$2, objType=$3, versionOf=$4, shortDesc=$5, longDesc=$6, fieldType=$7 where id=$8"      "2005-05-01 21:09:04.218-07"
17253      "KnowlistDevPGS"      1748      1      "root"      "update know.customobjfieldmeta set visibility=$1, versionId=$2, objType=$3, versionOf=$4, shortDesc=$5, longDesc=$6, fieldType=$7 where id=$8"      "2005-05-01 19:34:01.171-07"
17253      "KnowlistDevPGS"      2120      1      "root"      "update know.customobjfieldmeta set visibility=$1, versionId=$2, objType=$3, versionOf=$4, shortDesc=$5, longDesc=$6, fieldType=$7 where id=$8"      "2005-05-01 19:57:00.515-07"
17253      "KnowlistDevPGS"      2280      1      "root"      "update know.customobjfieldmeta set visibility=$1, versionId=$2, objType=$3, versionOf=$4, shortDesc=$5, longDesc=$6, fieldType=$7 where id=$8"      "2005-05-01 19:50:32.968-07"
17253      "KnowlistDevPGS"      5656      1      "root"      "<IDLE>"      "2005-05-01 21:33:20.937-07"



Notice how all the PIDs have 13 rows except for 208 which has only 10 and 5656 which has only two. All of the 13 row processes have a ShareLock from transaction 1321016 which has granted=FALSE.

There are a bunch more processes with 13 rows, but they all follow the same pattern as the ones I've listed.

So, could someone tell me what's going on here? What does it mean to have a lot of ungranted ShareLocks which all have the same transaction xid? Is this actually a deadlock or is it something else?


This is on my dev server, so I don't want to restart the webapp or kill the deadlocked processes or anything like that until I figure this out. (That's why I put URGENT in the question title. I can't do anything else until I figure this out.)
0
Comment
Question by:HappyEngineer
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 13908165
what steps are you performing to get into this state ?
0
 

Author Comment

by:HappyEngineer
ID: 13911275
I don't have a way to reproduce it. That's why I haven't restarted my dev webapp once it got in this state.

My public server seems to have the same problem every few days, but I don't know how it happens. I just restart my webapp and all the locks are released and the server runs fine again for another few days.

Obviously, a bunch of updates are attempted to the customobjfieldmeta table. I'm using Hibernate and apparently once an update needs to be saved to the DB, every view of a particular page causes another process to wait on that same lock in the DB.

Is the info in my initial question not enough to determine what is happening?
0
 

Author Comment

by:HappyEngineer
ID: 13913441
The most important thing I need to know is:
  Is this a postgres issue or an application issue? I've read that postgres has some internal deadlock issues regarding indexes among other things. I want to know if I'm running into one of those issues or if the problem is an application issue caused by Hibernate or just plain bad application logic on my part.
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 1000 total points
ID: 13913572
I would guess this is a Hibernate issue.  Or more exactly a Postgres - Hibernate issue.
0
 

Author Comment

by:HappyEngineer
ID: 13952105
I figured out the cause of the unexpected updates. I'll have to do tests to determine if updates cause deadlocks when they're done on purpose by my own code.

See:
  http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_21410535.html
for the answer to why unexpected updates were occurring.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

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…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

840 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