Understanding the output from trace flag 1204 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Understanding the output from trace flag 1204

Hello,<br /><br /> I have two sql statements that are creating a deadlock. The voulme of data I am dealing with currently is around 20GB, around 40 million records.<br /><br />I would appreciate it if some one could direct me or help me interpret the trace flag 1204 information, which I have pasted below.<br /><br />Thank you,<br />Sainath<br /><hr noshade size="1"><br /><br />Deadlock encountered …. Printing deadlock information<br />2004-04-26 13:54:08.80 spid4 <br />2004-04-26 13:54:08.80 spid4 Wait-for graph<br />2004-04-26 13:54:08.80 spid4 <br />2004-04-26 13:54:08.80 spid4 Node:1<br />2004-04-26 13:54:08.80 spid4 RID: 7:1:1075272:40 CleanCnt:1 Mode: U Flags: 0x2<br />2004-04-26 13:54:08.80 spid4 Grant List 0::<br />2004-04-26 13:54:08.80 spid4 Owner:0x78f1b5a0 Mode: U Flg:0x0 Ref:0 Life:02000000 SPID:57 ECID:0<br />2004-04-26 13:54:08.80 spid4 SPID: 57 ECID: 0 Statement Type: SELECT Line #: 1<br />2004-04-26 13:54:08.80 spid4 Input Buf: Language Event: Select Max(DateEnd) From AvgReadings (UPDLOCK)<br />Where AvgReadings.DateStart &gt;= ‘4/1/2003′<br />And AvgReadings.DateEnd &lt;= ‘4/30/2003′<br />And Project = 22<br />And FileTypeMasterId = 26<br /><br /><br />2004-04-26 13:54:08.80 spid4 Requested By: <br />2004-04-26 13:54:08.80 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:51 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />0x4D90D510) Value:0x785a9e00 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />0/0)<br />2004-04-26 13:54:08.80 spid4 <br />2004-04-26 13:54:08.80 spid4 Node:2<br />2004-04-26 13:54:08.80 spid4 RID: 7:1:435290:0 CleanCnt:1 Mode: U Flags: 0x2<br />2004-04-26 13:54:08.80 spid4 Grant List 0::<br />2004-04-26 13:54:08.80 spid4 Owner:0x786b7c80 Mode: U Flg:0x0 Ref:0 Life:02000000 SPID:51 ECID:0<br />2004-04-26 13:54:08.80 spid4 SPID: 51 ECID: 0 Statement Type: SELECT Line #: 2<br />2004-04-26 13:54:08.80 spid4 Input Buf: Language Event: <br />Selecttop 5000 Avgr.Channel,FTMP.Standard,Avgr.DateStart,Avgr.DateEnd,Avgr.Project,Avgr.FileTypeMasterId,Avgr.Mid<br />FromFileTypeMapping FTMP (updlock), AvgReadings Avgr (updlock)<br />WhereAvgr.Channel = FTMP.Mapped<br />And Avgr.FileTypeMasterId = FTMP.FileT<br />2004-04-26 13:54:08.80 spid4 Requested By: <br />2004-04-26 13:54:08.80 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:57 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />0x278D3510) Value:0x6d114580 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />0/0)<br />2004-04-26 13:54:08.80 spid4 Victim Resource Owner:<br />2004-04-26 13:54:08.80 spid4 ResType:LockOwner Stype:’OR’ Mode: U SPID:57 ECID:0 Ec<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />0x278D3510) Value:0x6d114580 Cost<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />0/0)<br />
it looks to me like someone coded your select statements to use updlock From BOL
Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
So,this means that others can read the data, but the connection that requested the updlock blocks anyone else from updating the data. I’d question the users need to run thiw with updlock. If its necessary, then you need to live with it and schedule them so that there arent more than one statment requesting these lock types.
Hi Chris, I did not understand your comment. The code has been written by me. I am using
updlock in both the statements. So, both the statements should work, but they deadlock. The deadlock occurs intermittently. I was wondering if some could explain the trace log. Thanks

What SQL and SP do you have? Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Hi Luis, I am using SQL server standard edition, with 2GB ram, dual processor 1.3Ghz The sql statements are: 1.
Select Max(DateEnd) From AvgReadings (UPDLOCK)
Where AvgReadings.DateStart >= ‘4/1/2003’
And AvgReadings.DateEnd <= ‘4/30/2003’
And Project = 22
And FileTypeMasterId = 26 2.
Selecttop 5000 Avgr.Channel,FTMP.Standard,Avgr.DateStart,Avgr.DateEnd,Avgr.Project,Avgr.FileTypeMasterId,Avgr.Mid
FromFileTypeMapping FTMP (updlock), AvgReadings Avgr (updlock)
WhereAvgr.Channel = FTMP.Mapped
And Avgr.FileTypeMasterId = FTMP.FileTypeMasterId
And avgr.filetypemasterid=26
And avgr.dateStart > ‘1/1/2000’
And Not exists
(
Select datestart
FromDalRecords DR (updlock)
Where DR.FileTypeMasterId = 26
And DR.DateTime = Avgr.DateStart
)
And Not Exists
(
Select datestart
From MMBTUReportData RD (updlock)
Where rd.dateend = avgr.dateend
and rd.FiletypeMasterId = 26
–RD.datestart = avgr.datestart
) Software Developer
(MCSD)
This trace flag returns the type of locks participating in a deadlock and the current command affected. This trace flag was documented in the SQL Server 7.0 Books Online, but not documented in the SQL Server 2000 Books Online. http://www.sql-server-performance.com/deadlocks.asp for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
So you have 2 queries that run at the same time and on occaision will both request an update lock on the same record(s) at the same time. It works the way I would expect it. Both queries want to get an exclusive update lock on the same data and of coures they cant. You need to either ensure that these queries wont run at the same time, or if they do, not update the same data.

Hello everyone, I have got a lot of suggestions. Thank you, but I stil need the queries
to run at the same time, since they are from two different modules, which my online users
access. The queries displayed are excatly they way it is. So I do not use them to update anything. They are basically select queries. The reason behind using the updlock, was that the whole set of records would be locked, till the whole query got completed. I guess i would have to remove the updlock, since each of the queries might wait for a section of code locked up by the other query. I stil wish I could understand the trace flag information. But I appreciate all your suggestions. Thank you
If you are getting all the records on the client side and not getting them page by page / using cursor, I think you should be fine without using UPDLOCK. This is beacuse whenever SQL Server reads a record, it puts a shared lock on the same. Having a shared lock on the record menas that the record can be read by other connection but it forbids updating the record. SO while you are reading the record, it is gauranteed that the record can not be updated. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
Maybe this link can shed some light on the Trace Flag. http://doc.ddart.net/mssql/sql2000/html/trblsql/tr_servdatabse_5xrn.htm
This may help: http://www.sql-server-performance.com/at_sql_locking.asp Same article suggested by Lazy_DBA:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp
]]>