November 28, 2006 at 9:33 am
I have a locking issue where a application called WBI jdbc connector 2.6.4 polls my dbase every 10 minutes using the sp_execute;1 statement. For the last 2 weeks this statement is starting a blocking chain and blocks my daily dbcc indexdefrag against a table. This goes on all night long until i either kill the spid or cancel the job.
Here is sp_lock.
spid 230 is blocking spid and the sp_execute (select) and spid 147 is the indexdefrag.
per sp_who spid 230 cpu changes yet spid147 does not.
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
147 13 0 0 DB S GRANT
147 2 0 0 EXT 10:61936 X GRANT
147 13 1391500186 1 PAG 3:5300 X WAIT ****** IS lock not compatible with X lock
147 13 0 0 PAG 3:192544 X GRANT
147 13 0 0 EXT 3:192544 X GRANT
147 13 1391500186 0 TAB IX GRANT
147 13 1391500186 0 TAB IX GRANT
147 13 0 0 IDX IDX: 13:13915001 X GRANT
230 13 0 0 DB S GRANT
230 13 1391500186 1 PAG 3:5300 IS GRANT *****
Why is this happening intermittently . I suspect it is a WBI issue but they say no. any inout would be helpful.
This is sql2000 sp4
November 28, 2006 at 9:40 am
You may try
SELECT ... FROM ... WITH (NOLOCK)
November 28, 2006 at 9:54 am
They say this is a canned app and they cannot modify the code.
November 28, 2006 at 10:00 am
When using SELECT statement, system may lock the data pages while reading data. It is quite normal to the occurance of such locks. If the locks last too long, you may have to ask related people to modify their query. In reality, it is part of a DBA's job.
November 29, 2006 at 1:18 am
I've found indexdefrag isn't quite as useful as one might expect ( in sql 2000 ) I'd suggest you try using indexrebuild for the table - your other option is to put the database into single user whilst your job runs.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply