locked
If we should have the record lock RRS feed

  • Question

  • Hi,

    I have one stored proc that will be called by the scheduled job

    Definitely the SP is retrieving data from the tables, During processing the big amount of data from the scheduled jobs, it seems sometimes the records are not retrieved properly especially under the case that 2 jobs are at the same time trying to retrieve the same set of records from the same table. It seems that the 2nd try will lead to not be able to catch the records (since I see the wrong result should be due to that some records cannot be retrieved properly, that are actually existing in the table).

    Why I have these suspicion is, if I call the SP continually (I mean to process the job one after another instead of using the schedule way), everything is fine. Should we apply anything like to lock the records during processing the scheduled jobs?

    Here are more details for the situation.
    I do have one schedule job that will call the following SP. This SP will further call p_jb_e2lv again and again. You know this main SP is just processing the EDI files which will come and maybe sometimes are jamming there. Within p_jb_e2lv (that is another SP), there're many transactions like select, update, insert. The defeating each other thing does happen within p_jb_e2lv. I mean each time too many running p_jb_e2lv are just defeating each other for the same record set. How to do it well?


    CREATE PROCEDURE [p_jb_caller]
    WITH
    EXECUTE AS CALLER
    AS
    begin
     set nocount on;

     declare
     cur1 cursor
     for select jb_rp_hd_id
     from jb_rp_header
     where status='FINE'
     declare @v_hd_id int,
     @v_message nvarchar (200),
     @v_return_value int
     
     open cur1
     
     fetch next from cur1
     into @v_hd_id
     
     while @@fetch_status=0
     begin
      SET @v_message = NULL
      EXEC @v_return_value = MYschema.dbo.p_jb_e2lv @v_hd_id, 'Y', @v_message OUT
      --SELECT @v_message AS N'@v_message', @v_return_value AS N'@Return Value'
     
      fetch next from cur1
      into @v_hd_id
     end
     
     close cur1
     deallocate cur1
    end
    GO


    Many Thanks & Best Regards, HuaMin Chen
    • Moved by Tom Phillips Friday, March 26, 2010 3:31 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Friday, March 26, 2010 2:25 AM

All replies

  • I wiill suggest you to use SSIS for this with Sequesnce Container and Watch window.
    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Friday, March 26, 2010 9:23 AM
  • thanks. can you pls provide with more details? I'm just calling another SP within one SP.
    Many Thanks & Best Regards, HuaMin Chen
    Friday, March 26, 2010 9:37 AM
  • Here are some details from the called SP

     declare cur_othr cursor for
      select it_tp_code,
      it_type,
      it_code,
      it_dsc,
      act1,
      act2,
      fig
      from tb_othr
      where hd_ID=@hd_ID;
     
     open cur_othr
     fetch next from cur_othr into @it_tp_code_othr,
     @it_tp_othr,
     @it_code_othr,
     @it_dsc_othr,
     @act1_othr,
     @act2_othr,
     @fig_othr;
     
     set @act1_othr=isnull(@act1_othr,0)
     set @act2_othr=isnull(@act2_othr,0)
     set @fig_othr=isnull(@fig_othr,0)
     
     while @@fetch_status=0
     begin
      ...
      
      fetch next from cur_othr into @it_tp_code_othr,
      @it_tp_othr,
      @it_code_othr,
      @it_dsc_othr,
      @act1_othr,
      @act2_othr,
      @fig_othr

      set @act1_othr=isnull(@act1_othr,0)
      set @act2_othr=isnull(@act2_othr,0)
      set @fig_othr=isnull(@fig_othr,0)
      set @fields=''
      
     end

    It seems for most cases, the 1st record will be missed when this is called by the scheduled p_jb_caller. But I don't have any problem when I call p_jb_e2lv one item id after another by another SP (not through the scheduled jobs)


    Many Thanks & Best Regards, HuaMin Chen
    Tuesday, March 30, 2010 5:19 AM
  • Since I suspect the reason of this, I wanna check if in the database, we should have as little data processing as possible for the scheduled SP. Many thanks in advance.
    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, April 14, 2010 6:38 AM
  • Here are more details for the situation.
    I do have one schedule job that will call the following SP. This SP will further call p_jb_e2lv again and again. You know this main SP is just processing the EDI files which will come and maybe sometimes are jamming there. Within p_jb_e2lv (that is another SP), there're many transactions like select, update, insert. The defeating each other thing does happen within p_jb_e2lv. I mean each time too many running p_jb_e2lv are just defeating each other for the same record set. How to do it well?


    You can setup a QUEUE table, post the requests, and have a stored procedure process the requests in the QUEUE in a delayed sequential manner.

    It is also possible that you just have a bug.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, April 14, 2010 7:55 AM
  • Many many thanks Kalman.
    1. My colleague did schedule the job and the scheduled process is running one SP which continually picks up the header records with the status 'Ready'
    2. I don't think it's due to a bug from the SP as everything is fine if I continually run the header records

    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, April 14, 2010 8:21 AM
  • How can I see how the job is scheduled, like by what time slice, the SP will be called?


    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, April 14, 2010 8:58 AM
  • How can I see how the job is scheduled, like by what time slice, the SP will be called?


    Many Thanks & Best Regards, HuaMin Chen
    Wednesday, April 14, 2010 9:08 AM
  • In SQL Server Agent, right click on job, View History.  You can see history by jobsteps.

    Check that the logic is correct.

    You may consider to put a loop delay in like:

    WAITFOR DELAY '00:00:02';
    

    For concurrency control, here is an article:

    http://www.sqlusa.com/articles2005/rowversion/

    As mentioned above, SSIS maybe a better solution with extensive control and error logging features.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, April 14, 2010 12:14 PM
  • Many thanks Kalman.

    1. within the DB, I can see tables, views and etc. I do not see any Job option there. I'm using management studio express.
    2. Do you mean we can put WAITFOR within the loop?
    3. Is it true that heavy scheduled jobs can lead to anything abnormal?
    4. Is it really suitable for that I should perform the scheduled SP through one SSIS package?

     


     

    Many Thanks & Best Regards, HuaMin Chen

    Thursday, April 15, 2010 2:56 AM
  • You may have to ask these question at the Express forum. I am not familiar with Express other than it is a free limited features version.

    http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/threads


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Thursday, April 15, 2010 5:12 AM
  • OK thanks. But could you pls advise for other questions?
    Many Thanks & Best Regards, HuaMin Chen
    Thursday, April 15, 2010 7:22 AM