October 21, 2008 at 1:45 am
I'm working on SQL server 2000 and I'm getting following error sometimes:
"Transaction (Process ID 69) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
In my application I'm accessing one table for read and write also. Sometimes if one user is updating a table and at the same time if other user tries to read data from that table I'm getting above error.
As there are large number of users lots of time this write /update and read deadlock error is coming.
How I can resolve this deadlock issue?
October 21, 2008 at 2:48 am
Kiran Kumar MP (10/21/2008)
I'm working on SQL server 2000 and I'm getting following error sometimes:"Transaction (Process ID 69) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
In my application I'm accessing one table for read and write also. Sometimes if one user is updating a table and at the same time if other user tries to read data from that table I'm getting above error.
As there are large number of users lots of time this write /update and read deadlock error is coming.
How I can resolve this deadlock issue?
First, use the SQL Server Profiler's Create Trace Wizard to run the "Identify The Cause of a Deadlock" trace. This will provide you with the raw data you need to help isolate the causes of deadlocks in your databases.
In your application, be sure to include program logic in your application to deal with killed deadlock transactions in a user-friendly way.
For example, let's say that two transactions are deadlocked and that SQL Server kills one of the transactions. In this case, SQL Server will raise an error message that your application needs to respond to. In most cases, you will want your application to wait a random amount of time after the deadlock in order to resubmit the killed transaction to SQL Server.
It is important that there is a random waiting period because it is possible that another contending transaction could also be waiting, and you don't want both contending transactions to wait the same amount of time and then both try to execute at the same time, causing another deadlock
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 21, 2008 at 2:51 am
First of all : Shorten your transactions.
If your client opens a connection,
reads some data ie that connection,
waits for the user to enter changes or new records,
writes back the data to sql server
and then some time later closes the connection
(and with that commits the transaction)
then the lifetime of the transactions is way to long.
So shorten transactions.
Second: if you're accesing multiple objects (tables/views)
during the act of updating / inserting data,
allways access them in the same order.
devloping robust and performant databaseapplications with Microsoft SQL-Server
October 21, 2008 at 4:09 am
Enable traceflag 1204 (DBCC TRACEON (1204,-1) or add -T1204 to SQL's startup parameters). That will write the deadlock graph into the error log
The deadlock graph will show you exactly what processes were involved in the deadlock and what resources they were deadlocked over. With that info, you can fix the cause.
Post the deadlock graph here if you need some help interpreting it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2008 at 4:18 am
Hi,
You need t enable 2 traces:
DBCC TRACEON (3604)
DBCC TRACEON (1204)
and
read the article
Tracing Deadlocks
By Shiv Kumar,
@
http://qa.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/tracingdeadlocks/1324/
October 21, 2008 at 4:24 am
one more:
How to resolve a deadlock
http://support.microsoft.com/kb/832524
---
After you trace deadlock and get idea what is running (who is victim)
you will probably wish to add indexes and try to use NOLOCK hint
or\and to use SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }
http://www.sql-server-performance.com/tips/deadlocks_p1.aspx
October 21, 2008 at 8:29 am
To quickly identify locks use Query Analyser and sp_who or sp_who2
If you have a couple of tables central to the transactions of a couple of users, then the chances are you'll lock each other out.
When you identify the SPID's, trace it back to the source.
October 21, 2008 at 8:36 am
Ian Ritchie (10/21/2008)
To quickly identify locks use Query Analyser and sp_who or sp_who2If you have a couple of tables central to the transactions of a couple of users, then the chances are you'll lock each other out.
When you identify the SPID's, trace it back to the source.
You can run sp_lock at the same time to see the objects involved
October 21, 2008 at 8:38 am
Eugene (10/21/2008)
Hi,You need t enable 2 traces:
DBCC TRACEON (3604)
DBCC TRACEON (1204)
1204 alone is enough. 3604 is used to direct output of some commands to the client. Good example is DBCC page.
1204, by itself, will write the deadlock graph into the error log
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2008 at 9:07 am
Eugene (10/21/2008)
After you trace deadlock and get idea what is running (who is victim)you will probably wish to add indexes and try to use NOLOCK hint
I would suggest, once you've found the cause, fix the code and tweak the indexes first, and only as a last resort use nolock.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2008 at 10:54 pm
Thanks to All I got it.
October 21, 2008 at 10:55 pm
Thanks to All
I got it.
Kiran
October 22, 2008 at 10:32 am
GilaMonster :
You need set 1204 in combination with the 3605 flag:
The output of the flag will be sent to the SQL Server error log whenever SQL Server encounters a deadlock.
Note This way you can gather information about the deadlocks immediately. The "-1" indicates all SPIDs.
dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go
October 22, 2008 at 10:35 am
Kiran,
There are basically four steps you can use to resolve deadlocks. These are:
Remove incompatible lock requests
Change the timing of transactions
Change the order of resource requests
Change the isolation level
more:
http://www.code-magazine.com/article.aspx?quickid=0309101&page=4
October 22, 2008 at 12:20 pm
Eugene (10/22/2008)
GilaMonster :You need set 1204 in combination with the 3605 flag:
The output of the flag will be sent to the SQL Server error log whenever SQL Server encounters a deadlock.
Despite what the kb article says, 1204 alone can, will and does (at least on every server I've ever used it on from 2000 to 2008) write the deadlock graph into the error log.
On my 2008 server (I don't have a 2000 one handy right now)
dbcc tracestatus(-1) -- returns nothing, so neither 3604 nor 3605 is enabled.
dbcc traceon (1204,-1)
CREATE TABLE Test1 (id int)
CREATE TABLE Test2 (id int)
Then force a deadlock. Not hard to do.
In one window
BEGIN TRANSACTION
Insert into Test1 Values (1)
waitfor delay '00:00:20'
select * from Test2
and in a second window
BEGIN TRANSACTION
Insert into Test2 Values (1)
waitfor delay '00:00:20'
select * from Test1
20 seconds later:
Msg 1205, Level 13, State 45, Line 6
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
and in my error log: (slightly trimmed for space reasons)
DBCC TRACEON 1204, server process ID (SPID) 53. This is an informational message only; no user action is required.
Deadlock encountered .... Printing deadlock information
Wait-for graph
Node:1
RID: 12:1:3783:0 CleanCnt:2 Mode:X Flags: 0x3
Grant List 2:
Owner:0x00000000845CA180 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:51
SPID: 51 ECID: 0 Statement Type: SELECT Line #: 6
Input Buf: Language Event: BEGIN TRANSACTION
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000083844E90 Mode: S SPID:52
Node:2
RID: 12:1:3503:0 CleanCnt:2 Mode:X Flags: 0x3
Grant List 3:
Owner:0x0000000080177DC0 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:52
SPID: 52 ECID: 0 Statement Type: SELECT Line #: 6
Input Buf: Language Event: BEGIN TRANSACTION
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000088B48E90 Mode: S SPID:51
Victim Resource Owner:
ResType:LockOwner Stype:'OR'Xdes:0x0000000083844E90 Mode: S SPID:52
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply