May 20, 2010 at 3:37 am
Hi frndz
i got deadlock info in my errorlog posted bellow
2010-05-20 14:57:42.770 spid6s Deadlock encountered .... Printing deadlock information
2010-05-20 14:57:42.770 spid6s Wait-for graph
2010-05-20 14:57:42.770 spid6s NULL
2010-05-20 14:57:42.770 spid6s Node:1
2010-05-20 14:57:42.770 spid6s OBJECT: 8:754101727:0 CleanCnt:4 Mode:Sch-M Flags: 0x0
2010-05-20 14:57:42.770 spid6s Grant List 1:
2010-05-20 14:57:42.770 spid6s Owner:0x0BDBDA20 Mode: Sch-M Flg:0x0 Ref:0 Life:20000000 SPID:189 ECID:0 XactLockInfo: 0x0BB8554C
2010-05-20 14:57:42.770 spid6s SPID: 189 ECID: 0 Statement Type: TRUNCATE TABLE Line #: 240
2010-05-20 14:57:42.770 spid6s Input Buf: Language Event: [SP_ClientBPS]
2010-05-20 14:57:42.770 spid6s Requested By:
2010-05-20 14:57:42.770 spid6s ResType:LockOwner Stype:'OR'Xdes:0x21623A30 Mode: Sch-S SPID:142 BatchID:0 ECID:0 TaskProxy:(0x1CB4A378) Value:0xbdbbe20 Cost:(0/0)
2010-05-20 14:57:42.770 spid6s NULL
2010-05-20 14:57:42.770 spid6s Node:2
2010-05-20 14:57:42.770 spid6s OBJECT: 8:754101727:3 CleanCnt:2 Mode:Sch-S Flags: 0x0
2010-05-20 14:57:42.770 spid6s Grant List 0:
2010-05-20 14:57:42.770 spid6s Owner:0x0BDBB640 Mode: Sch-S Flg:0x0 Ref:1 Life:00000000 SPID:140 ECID:0 XactLockInfo: 0x35D9490C
2010-05-20 14:57:42.770 spid6s SPID: 140 ECID: 0 Statement Type: SELECT Line #: 65
2010-05-20 14:57:42.770 spid6s Input Buf: RPC Event: Proc [Database Id = 8 Object Id = 2046630334]
2010-05-20 14:57:42.770 spid6s Requested By:
2010-05-20 14:57:42.770 spid6s ResType:LockOwner Stype:'OR'Xdes:0x0BB85528 Mode: Sch-M SPID:189 BatchID:0 ECID:0 TaskProxy:(0x352D0378) Value:0xbdb9360 Cost:(0/0)
2010-05-20 14:57:42.770 spid6s NULL
2010-05-20 14:57:42.770 spid6s Node:3
2010-05-20 14:57:42.770 spid6s OBJECT: 8:754101727:0 CleanCnt:4 Mode:Sch-M Flags: 0x0
2010-05-20 14:57:42.770 spid6s Wait List:
2010-05-20 14:57:42.770 spid6s Owner:0x0BDBBE20 Mode: Sch-S Flg:0x2 Ref:1 Life:00000000 SPID:142 ECID:0 XactLockInfo: 0x21623A54
2010-05-20 14:57:42.770 spid6s SPID: 142 ECID: 0 Statement Type: SELECT Line #: 65
2010-05-20 14:57:42.770 spid6s Input Buf: RPC Event: Proc [Database Id = 8 Object Id = 2046630334]
2010-05-20 14:57:42.770 spid6s Requested By:
2010-05-20 14:57:42.770 spid6s ResType:LockOwner Stype:'OR'Xdes:0x35D95CE0 Mode: Sch-S SPID:140 BatchID:0 ECID:0 TaskProxy:(0x25A52364) Value:0xbdbba80 Cost:(0/0)
2010-05-20 14:57:42.770 spid6s NULL
2010-05-20 14:57:42.770 spid6s Victim Resource Owner:
2010-05-20 14:57:42.770 spid6s ResType:LockOwner Stype:'OR'Xdes:0x35D95CE0 Mode: Sch-S SPID:140 BatchID:0 ECID:0 TaskProxy:(0x25A52364) Value:0xbdbba80 Cost:(0/0)
(95 row(s) affected)
in this Truncate table statement deadlocking select statement
we have already used nolock option in select statement
ur suggestions would be appriciated
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 20, 2010 at 4:57 am
1204 is not the best traceflag to use on SQL 2005 or higher.
Switch traceflag 1222 on. That will result in a more detailed deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
It's blocking on Sch-S and Sch-M locks. Schema stability and schema modification. Those locks are always taken, regardless of the isolation level. It's to prevent the structure of the table changing while the select is in process, which would be really nasty.
What's the definition of the following?
SP_ClientBPS
The procedure with an ID of 2046630334 in database 8
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
May 20, 2010 at 6:20 am
hiii Gail,
tomorrow i will post the O\P for 1222 trace flag ,since application is closed for the day.
till that time i will give the def. of sps SP_ClientBPS & ID 2046630334 (i.e.Sp_IEActiveScreen) attached here.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 20, 2010 at 3:12 pm
sanketahir1985 (5/20/2010)
Hi frndz2010-05-20 14:57:42.770 spid6s SPID: 189 ECID: 0 Statement Type: TRUNCATE TABLE Line #: 240
.....
.....
2010-05-20 14:57:42.770 spid6s SPID: 140 ECID: 0 Statement Type: SELECT Line #: 65
2010-05-20 14:57:42.770 spid6s Input Buf: RPC Event: Proc [Database Id = 8 Object Id = 2046630334]
.....
Your TRUNCATE TABLE on ClientBPS in the stored proc SP_ClientBPS is conflicting with the SELECT in the the stored proc Sp_IEActiveScreen in the section if(@GroupBy='ClientCode')....
You need to establish some way of ensuring these two stored procs don't run at the same time OR change the truncate and use MERGE join, or write your own DELETE/UPDATE/INSERT.
TRUNCATE is very efficient, but possibly not a good idea in this situation. I would be interested to know if removing the "with (nolock)" condition on the select doesn't solve this. Without the nolock, you may get blocking rather than a deadlock. Could be worth trying.
If you want a nice fast way of replacing existing tables like this you can do some tricks, like have 2 tables and a view. The stored proc inserts into the empty table and when done updates the view to point to the refreshed table, then truncates the old table.
Leo
Striving to provide a better service.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 20, 2010 at 11:17 pm
Leo is spot on. Truncating a permanent table that's "in service" will always cause some sort of a problem in the future.
In SQL Server 2005 and up, there's a pretty neat way to get around this. Have a ClientBPS1 and a ClientBPS2 table. While one is active, you can do whatever you want to the other with impunity. For example, ClientBPS1 could be "in service" while your code is working on ClientBPS2. You would have a "Synonym" called ClientBPS (so, no code changes required in most places) instead of a table called ClientBPS. Initially point it to the "in service" ClientBPS1 table. Once ClientBPS2 is correctly populated, just repoint the ClientBPS synonym to the ClientBPS2 table which is much faster than the combination of truncating AND populating a table. Next time around, just do the opposite.
As a side bar, it's a very bad thing to name your stored procedures starting with "SP_" because all calls to such stored procedures using a 1 or 2 part naming convention will first make a trip to the MASTER database to see if the sp lives there first.
As a second side bar, lines of code that are 285 characters wide are a wee bit difficult to read. My recommendation would be to never exceed 119 characters (max number of characters that can be printed in landscape mode with 10pt courier font (12 pitch) with half inch margins) and to adopt a code formatting standard. It also makes code fit on most screens without having to do horizontal scrolls. Heh... or not... you folks are the ones that have to read/troubleshoot it. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2010 at 12:07 am
hiii all
i m not getting this work around, since i m not a T-sql developer,i will talk to development team ,
but let me tell u my scenario
actually SP_ClientBPS runs after every 5 sec. thru console program
while Sp_IEActiveScreen gets fired randomly by users avg freq is 5-10 hits per sec
just confirm me whether ur work around will work in this scenario or not?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 21, 2010 at 12:15 am
Heh... does your stored procedure take less than 5 seconds to run? If it does, then yes, either Leo's pass-through view or my synyonm swap should do the trick just fine. Well, except on Tuesday's when it's raining in Rhode Island and the Monk fish aren't biting and there's a red car in the inboard Northbound lane doing 60 miles an hour at mile marker #5. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2010 at 1:14 am
it depends on amount of data
but developers have taken care of that thing ,they have used some threading concept in which it checks for thread if it is busy then other query won't get fired (this is applicable to sp_clientBPS)
here the case is some times select statement blocks Truncate statement & it takes around 40-42 sec.
to execute
shud i work on that select statement?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 21, 2010 at 1:49 am
GilaMonster (5/20/2010)
1204 is not the best traceflag to use on SQL 2005 or higher.Switch traceflag 1222 on. That will result in a more detailed deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)It's blocking on Sch-S and Sch-M locks. Schema stability and schema modification. Those locks are always taken, regardless of the isolation level. It's to prevent the structure of the table changing while the select is in process, which would be really nasty.
What's the definition of the following?
SP_ClientBPS
The procedure with an ID of 2046630334 in database 8
Hii gail
here is the output for trace flag 1222
deadlock-list
2010-05-21 12:54:35.540 spid18s deadlock victim=processf604d8
2010-05-21 12:54:35.540 spid18s process-list
2010-05-21 12:54:35.540 spid18s process id=processcb4a78 taskpriority=0 logused=0 waitresource=OBJECT: 8:754101727:2 waittime=3031 ownerId=88010588 transactionname=Lookup lasttranstarted=2010-05-21T12:54:32.483 XDES=0x32ea63c8 lockMode=Sch-S schedulerid=5 kpid=6904 status=suspended s
2010-05-21 12:54:35.540 spid18s executionStack
2010-05-21 12:54:35.540 spid18s frame procname=OnlineMTMUAT.dbo.Sp_IEActiveScreen line=65 stmtstart=4214 stmtend=6268 sqlhandle=0x03000800be19fd793a6e9c00609d00000100000000000000
2010-05-21 12:54:35.540 spid18s select a.clientcode as [Client Code],a.Symbol,a.symbolcode,scrip.bloomberg_code as [Bloomberg Code] ,scrip.reuters_code as [Reuters Code],a.exchange,a.Series,a.Option_Type as [Option Type],a.Strike_price
2010-05-21 12:54:35.540 spid18s as [Strike Price],a.BuyQty,a.BuyPrice,a.BuyValue,a.SellQty,a.SellPrice,a.SellValue,NetValue=(a.SellValue-a.BuyValue),NetQty=(a.Buyqty-a.SellQty)
2010-05-21 12:54:35.540 spid18s ,Lots=(case when a.mnminstrumentName in ('FUTIDX','FUTSTK','OPTSTK','OPTIDX') then (case when (convert(bigint,a.SellQty))<>0 then (convert(bigint,a.SellQty))/(convert(bigint,scrip.lot_size)) else (convert(bigint,a.BuyQty))/(convert(bigint,scrip.lot_size))
2010-05-21 12:54:35.540 spid18s else 0 end),a.BPS,c.client_type as [Client Type],c.client_ac_name as [Client Name] from ClientBPS a with(nolock)
2010-05-21 12:54:35.540 spid18s inner join user_client_mapping on clientcode=clientid and user_id=@userID
2010-05-21 12:54:35.540 spid18s left outer join scrip_master scrip on isnull(scrip.NSE_symbol,scrip.bse_Code)=a.symbolcode
2010-05-21 12:54:35.540 spid18s left outer join client_master c on c.client_ac_code=a.clientcode
2010-05-21 12:54:35.540 spid18s order by a.clientcode,a.symbolcode,a.exchange,a.symbol
2010-05-21 12:54:35.540 spid18s inputbuf
2010-05-21 12:54:35.540 spid18s Proc [Database Id = 8 Object Id = 2046630334]
2010-05-21 12:54:35.540 spid18s process id=processf604d8 taskpriority=0 logused=0 waitresource=OBJECT: 8:754101727:4 waittime=3093 ownerId=88010059 transactionname=TRUNCATE TABLE lasttranstarted=2010-05-21T12:54:32.437 XDES=0xbb5d9f8 lockMode=Sch-M schedulerid=7 kpid=7828 status=susp
2010-05-21 12:54:35.540 spid18s executionStack
2010-05-21 12:54:35.540 spid18s frame procname=OnlineMTMUAT.dbo.SP_ClientBPS line=240 stmtstart=25226 stmtend=25276 sqlhandle=0x030008009e92785bda07bf00eb9c00000100000000000000
2010-05-21 12:54:35.540 spid18s truncate table ClientBPS
2010-05-21 12:54:35.540 spid18s inputbuf
2010-05-21 12:54:35.540 spid18s Proc [Database Id = 8 Object Id = 1534628510]
2010-05-21 12:54:35.540 spid18s resource-list
2010-05-21 12:54:35.540 spid18s objectlock lockPartition=4 objid=754101727 subresource=FULL dbid=8 objectname=OnlineMTMUAT.dbo.ClientBPS id=lock1a22f840 mode=Sch-S associatedObjectId=754101727
2010-05-21 12:54:35.540 spid18s owner-list
2010-05-21 12:54:35.540 spid18s owner id=processcb4a78 mode=Sch-S
2010-05-21 12:54:35.540 spid18s waiter-list
2010-05-21 12:54:35.540 spid18s waiter id=processf604d8 mode=Sch-M requestType=wait
2010-05-21 12:54:35.540 spid18s objectlock lockPartition=2 objid=754101727 subresource=FULL dbid=8 objectname=OnlineMTMUAT.dbo.ClientBPS id=lock21610a00 mode=Sch-M associatedObjectId=754101727
2010-05-21 12:54:35.540 spid18s owner-list
2010-05-21 12:54:35.540 spid18s owner id=processf604d8 mode=Sch-M
2010-05-21 12:54:35.540 spid18s waiter-list
2010-05-21 12:54:35.540 spid18s waiter id=processcb4a78 mode=Sch-S requestType=wait
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 21, 2010 at 8:03 am
sanketahir1985 (5/21/2010)
it depends on amount of databut developers have taken care of that thing ,they have used some threading concept in which it checks for thread if it is busy then other query won't get fired (this is applicable to sp_clientBPS)
here the case is some times select statement blocks Truncate statement & it takes around 40-42 sec.
to execute
shud i work on that select statement?
If it were an SQL Server Scheduled job, they wouldn't have to worry about all of that.
The idea of having two tables and either a view (like Leo suggested) or especially a synonym snap between the two could help a lot. What would also help alot is if the other process were very effecient.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2010 at 3:59 am
how can it be sql job? it runs after every 5 sec
sql job min freq. is 1 min.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 23, 2010 at 4:29 pm
sanketahir1985 (5/21/2010)
but let me tell u my scenarioactually SP_ClientBPS runs after every 5 sec. thru console program
while Sp_IEActiveScreen gets fired randomly by users avg freq is 5-10 hits per sec
just confirm me whether ur work around will work in this scenario or not?
The work arounds would work, BUT...
I would be seriously concerned if my developers deliverd a console app that potentially truncated and reloaded a table every 5 seconds. There must be better ways to do this.
Why are the developers using this and what are they actually tying to achieve. As I see it one of the jobs of a DBA is to advise them on bad practice and recommend better ways of doing things. And what they are doing here is definitly bad practice.
Leo
Striving to provide a better service.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 23, 2010 at 7:41 pm
sanketahir1985 (5/22/2010)
how can it be sql job? it runs after every 5 secsql job min freq. is 1 min.
That's part of my point. I agree with Leo in wondering why the developers have created code that truncates and rebuilds a table every 5 seconds.
Still, it can be done. If you put the code in a job along with a loop and a WAITFOR DELAY '00:00:05', then the job will run continuously a dutifully process the whole schebang every 5 seconds + execution time.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2010 at 10:41 pm
Leo.Miller (5/23/2010)
sanketahir1985 (5/21/2010)
but let me tell u my scenarioactually SP_ClientBPS runs after every 5 sec. thru console program
while Sp_IEActiveScreen gets fired randomly by users avg freq is 5-10 hits per sec
just confirm me whether ur work around will work in this scenario or not?
The work arounds would work, BUT...
I would be seriously concerned if my developers deliverd a console app that potentially truncated and reloaded a table every 5 seconds. There must be better ways to do this.
Why are the developers using this and what are they actually tying to achieve. As I see it one of the jobs of a DBA is to advise them on bad practice and recommend better ways of doing things. And what they are doing here is definitly bad practice.
Leo
Striving to provide a better service.
exactly Leo, i do agree with that
i already told them to use temp table approach to resolve deadlock problem
but developers are not convinced with that,so i was looking for any other option.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 24, 2010 at 1:59 pm
sanketahir1985 (5/23/2010)
exactly Leo, i do agree with thati already told them to use temp table approach to resolve deadlock problem
but developers are not convinced with that,so i was looking for any other option.
Sorry, there is no other option. Their design is flawed and they will keep getting this deadlock.
Why don't you tell us what they are trying to do and there may be a better way to do it.
Leo
There are 10 types of people in the world.
Those who understand binary and and those that don't
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply