March 1, 2010 at 1:23 am
HI all
I have a funny problem. What I see it that a select from one table "A" is blocking another operation on a diff table "B". I did check and there is no for_key contraint and no triggers on either of the tables. The select from table "A" does not only lock this table but different tables. Why would this happen? What can I do to get rid of the Locks?
In the Activity monitor I see the following.
Selecty from "A" :
declare @P1 float
SELECT * FROM JDE_PRODUCTION.PRODDTA.F4215 WHERE ( XHSHPN = @P1 )
SELECT from "B" :
(@P1 nchar(4),@P2 nchar(2),@P3 nchar(10))SELECT * FROM JDE_PRODUCTION.PRODCTL.F0005
WHERE ( DRSY = @P1 AND DRRT = @P2 AND DRKY = @P3 )
Lock that is created :
LOCK_M_IX
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
March 1, 2010 at 2:28 am
Are you sure that those are the only statements that were running. Is there a chance that those are the LAST statements that the sessions ran? My guess is that at least one of the sessions is in the middle of transaction and a previous update/delete/insert statement is causing the blocking. You can check the @@trancount of the session using this query:
select open_transaction_count from sys.dm_exec_requests where session_id = WriteSPIDHere
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/
March 1, 2010 at 2:43 am
I ran that query while the locks was in place but the query did not return any information.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
March 1, 2010 at 2:56 am
Do you mean that it returned no records at all? Didn’t stop running? Returned 0 as number of transactions count? Or anything else? Also did you change it so it will show you the transaction counts of the blocking process?
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/
March 1, 2010 at 2:58 am
Maybe found the problem. The command "DBCC inputbuffer" did not give the correct code that was actualy locking tables. I checked the activity monitor and that session had 4 diff scripts running at the same time. The other scripts/queries was blocking the other tables.
I Saw this once and every time after that it gave me the same query as above. Stil a problem
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
March 1, 2010 at 3:30 am
Actually I am still struggeling to see what the code is that is blocking. I have tried activity monitor and inputbuffer but the system is giving me the same query the whole time. If I check my trace I can see alot of diff scripts and processes but nothing that would explain why the select would lock the other tables for so long. Some times it locks the tables for more than 5 minutes.
I ran that query with the blocking spid (1246) and it gave me no result. Not a 0 not a null?? Do not know why because that session was busy. Could it have something to do with the fact that there is alot of sp_cursorexecute, sp_cursorfetch and sp_cursorunprepare
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
March 1, 2010 at 3:51 am
Here is some screen shots.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
March 1, 2010 at 8:56 am
it can be as simple as not enough memory....
March 1, 2010 at 9:55 am
As far as I know it will then give diff waittime not a lck. Do you know of a way to make sure, accept for the normal perfmon?
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
March 1, 2010 at 10:50 am
i thought so too... 🙂
i just posted a new topic 'memory wait time'
i too have two different databases afected by the third one with locks and everything....
and the only perf counter spike that i can see is a 'memory wait', even though sql has 26Gb of it dedicated
???
anyway, it's just a thought....
March 1, 2010 at 11:06 pm
Will investigate the memory thanks.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
March 1, 2010 at 11:49 pm
Hi
In this case First u run this command
select spid,blocked from sysprocesses where blocked>o
it will give the information of which ids r cause of this blocking.
after that use this command DBCC Inputbuffer(spid).it will give brief information of blocking transtions.may be here u will find the solution.
Still u can't understand the problem the next step is u run the profiler.
March 2, 2010 at 12:07 am
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.blocking_session_id,
Db_name(req.database_id) AS database_name,
req.wait_type,
req.wait_time,
req.last_wait_type,
req.reads,
req.writes,
req.logical_reads,
sqltext_blocking.TEXT AS blocking_stmt
FROM sys.dm_exec_requests req
LEFT OUTER JOIN sys.sysprocesses blocking
ON blocking.spid = req.blocking_session_id
OUTER APPLY sys.Dm_exec_sql_text(req.sql_handle) AS sqltext
OUTER APPLY sys.Dm_exec_sql_text(blocking.sql_handle) AS sqltext_blocking
WHERE req.session_id > 50
Try the above query. This will tell you what query is blocking what clearly.
Regards,
Raj
March 15, 2010 at 1:50 am
Hi - Sorry it took so long. Problem came up now again. You will see the result of the query and that the table select that is blocking has nothing to do with the table select being blocked. My question is how is this possible??
Blocked STMT
(@P1 nchar(3),@P2 nchar(3),@P3 nchar(3),@P4 nchar(3),@P5 nchar(3),@P6 numeric(18, 0),@P7 numeric(18, 0),@P8 nchar(3),@P9 float,@P10 nchar(5),
@P11 nchar(5),@P12 nchar(5),@P13 nchar(5),@P14 nchar(5),@P15 nchar(5))
SELECT T0.SDKCOO, T0.SDDOCO, T0.SDDCTO, T0.SDLNID, T0.SDMCU, T0.SDCO,
T0.SDOKCO, T0.SDOORN, T0.SDOCTO, T0.SDOGNO, T0.SDRKCO, T0.SDRORN, T0.SDRCTO, T0.SDRLLN, T0.SDDMCT, T0.SDDMCS, T0.SDAN8, T0.SDSHAN, T0.SDPA8,
T0.SDDRQJ, T0.SDPDDJ, T0.SDADDJ, T0.SDIVD, T0.SDCNDJ, T0.SDDGL, T0.SDRSDJ, T0.SDPEFJ, T0.SDPPDJ, T0.SDVR02, T0.SDITM, T0.SDLITM, T0.SDAITM,
T0.SDLOCN, T0.SDLOTN, T0.SDFRGD, T0.SDTHGD, T0.SDFRMP, T0.SDTHRP, T0.SDEXDP, T0.SDDSC1, T0.SDDSC2, T0.SDLNTY, T0.SDNXTR, T0.SDLTTR, T0.SDEMCU,
T0.SDRLIT, T0.SDKTLN, T0.SDCPNT, T0.SDRKIT, T0.SDKTP, T0.SDSRP1, T0.SDSRP2, T0.SDSRP3, T0.SDSRP4, T0.SDSRP5, T0.SDPRP1, T0.SDPRP2, T0.SDPRP3,
T0.SDPRP4, T0.SDPRP5, T0.SDUOM, T0.SDUORG, T0.SDSOQS, T0.SDSOBK, T0.SDSOCN, T0.SDSONE, T0.SDQTYT, T0.SDCOMM, T0.SDOTQY, T0.SDUPRC, T0.SDAEXP,
T0.SDPROV, T0.SDTPC, T0.SDAPUM, T0.SDLPRC, T0.SDUNCS, T0.SDECST, T0.SDCSTO, T0.SDTCST, T0.SDINMG, T0.SDPTC, T0.SDRYIN, T0.SDDTBS, T0.SDTRDC,
T0.SDASN, T0.SDPRGR, T0.SDCLVL, T0.SDCADC, T0.SDKCO, T0.SDDOC, T0.SDDCT, T0.SDODOC, T0.SDODCT, T0.SDOKC, T0.SDPSN, T0.SDDELN, T0.SDTAX1,
T0.SDTXA1, T0.SDEXR1, T0.SDATXT, T0.SDPRIO, T0.SDRESL, T0.SDBACK, T0.SDSBAL, T0.SDAPTS, T0.SDLOB, T0.SDEUSE, T0.SDDTYS, T0.SDNTR, T0.SDVEND,
T0.SDCARS, T0.SDMOT, T0.SDROUT, T0.SDSTOP, T0.SDZON, T0.SDCNID, T0.SDFRTH, T0.SDSHCM, T0.SDSHCN, T0.SDSERN, T0.SDUOM1, T0.SDPQOR, T0.SDUOM2,
T0.SDSQOR, T0.SDUOM4, T0.SDITWT, T0.SDWTUM, T0.SDITVL, T0.SDVLUM, T0.SDRPRC, T0.SDORPR, T0.SDORP, T0.SDCMGP, T0.SDGLC, T0.SDCTRY, T0.SDFY,
T0.SDSO01, T0.SDSO02, T0.SDSO03, T0.SDSO04, T0.SDSO05, T0.SDSO06, T0.SDSO07, T0.SDSO08, T0.SDSO09, T0.SDSO10, T0.SDSO11, T0.SDSO12, T0.SDSO13,
T0.SDSO14, T0.SDACOM, T0.SDCMCG, T0.SDRCD, T0.SDGRWT, T0.SDGWUM, T0.SDSBL, T0.SDSBLT, T0.SDLCOD, T0.SDUPC1, T0.SDUPC2, T0.SDUPC3, T0.SDSWMS,
T0.SDUNCD, T0.SDCRMD, T0.SDCRCD, T0.SDCRR, T0.SDFPRC, T0.SDFUP, T0.SDFEA, T0.SDFUC, T0.SDFEC, T0.SDURCD, T0.SDURDT, T0.SDURAT, T0.SDURAB,
T0.SDURRF, T0.SDTORG, T0.SDUSER, T0.SDPID, T0.SDJOBN, T0.SDUPMJ, T0.SDTDAY, T0.SDSO16, T0.SDSO17, T0.SDSO18, T0.SDSO19, T0.SDSO20, T0.SDIR01,
T0.SDIR02, T0.SDIR03, T0.SDIR04, T0.SDIR05, T0.SDSOOR, T0.SDVR03, T0.SDDEID, T0.SDPSIG, T0.SDRLNU, T0.SDPMDT, T0.SDRLTM, T0.SDRLDJ, T0.SDDRQT,
T0.SDADTM, T0.SDOPTT, T0.SDPDTT, T0.SDPSTM, T0.SDXDCK, T0.SDXPTY, T0.SDDUAL, T0.SDBSC, T0.SDCBSC, T0.SDCORD, T0.SDDVAN, T0.SDPEND, T0.SDRFRV,
T0.SDMCLN, T0.SDSHPN, T0.SDRSDT, T0.SDPRJM, T0.SDOSEQ, T0.SDMERL, T0.SDHOLD, T0.SDHDBU, T0.SDDMBU, T0.SDBCRC, T0.SDODLN, T0.SDPOE, T0.SDPMTO,
T0.SDANBY, T0.SDPMTN, T0.SDNUMB, T0.SDAAID, T1.SHKCOO, T1.SHDOCO, T1.SHDCTO, T1.SHSFXO, T1.SHMCU, T1.SHCO, T1.SHRORN, T1.SHAN8, T1.SHSHAN,
T1.SHTRDJ, T1.SHPDDJ, T1.SHVR01, T1.SHDEL1, T1.SHDEL2, T1.SHINMG, T1.SHHOLD, T1.SHPLST, T1.SHFRTH, T1.SHOTOT, T1.SHWUMD, T1.SHCRRM, T1.SHCRCD,
T1.SHFAP, T1.SHDOC1, T1.SHDCT4, T1.SHBCRC, T2.ABAN8, T2.ABMCU, T2.ABAC08 FROM JDE_PRODUCTION.PRODDTA.F4211 T0,JDE_PRODUCTION.PRODDTA.F4201 T1,
JDE_PRODUCTION.PRODDTA.F0101 T2
WHERE ( ( T0.SDROUT <> @P1 AND T0.SDROUT <> @P2 AND T0.SDROUT <> @P3 ) AND
( T0.SDNXTR >= @P4 AND T0.SDNXTR <= @P5 AND T0.SDDRQJ >= @P6 AND T0.SDDRQJ <= @P7 AND T2.ABAC08 = @P8 AND T0.SDSOQS > @P9 ) ) AND
( ( T0.SDCO = @P10 OR T0.SDCO = @P11 OR T0.SDCO = @P12 OR T0.SDCO = @P13 OR T0.SDCO = @P14 OR T0.SDCO = @P15 ) )
AND ( T0.SDDOCO=T1.SHDOCO AND T0.SDDCTO=T1.SHDCTO AND T0.SDKCOO=T1.SHKCOO AND T1.SHSHAN=T2.ABAN8 )
ORDER BY T0.SDSHAN ASC,T0.SDDSC1 ASC,T0.SDDRQJ ASC
BLOCKED BY
(@P1 float)SELECT * FROM JDE_PRODUCTION.PRODDTA.F4215 WHERE ( XHSHPN = @P1 )
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
March 15, 2010 at 1:53 am
here is another:
Blocked stmt
(@P1 nchar(10),@P2 nchar(30),@P3 float,@P4 float,@P5 float,@P6 float,@P7 float,@P8 float,@P9 float,@P10 nchar(15),@P11 nchar(2),@P12 numeric(18, 0),@P13 float,@P14 float,@P15 numeric(18, 0),@P16 float)
INSERT INTO JDE_PRODUCTION.PRODDTA.F57CD100
VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16)
BLOCKING STMT
(@P1 float)SELECT * FROM JDE_PRODUCTION.PRODDTA.F4215 WHERE ( XHSHPN = @P1 )
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply