Deadlock problems - 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

Deadlock problems

Author

Topic

sql_buddy
Starting Member

41 Posts

Posted - 2009-08-17 : 08:05:44

we are using sql 2005 and from few days we are getting a lot of deadlock problems
"Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction" what could be the most probable reason here
we are not using locking in our queries.
server settings are
maximum worker threads : 0
Maximum number of concurrent connections : 0
lock :0
Maximum degree of parrallelism :0
query wait :-1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 08:17:05

The SQL Server is itself settings locks when it needs to.

You have to investigate what caused the deadlock, such as

* bad-performing query
* lack of proper indexes
* resource-intensive queries

N 56°04'39.26"
E 12°55'05.63"

sql_buddy
Starting Member

41 Posts

Posted - 2009-08-17 : 08:35:10

how to find out that ?
any tool or other thing

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 09:00:01

You can use the Deadlock Monitor.
Or SQL Profiler to trace the database.

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-17 : 10:55:02

This link is very good for tracking down deadlocks.

http://weblogs.sqlteam.com/mladenp/archive/2008/05/21/SQL-Server-2005-Immediate-Deadlock-notifications.aspx

Peso:

What is Deadlock Monitor?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-17 : 10:57:25

It's a SQL Server 2008 feature.

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-17 : 11:02:37

Sounds like another tool I wish I had!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sql_buddy
Starting Member

41 Posts

Posted - 2009-08-18 : 01:15:11

i think one of my view is getting deadlock problem ,in that view we are doing inner join on same table 2 times to retreive
data stored in parent-child format.
is that could be the reason.
what is meant by resource-intensive queries

sql_buddy
Starting Member

41 Posts

Posted - 2009-08-19 : 02:15:59

what are the features for a query to be subjected to deadlock problem?
i have one view that is having deadlock problem ,but not finding the problem in that and how to resolve that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources