unable to avoid the deadlock - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

unable to avoid the deadlock

Author

Topic

swethar
Starting Member

2 Posts

Posted - 2007-10-11 : 21:43:03

Hi,

Please help with the below deadlock problem :

We are using SQL Server 2000. Our application is a J2EE based. We use Java v 1.5 ,EJB 3.0 ,JBoss-4.2.0 and have sqljdbc.jar v 1.1 driver (to connect to sql server from jboss).

We have a JMS Queue which is unloading a java.util.List object at a time. This List has 5 value objects.

In the session bean I iterate through this list and do the following :

while(importListPeople.hasNext()){
Person value = (Person)importListPeople.next();

//select statement to check if the Person already exists in DB
Person prsn =entityManager.query("SELECT from Person where...");

if(prsn !=null){ //do an update if person already exists

// update person record
// update PersonAV table
// do an insert into personIndexingQueue
// increment the person update counter.

}else{//do a create

entityManager.persist(value);
//insert into personAv table
//do an insert into personIndexingQueue
//increment the person create counter.
}

}

I get the below error when we are running the application
13:04:41,904 WARN [JDBCExceptionReporter] SQL Error: 1205, SQLState: 40001
13:04:41,904 ERROR [JDBCExceptionReporter] Transaction (Process ID 63) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

For log ,sqlprofiler I could narrow down that the deadlock is occurring on the below select statement in the code.

//select statement to check if the Person already exists in DB
Person prsn =entityManager.query("SELECT from Person where...");

This code works if there is only one object in the list.

The above code works on mysql with 20 objects in the list.

Please help.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-10-12 : 01:42:42

Swetha,
you first go to your database and there check for locks are there on the database.

1)open the query analyzer and select that database
2)type the command "DBCC OPENTRAN" Then execute the command by pressing F5.
3) if you get an spid number there,
4) type "kill 480" and execute the command.(assume 480 is the number you find at spid...replace with the number)
then check your application...
5)please let me know wether it worked for you or not

Vinod
Even you learn 1%, Learn it with 100% confidence.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-12 : 01:46:01

quote:
Originally posted by sunsanvin

Swetha,
you first go to your database and there check for locks are there on the database.

1)open the query analyzer and select that database
2)type the command "DBCC OPENTRAN" Then execute the command by pressing F5.
3) if you get an spid number there,
4) type "kill 480" and execute the command.(assume 480 is the number you find at spid...replace with the number)
then check your application...
5)please let me know wether it worked for you or not

This will not help for deadlocks.

swethar, you'll need to follow the steps outlined in Books Online on how to troubleshoot deadlocking.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-10-12 : 02:28:26

"4) type "kill 480" and execute the command.(assume 480 is the number you find at spid...replace with the number)"

Personally I can't remember the last time I killed a process. I'd be very worried about whether that process was built to be able to rollback cleanly, or not

so at the very least I would want to check precisely what that process had done, was in the middle of doing, and was not going to finish doing if I killed it. Also how long it might take to roll back ...

Kristen

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-12 : 03:09:38

deadlocks can be tricky. One thing that has helped me in the past in debugging them are some traceflags you can set that will print out deadlock information to the sql logs. as Tara said, it's all in BOL.

elsasoft.org

swethar
Starting Member

2 Posts

Posted - 2007-10-14 : 20:41:05

Hi,

Thanks all.I had already detected my deadlock but from my code end couldnt figure out why a deadlock was occuring.

I fixed my problem by setting <transaction-isolation>TRANSACTION_READ_UNCOMMITTED</transaction-isolation>
in my Jboss Datasource.xml file.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-15 : 00:36:56

that's not really a fix, more of a band-aid. it means you are doing dirty reads, which is often not appropriate. particularly if you are in the financial industry.

also, it exposes you to new kinds of errors: "cannot continue nolock scan due to data movement"

discussed recently a bit in this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89668&whichpage=2

the error you may hit is demonstrated here:
http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx

elsasoft.org

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-15 : 01:13:08

See this: http://vyaskn.tripod.com/administration_faq.htm#q14 to understand how to capture deadlock information using trace flags as suggested by Jezemine.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources