Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    91

    Unanswered: SP, Isolation and blocking

    Hi Everyone -

    Here is the setup...
    I created a stored procedure that will fire off an event when called.
    (the event is set to manual execution)

    The event simply calls another stored procedure as soon as it is launched.

    The final stored procedure has a cursor and a series of deletes within it.

    <EDIT: the logic behind this is because the stored procedure locks
    the UI from being updated until it completes, but a event
    will be run by the database server without UI interuption>


    So the problem is....

    The stored procedure runs, but sometimes there is an error message
    that is displayed and the deletes rollback.

    The error is something about the page is locked... (i'll get the exacy error a little later)
    but for the moment, lets say the user has added a new record to the table,
    so the page seems to be locked from the user, and the event's stored
    procedure cannot continue.

    Inside the series of deletes for the events dtored procedure,
    i don't do any commits (By default, ODBC operates in autocommit mode -
    and each fetch of the cursor forces an commit)

    SO - Do i need to set the isolation level within the delete statement?

    If so, what isolation level would be best practice for speed?

    One thing to remember -
    There will be inserts into the tables that are being deleted from.

    AND

    where would the best place to change the isolation level at?

    Stored Proc that calls the event?
    Within the event itself that calls the delete stored procedure?
    Inside the delete stored procedure itself?

    or do i simply force a commit after each delete within the final stored procedure???


    sorry to ramble so much


    thanks again

    take care
    tony
    Last edited by alt-088; 11-18-05 at 16:15.

  2. #2
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Hi Tony,

    Is this for ASA or ASE?

    Anyways, if this is a simple locking problem then your STP would simply wait until your user is finished and then continues running. It only goes wrong if a deadlock happens. This means that your stored procedure is waiting for another process to release a lock, and the other process is waiting for your stored procedure to release a lock.

    I'm not completely sure if everything happening in a stored procedure is automatically happening within a transaction that is only committed when the stored procedure finishes, I dont remember it working like that. It should commit every action it takes unless if you started a transaction yourself. Possibly the open cursor may have to do something for it. Why are you using a cursor to delete the records btw?

    One more thing, if you are looping in your stored procedure anyways a trick is to give a select that returns output within your stored procedure. This will cause the stored procedure to return information back to your client periodically and your client will be able to react to that instead of waiting foreever on the stp to complete. This approach does have a risk because you can potentially send a new SQL command while the stored procedure is still running.

    Anyways, if you can give some more info about what your stp does, and what the other process does, and what the exact error is you are getting, then maybe we can say something a bit more sensable

    *edit* Owh I forgot to mention, isolation levels only determine how selects are processed, a delete,update,insert will always lock the data they change until the transaction is committed.

    Maybe you should try to do your select for your cursor in your stp using the holdlock keyword. Though I do not think that will be a nice solution.
    Last edited by BasOlij; 11-22-05 at 05:53.
    Greetz,

    Bastiaan Olij

  3. #3
    Join Date
    Oct 2004
    Posts
    91

    Update

    Bastiaan -

    Thanks for the reply...

    ASA 7 is the db in question.

    The initial stored proc simply fires the manual event.
    The event simply executes the final stored proc.

    The final stored proc does have a cursor
    without using the WITH HOLD parameter.

    >>Why are you using a cursor to delete the records btw?
    The cursor was utilized to allow for future expansion
    of the STP - right now, it calls another stored proc that
    actually performs 12 deletes and a series of sub-deletes.
    The original code was accomplished
    inside the VB6 application, so the stored procs and
    procedures were created to mirror the operation of the VB6 code.


    Here is the STP in question....

    Code:
      -- Declare the variables
      declare @r integer
      declare @xx integer
      declare @d DATETIME
      declare @cnt integer
      
      -- Fill the variables
      select @xx=(select ORG_lPurgeSample from tblOrganization)
      select @r=(-@xx)
      select @d=(select dateadd(dd,@r,getdate(*)))
      select @cnt=0
      
      -- Create a cursor for the expired samples
      declare cur_purge_sample dynamic scroll cursor for(select si_lkey from tblsampleinformation left outer join
          tblreturnhostinfo on rhi_lfksikey = si_lkey where
          si_bytexceptionstatus = 0 and
          si_dtoutputdatetime < @d and
          (rhi_boolreturnstatus is null or rhi_boolreturnstatus = 0))
      declare @si_lkey integer
      open cur_purge_sample
      fetch next cur_purge_sample into @si_lkey
      while(@@sqlstatus = 0)
        begin
          execute DBA.DeleteSIAndAssoc @si_lkey
          fetch next cur_purge_sample into @si_lkey
          select @cnt = @cnt + 1
        end
      close cur_purge_sample
      deallocate cursor cur_page_sample
      
      -- update the event log
      insert into tbleventlog (evlog_szOperator, evlog_szOption, evlog_szMessage, evlog_lchangectr, evlog_boolReportSelected) values ('Stored Proc', 'Completed', 'procPurgeSamples purged : ' + cast(@cnt as varchar(50)), 0, 0)
    <update>
    After looking at the code, i am in the process of trying 500 records at a time
    to allow the database to do its stuff between runs...
    </update>


    Code:
    create procedure DBA.procDeleteSIAndAssoc(@si_lkey integer)
    begin
      delete from tblReturnHostInfo where RHI_lfkSIKey = @si_lkey
      delete from tblReturnHostOrderInfo where RHOI_lfkSIKey = @si_lkey
      delete from tblOutputCommentInformation where OCI_lfkSIKey = @si_lkey
      delete from tblOutputResultInformation where ORI_lfkSIKey = @si_lkey
      delete from tblOutputSampleInformation where OSI_lfkSIKey = @si_lkey
      execute procDeleteNoteAndLookup @si_lkey
      delete from tblExceptionPool where EXPool_lfkSIKey = @si_lkey
      delete from tblResultInformation where RI_lfkSIKey = @si_lkey
      delete from tblSampleInformationComment where SIC_lfkSIKey = @si_lkey
      delete from tblSpecialFieldValue where SFV_lfkSIKey = @si_lkey
      delete from tblADTPatientInformation where ADTPI_lfkSIKey = @si_lkey
      delete from tblSampleInformation where si_lkey = @si_lkey
    end;
    This is the section of code that i'm leaning twords that
    might be causing the problems...


    Isolation stuff and commiting -
    I have pushed the isolation stuff out of my mind for
    a little while. I am going to concentrate on
    the transactions and commiting under the cursor.

    I think this might give me a little more milage....

    anyones thoughts???

    thanks for the replies!!

    take care
    tony

  4. #4
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Hey Tony,

    Hmm I work with ASE myself so I am not sure how ASA handles transactions and stored procedure.

    With the delete approach you are using you do have a great potential for a deadlock if all happens within a transaction. You are repeating deletes over multiple tables and if another process accesses those tables and touch those records....

    Just imagine, you delete from table A, then you try a delete on table B, then you delete another record from A, then another on B.

    So A-B-A-B

    If another process updates a record in A while you are still busy with B, and then you delete from A again and now get locked by that other process, and the other process now attempts to update a record in table B that you've locked, both process wait on eachother.

    It may be smart for you to rewrite your stp to first do all deletes in table A, then all deletes in table B, then all deletes in table C, etc.

    What you could do is not use a cursor but have your stored procedure fill a temporary table and then call the second stored procedure that uses that same temporary table to delete the data.

    Your stored procedures will look something like this:
    Code:
    /* create the temporary table that your other stored procedure would normally make before creating the second stored procedure */
    create table #TMP_TABLE (
      TMP_KEY numeric(15) not null /* I'm assuming you are using numeric keys */
    );
    create index key1 on #TMP_TABLE (TMP_KEY);
    create procedure DBA.procDeleteSIAndAssoc
    begin
      delete from tblReturnHostInfo from #TMP_TABLE where RHI_lfkSIKey = TMP_KEY
      delete from tblReturnHostOrderInfo #TMP_TABLE where RHOI_lfkSIKey = TMP_KEY
      /* ... */
    end;
    drop table #TMP_TABLE;
    
    create procedure DBA.doStuff
    begin
      /* ... */
    
      select si_lkey TMP_KEY 
        into #TMP_TABLE 
        from tblsampleinformation left outer join
        tblreturnhostinfo on rhi_lfksikey = si_lkey where
        si_bytexceptionstatus = 0 and
        si_dtoutputdatetime < @d and
        (rhi_boolreturnstatus is null or rhi_boolreturnstatus = 0))
    
      create index key1 on #TMP_TABLE (TMP_KEY)
    
       execute DBA.DeleteSIAndAssoc
    
      /* ... */
    end;
    Not completely sure if ASA has the same syntax as ASE though..
    Greetz,

    Bastiaan Olij

  5. #5
    Join Date
    Oct 2004
    Posts
    91

    Excellent

    Bastiaan -

    Thanks for the reply...

    three things....

    1) Do i need to create the index twice?

    2) The syntax for the deletes - which one is correct??

    3) Do i wrap each of the deletes with a begin trans and/or commit?

    Excellent idea!!

    thanks again for the assistance!

    take care
    tony

  6. #6
    Join Date
    Feb 2003
    Location
    Netherlands
    Posts
    63
    Hi Tony,

    Quote Originally Posted by alt-088
    1) Do i need to create the index twice?
    You aren't creating them twice. The first time you create them (and the table) it is just so you can create the stored procedure. When creating a stored procedure the tables used in it should exist even if later on they are dropped aslong as they (or a new table by the same name) exists when the stored procedure is run at a later date.

    The scripts as I presented it only creates the stored procedures. Once you then run the 2nd stored procedure, it will actually create the temporary table, fill it with data, create the index and then call the 1st stored procedure so it can use the data.

    Quote Originally Posted by alt-088
    2) The syntax for the deletes - which one is correct??
    Thats what you get when you are in a hurry typing things, my fault
    The correct syntax is:
    delete <table_to_delete_from> from <other_tables> where <whereclause>

    So it should be:

    delete tblReturnHostInfo from #TMP_TABLE where RHI_lfkSIKey = TMP_KEY

    Quote Originally Posted by alt-088
    3) Do i wrap each of the deletes with a begin trans and/or commit?
    Well on ASE if you don't do this each delete is commited automatically so I think this is a difference with ASA. If in ASA all commands within a stored procedure always happen within a transaction than there is no extra need for the begin tran and commit statements.

    I would try it first without the transaction commands and see what happens.

    Sorry for the confusion with the typo's
    Greetz,

    Bastiaan Olij

Posting Permissions

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