November 26, 2008 at 10:45 pm
Hi,
I have an application which is running from two terminals. If, the applications from two terminals updates the table in database simultaneously, one of the application is getting a message that ''
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
The two rows from different terminals doesn't have any Primary Key conflicts. Those two are completely different.
I tried the UPDATE statement with (ROWLOCK) also, but the result is same.
November 26, 2008 at 10:53 pm
thenewsview (11/26/2008)
Hi,I have an application which is running from two terminals. If, the applications from two terminals updates the table in database simultaneously, one of the application is getting a message that ''
com.microsoft.sqlserver.jdbc.SQLServerException: Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."The two rows from different terminals doesn't have any Primary Key conflicts. Those two are completely different.
I tried the UPDATE statement with (ROWLOCK) also, but the result is same.
Tmporarily you can use the following:
SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }
but for finding the deadlock cause 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.
To help identify which tables or stored procedures are causing deadlock problems, turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data).
DBCC TRACEON (3605,1204,-1)
Be sure to turn off this trace flag when you are done, as this trace can eat up SQL Server's resources unnecessarily, hurting performance.
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
November 26, 2008 at 11:08 pm
Hi Kumar,
Basically I am new to this SQL Server stuff...
I am using MSSQL Server 2005 Express. I turned on trace by using DBCC TRACEON (1024). But in the errorlog, I couldn't see any info on deadlocks.
And where can I find the SQL Profiler. I couldn't see any SQL Profiler in the Installation folders.
Thanks,
TNV
November 26, 2008 at 11:24 pm
TNV (11/26/2008)
Hi Kumar,Basically I am new to this SQL Server stuff...
I am using MSSQL Server 2005 Express. I turned on trace by using DBCC TRACEON (1024). But in the errorlog, I couldn't see any info on deadlocks.
And where can I find the SQL Profiler. I couldn't see any SQL Profiler in the Installation folders.
Thanks,
TNV
I don't remember if the default for trace flag 1024 output is the error log or not. I also don't remember if this trace flag applies to all connections, so I always run it with trace flags 3605 and -1:
dbcc traceon (2605, 1204, -1).
Profiler is a tool that comes when you install the server's client tool. I don't think that it comes with SQL Server Express, but if where you work there is also another edition of SQL Server, there is a good chance that you'll have profiler at your work place. The profiler can connect to SQL Server express edition. Notice that if you don’t have profiler and you’ll use the trace flags and the error log, most chances that you'll have to post the deadlock details that you'll get, because at the beginning it really looks cryptic
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 26, 2008 at 11:25 pm
TNV (11/26/2008)
Hi Kumar,Basically I am new to this SQL Server stuff...
I am using MSSQL Server 2005 Express. I turned on trace by using DBCC TRACEON (1024). But in the errorlog, I couldn't see any info on deadlocks.
And where can I find the SQL Profiler. I couldn't see any SQL Profiler in the Installation folders.
Thanks,
TNV
SQL express does not provide the Profiler.
Use SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }
and if you are maintaining any flag in the table first check whether the flag is updated
after the first tran and then request the second transaction
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 26, 2008 at 11:45 pm
krayknot (11/26/2008)
TNV (11/26/2008)
Hi Kumar,Basically I am new to this SQL Server stuff...
I am using MSSQL Server 2005 Express. I turned on trace by using DBCC TRACEON (1024). But in the errorlog, I couldn't see any info on deadlocks.
And where can I find the SQL Profiler. I couldn't see any SQL Profiler in the Installation folders.
Thanks,
TNV
SQL express does not provide the Profiler.
Use SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }
and if you are maintaining any flag in the table first check whether the flag is updated
after the first tran and then request the second transaction
I don’t think that setting deadlock priority would help at this scenario. You can use this setting if you know that you want to modify your application’s deadlock priority comparing to a different application. At this case he said that the same application is causing the deadlock when it runs from more then one workstation. In that case you’ll just end up with 2 processes that were had a deadlock and the same priority.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 27, 2008 at 12:57 am
Traceflag 1222 is preferred over 1204 on SQL 2005 as it provides a lot more info. 3605 is not required for logging deadlock info to the error log.
To get deadlock info, the traceflags have to be turned on globally.
DBCC TRACEON (1222) just turns it on for that session, which is useless for deadlocks, unless that session is involved in a deadlock.
use DBCC TRACEON (1222,-1) to turn the flag on globally.
When you get the deadlock graph, post it here. There are several people here that can read the deadlock graphs very easily.
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
November 27, 2008 at 12:59 am
krayknot (11/26/2008)
Use SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }
Why? What is that going to achieve?
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
November 27, 2008 at 1:38 am
GilaMonster (11/27/2008)
Traceflag 1222 is preferred over 1204 on SQL 2005 as it provides a lot more info. 3605 is not required for logging deadlock info to the error log.To get deadlock info, the traceflags have to be turned on globally.
DBCC TRACEON (1222) just turns it on for that session, which is useless for deadlocks, unless that session is involved in a deadlock.
use DBCC TRACEON (1222,-1) to turn the flag on globally.
When you get the deadlock graph, post it here. There are several people here that can read the deadlock graphs very easily.
I didn't know about the new trace flag. Thank you for sharing this info.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 27, 2008 at 4:37 am
Here is the graph from error log..
I have deleted the SQL after the SET keyword for convenience. Those SQLs are different and did not have any Primary Key conflicts..
2008-11-27 16:40:58.64 spid4s Wait-for graph
2008-11-27 16:40:58.64 spid4s
2008-11-27 16:40:58.64 spid4s Node:1
2008-11-27 16:40:58.64 spid4s KEY: 20:72057594044547072 (01016c1436f9) CleanCnt:3 Mode:X Flags: 0x0
2008-11-27 16:40:58.64 spid4s Grant List 0:
2008-11-27 16:40:58.64 spid4s Owner:0x036D5080 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0 XactLockInfo: 0x06157524
2008-11-27 16:40:58.64 spid4s SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 1
2008-11-27 16:40:58.64 spid4s Input Buf: Language Event: UPDATE SS.SECTIONS WITH(ROWLOCK) SET SECTNO=160 WHERE ROOMNO=401
2008-11-27 16:40:58.64 spid4s Requested By:
2008-11-27 16:40:58.64 spid4s ResType:LockOwner Stype:'OR'Xdes:0x065E2F60 Mode: U SPID:70 BatchID:0 ECID:0 TaskProxy:(0x065E4378) Value:0x36d4760 Cost:(0/688)
2008-11-27 16:40:58.64 spid4s
2008-11-27 16:40:58.64 spid4s Node:2
2008-11-27 16:40:58.64 spid4s KEY: 20:72057594044547072 (f400557bd7f9) CleanCnt:2 Mode:X Flags: 0x0
2008-11-27 16:40:58.64 spid4s Grant List 0:
2008-11-27 16:40:58.64 spid4s Owner:0x036D4EE0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:70 ECID:0 XactLockInfo: 0x065E2F84
2008-11-27 16:40:58.64 spid4s SPID: 70 ECID: 0 Statement Type: UPDATE Line #: 1
2008-11-27 16:40:58.64 spid4s Input Buf: Language Event: UPDATE SS.SECTIONS WITH(ROWLOCK) SET SECTNO=101 WHERE ROOMNO=601
2008-11-27 16:40:58.64 spid4s Requested By:
2008-11-27 16:40:58.64 spid4s ResType:LockOwner Stype:'OR'Xdes:0x06157500 Mode: U SPID:60 BatchID:0 ECID:0 TaskProxy:(0x07CEC378) Value:0x36d5600 Cost:(0/2860)
2008-11-27 16:40:58.64 spid14s deadlock-list
2008-11-27 16:40:58.64 spid14s deadlock victim=process73ba68
2008-11-27 16:40:58.64 spid14s process-list
2008-11-27 16:40:58.64 spid14s process id=process73b978 taskpriority=0 logused=2860 waitresource=KEY: 20:72057594044547072 (f400557bd7f9) waittime=3796 ownerId=155389 transactionname=user_transaction lasttranstarted=2008-11-27T16:38:46.340 XDES=0x6157500 lockMode=U schedulerid=1 kpid=3592 status=suspended spid=60 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2008-11-27T16:40:54.793 lastbatchcompleted=2008-11-27T16:40:54.780 clientapp=Microsoft SQL Server 2005 JDBC Driver hostname=SYS081 hostpid=0 loginname=XXXX isolationlevel=read committed (2) xactid=155389 currentdb=20 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2008-11-27 16:40:58.64 spid14s executionStack
2008-11-27 16:40:58.64 spid14s frame procname=adhoc line=1 stmtstart=1618 sqlhandle=0x0200000095b1480a2185b7bd9f9b6f62773c9912ac37d6d8
2008-11-27 16:40:58.64 spid14s UPDATE SS.EMPLOYEES WITH (ROWLOCK) SET
2008-11-27 16:40:58.64 spid14s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-27 16:40:58.64 spid14s unknown
2008-11-27 16:40:58.64 spid14s inputbuf
2008-11-27 16:40:58.64 spid14s UPDATE SS.SECTIONS WITH(ROWLOCK) SET SECTNO=160 WHERE ROOMNO=401
2008-11-27 16:40:58.64 spid14s process id=process73ba68 taskpriority=0 logused=688 waitresource=KEY: 20:72057594044547072 (01016c1436f9) waittime=3890 ownerId=160345 transactionname=implicit_transaction lasttranstarted=2008-11-27T16:40:54.670 XDES=0x65e2f60 lockMode=U schedulerid=1 kpid=3376 status=suspended spid=70 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2008-11-27T16:40:54.717 lastbatchcompleted=2008-11-27T16:40:54.700 hostname=sys30 hostpid=0 loginname=XXXX isolationlevel=read committed (2) xactid=160345 currentdb=20 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
2008-11-27 16:40:58.64 spid14s executionStack
2008-11-27 16:40:58.64 spid14s frame procname=adhoc line=1 stmtstart=1492 sqlhandle=0x020000003b42fd072929df72c166b1166887b48e7f3a1ac5
2008-11-27 16:40:58.64 spid14s UPDATE SS.EMPLOYEES WITH (ROWLOCK) SET
2008-11-27 16:40:58.64 spid14s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2008-11-27 16:40:58.64 spid14s unknown
2008-11-27 16:40:58.64 spid14s inputbuf
2008-11-27 16:40:58.64 spid14s UPDATE SS.SECTIONS WITH(ROWLOCK) SET SECTNO=101 WHERE ROOMNO=601
2008-11-27 16:40:58.64 spid14s resource-list
2008-11-27 16:40:58.64 spid14s keylock hobtid=72057594044547072 dbid=20 objectname=EMPMSQL.SS.EMPLOYEES indexname=IDX_EMP id=lock3688cc0 mode=X associatedObjectId=72057594044547072
2008-11-27 16:40:58.64 spid14s owner-list
2008-11-27 16:40:58.64 spid14s owner id=process73b978 mode=X
2008-11-27 16:40:58.64 spid14s waiter-list
2008-11-27 16:40:58.64 spid14s waiter id=process73ba68 mode=U requestType=wait
2008-11-27 16:40:58.64 spid14s keylock hobtid=72057594044547072 dbid=20 objectname=EMPMSQL.SS.EMPLOYEES indexname=IDX_EMP id=lock3689480 mode=X associatedObjectId=72057594044547072
2008-11-27 16:40:58.64 spid14s owner-list
2008-11-27 16:40:58.64 spid14s owner id=process73ba68 mode=X
2008-11-27 16:40:58.64 spid14s waiter-list
2008-11-27 16:40:58.64 spid14s waiter id=process73b978 mode=U requestType=wait
2008-11-27 16:40:58.64 spid4s
2008-11-27 16:40:58.64 spid4s Victim Resource Owner:
2008-11-27 16:40:58.64 spid4s ResType:LockOwner Stype:'OR'Xdes:0x065E2F60 Mode: U SPID:70 BatchID:0 ECID:0 TaskProxy:(0x065E4378) Value:0x36d4760 Cost:(0/688)
November 27, 2008 at 4:40 am
If I use TABLOCKX instead of ROWLOCK, the applications in both the systems are getting non-responsive and there was no deadlock graph in ERRORLOG too...
November 27, 2008 at 6:20 am
UPDATE SS.SECTIONS WITH(ROWLOCK,HOLDLOCK) SET SECTNO=101 WHERE ROOMNO=601
This will indicate to the SQL engine that it must hold the lock on the rows it is updating till it has completed that transaction. The other queries will have to wait to access the data until the lock is released.
November 28, 2008 at 12:01 am
chris (11/27/2008)
UPDATE SS.SECTIONS WITH(ROWLOCK,HOLDLOCK) SET SECTNO=101 WHERE ROOMNO=601This will indicate to the SQL engine that it must hold the lock on the rows it is updating till it has completed that transaction. The other queries will have to wait to access the data until the lock is released.
When ever you run an insert/update/delete statement as a part of transaction, the locks are held until the end of the whole transaction and not until the end of the statement, so adding the holdlock hint, will not change the locking behavior.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 28, 2008 at 12:04 am
Might be wrong here but it seems that those 2 statements caused the deadlock:
UPDATE SS.SECTIONS WITH(ROWLOCK) SET SECTNO=160 WHERE ROOMNO=401
UPDATE SS.SECTIONS WITH(ROWLOCK) SET SECTNO=101 WHERE ROOMNO=601
Do you have an index on column ROOMNO? If there isn’t any can you try adding one and see if it helps? Are there many rows with the same roomno?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 28, 2008 at 1:16 pm
You might want to look at the table and see if there is a trigger on it that does some other update.
Todd Fifield
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply