Post reply

locking issues

  • November 28, 2006 at 9:33 am

    #115286

    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

    #674435

    You may try

    SELECT ... FROM ... WITH (NOLOCK)

  • November 28, 2006 at 9:54 am

    #674447

    They say this is a canned app and they cannot modify the code.

  • November 28, 2006 at 10:00 am

    #674450

    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

    #674596

    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