Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38

    Unanswered: Stored procedure now (b)locking...

    I have a stored procedure that uses a 3rd party ODBC driver to mirror information on our local SQL server for local queries.

    All has been running fine until this quarter.

    Now for some reason, the procedure is locking the database that is being written exclusively. I added set transaction isolation level read committed to try to allow reads. This sp is the only thing writing to the database.

    Now, occasionally it is failing to connect on at least one iteration of the sp, which runs every 10 minutes.

    Is there some other way to set the locking to allow reads of the data up to that poing. I though that was what set transaction isolation level read committed was doing.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Stored procedure now (b)locking...

    Have you tried (nolock) in your query?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Got any code to show us?

    I'm assuming the stored procedure is doing INSERTS...How many?

    Why every 10 minutes?

    (NOLOCK) should be used with Care or not at all...it can give yo uthw wrong results..

    Failing to connect?

    What Platform and flavor are you running?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38

    Re: Stored procedure now (b)locking...

    Originally posted by joejcheng
    Have you tried (nolock) in your query?
    Adding nolock will allow "dirty reads" which I do not want. I want to read only commited data. That is how it is supposed to be by default. However, when the connection times out, then it deadlocks.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you doing dynamic sql from the front end app?

    Doesn't sound like it...what connection is timing out?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38
    The front end is Cold Fusion.

    The write is a stored procedure that is passed a single varchar variable.

    There is a loop that runs the sp once for every quarter that needs to be processed.

    The timeout seems to happen communicating with the remote server. I don't think there is anyway to avoid this. However, the entire web server is brought to its knees whenever the blocking occurs. Our front page doesn't even load.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where's the loop? Cold fusion? Do you leave the connection open the whole time?

    This sounds like a batch process, why isn't running on the server a scheduled job?

    Where does the parameter come from?

    Can you post the sproc?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38
    You asked for it...

    ALTER proc dbo.getClasses @Range varchar(4) = 'A233'
    as
    declare @sqlquery1 varchar(2500), @sqlquery2 varchar(1500), @list_id varchar(6), @updatetime datetime
    set @sqlquery1 = 'insert into classes(list_id,course_number,course_id,section,ti tle,credits,capacity,enrolled,instructor,days,time start,timeend,location,cluster_id,cluster_cap,clus ter_enr,closed)
    Select
    ''q'' + Lower(class_d.YRQ) + ''b'' as list_id,
    left(class_d.CLASS_ID,4) as course_number,
    rtrim(left(class_d.COURSE_ID,5)) + '' '' + rtrim(right(class_d.COURSE_ID,5)) as course_id,
    class_d.SECT as section,
    rtrim(class_d.COURSE_TITLE) as title,
    class_d.CR/10 as credits,
    class_d.CLASS_CAP as capacity,
    class_d.ENR as enrolled,
    rtrim(class_d.INSTR_NAME) as instructor,
    tblDays.ABBR_TITLE as days,
    starttime =
    case when rtrim(class_d.STRT_TIME) = ''ARR'' or rtrim(class_d.STRT_TIME) = ''''
    then cast(NULL as smalldatetime)
    else cast(left(rtrim(class_d.STRT_TIME),2) + '':'' + right(rtrim(class_d.STRT_TIME),3) + ''M'' as smalldatetime) end,
    endtime =
    case when rtrim(class_d.END_TIME) = ''ARR'' or rtrim(class_d.END_TIME) = ''''
    then cast(NULL as smalldatetime)
    else cast(left(rtrim(class_d.END_TIME),2) + '':'' + right(rtrim(class_d.END_TIME),3) + ''M'' as smalldatetime) end,
    rtrim(left(class_d.ROOM_LOC,3)) + '' '' + right(rtrim(class_d.ROOM_LOC),4) as location,
    left(class_d.CLASS_CLUSTER,4) as clusterid,
    class_cluster_m.CLUSTER_CAP as cluster_cap,
    class_cluster_m.CLUSTER_ENR as cluster_enr,
    class_d.CLASS_CLOSE as closed
    from openquery(hp3000 ,''select * from class_d where YRQ = ''''' + @Range + ''''''') as class_d
    left join openquery(hp3000 ,''select * from day_d'') as tblDays on class_d.DAY_CD = tblDays.DAY_CD
    left join openquery(hp3000 ,''select * from class_cluster_m'') as class_cluster_m on class_d.CLASS_CLUSTER = class_cluster_m.CLASS_CLUSTER
    where right(left(SECT_STAT,4),1) != ''M'''
    set @sqlquery2 = 'insert into alternate(list_id,course_number,instructor,days,ti mestart,timeend,location)
    select ''q'' + right(rtrim(room_d.CLASS_ID),4) + ''b'' as list_id,
    left(rtrim(room_d.CLASS_ID),4) as course_number,
    rtrim(room_d.INSTR_NAME) as instructor,
    rtrim(tblDays.ABBR_TITLE) as days,
    timestart =
    case when rtrim(room_d.STRT_TIME) = ''ARR'' or rtrim(room_d.STRT_TIME) = ''''
    then cast(NULL as smalldatetime)
    else cast(left(rtrim(room_d.STRT_TIME),2) + '':'' + right(rtrim(room_d.STRT_TIME),3) + ''M'' as smalldatetime) end,
    timeend =
    case when rtrim(room_d.END_TIME) = ''ARR'' or rtrim(room_d.END_TIME) = ''''
    then cast(NULL as smalldatetime)
    else cast(left(rtrim(room_d.END_TIME),2) + '':'' + right(rtrim(room_d.END_TIME),3) + ''M'' as smalldatetime) end,
    left(rtrim(room_d.ROOM_LOC),3) + '' '' + right(rtrim(room_d.ROOM_LOC),4) as location from
    openquery(hp3000, ''select * from instr_room_d'') as room_d
    left join openquery(hp3000 ,''select * from day_d'') as tblDays
    on room_d.DAY_CD = tblDays.DAY_CD where room_d.class_id like ''%' + @Range + ''''
    set @list_id = 'q' + lower(@Range) + 'b'
    set @updatetime = getdate()
    set transaction isolation level read committed
    begin tran
    delete from lists
    where list_id = @list_id
    insert into lists(list_id, list_date)
    values(@list_id, @updatetime)
    if @@error <> 0
    begin
    rollback tran
    print 'No update of lists table'
    return
    end
    delete from classes
    where list_id = @list_id
    if @@error <> 0
    begin
    rollback tran
    print 'No delete from classes table'
    return
    end
    delete from alternate
    where list_id = @list_id
    if @@error <> 0
    begin
    rollback tran
    print 'No delete from alternate table'
    return
    end
    exec(@sqlquery1)
    if @@error <> 0
    begin
    rollback tran
    print 'No insert into classes table'
    return
    end
    exec(@sqlquery2)
    if @@error <> 0
    begin
    rollback tran
    print 'No insert into alternate table'
    return
    end
    commit tran

    The batch is run on ColdFusion to perform the query to determine what quarters (YRQs) are processed. It is a ColdFusion page to utilize the scheduling of the the server.

    If you want the coldfusion code, let me know.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

  9. #9
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38
    Originally posted by Brett Kaiser
    Got any code to show us?

    I'm assuming the stored procedure is doing INSERTS...How many?

    Why every 10 minutes?

    (NOLOCK) should be used with Care or not at all...it can give yo uthw wrong results..

    Failing to connect?

    What Platform and flavor are you running?
    The insert is as many as 1500 records on average. This is for class openings. That is why the update every 10 minutes.

    I am not going to use NOLOCK to avoid problems.

    This is a ColdFusion front end on SQL Server 2000 connecting to a remote server with openquery. The remote server is an HP3000 with a Turbo Image (ISAM) database.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dynamic SQL...why did have to be dynamic SQL...

    Your stored procedure is on on sql server box, and you call another one using OPENQUERY in the FROM Clause?

    Is there a reason?

    Can't you set up Linked servers?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38
    Originally posted by Brett Kaiser
    Dynamic SQL...why did have to be dynamic SQL...

    Your stored procedure is on on sql server box, and you call another one using OPENQUERY in the FROM Clause?

    Is there a reason?

    Can't you set up Linked servers?
    The TurboImage database has to be accessed by a 3rd party ODBC driver.

    Also, state security audits prohibit linked servers.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ????

    Audits prohibit?

    Why, what's the reason?

    And didn't you find a work around?

    Did you try setting up a trace to see what's going on when the sproc is called...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38
    Originally posted by Brett Kaiser
    ????

    Audits prohibit?

    Why, what's the reason?

    And didn't you find a work around?

    Did you try setting up a trace to see what's going on when the sproc is called...
    I am not sure why the linked servers are not allowed. But it is really a moot point as it is ot an option.

    If I run the sp from qa, it runs everytime. I think it is timing out. We started registration, and the remote server is getting hammered right now.

    My biggest concern is how to not have the locking occur. When it does, it brings the web server to its knees.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •