Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    4

    Unanswered: Unusual Blocking - Please Help Explain

    I encountered an unusual blocking lately that I can't explain. Theoritically, this shouldn't happen but it does.

    Suppose that I have the procedure below.

    Code:
    create proc my_procedurename
    as
    begin
    
        INSERT INTO table_t1(field1, field2)
        SELECT a.field1, b.field2
        FROM   table_s1 a, table_s2 b
        WHERE a.field1 = b.field1
    
        INSERT INTO table_t2(field1, field2)
        SELECT a.field1, b.field2
        FROM   table_s1 a, table_s3 b
        WHERE a.field1 = b.field1
    
        INSERT INTO table_t3(field1, field2)
        SELECT a.field1, b.field2
        FROM   table_s1 a, table_s4 b
        WHERE a.field1 = b.field1
    
        INSERT INTO table_t4(field1, field2)
        SELECT a.field1, b.field2
        FROM   table_s1 a, table_s5 b
        WHERE a.field1 = b.field1
    
        INSERT INTO table_t5(field1, field2)
        SELECT a.field1, b.field2
        FROM   table_s1 a, table_s6 b
        WHERE a.field1 = b.field1
    
    end
    When this type of stored proc is running before and there's a blocking/locking, I can see which part of the sp is choking which could easily tell me what to optimize. here's what I see before in sp_mon

    Code:
    577	USERNAME	DB_NAME	TABLE_T3	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_S1	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_S4	INSERT	880	10889
    That will tell me that it is the third statement in the SP that's having problem.

    But now, what happens is that all the tables inside the SP is blocked by the same process as below.
    Code:
    577	USERNAME	DB_NAME	TABLE_T1	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_T2	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_T3	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_T4	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_T5	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_S1	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_S2	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_S3	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_S4	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_S5	INSERT	880	10889
    577	USERNAME	DB_NAME	TABLE_S6	INSERT	880	10889
    Since I'm not using TRANSACTION, I expect that the holding of tables would be on a per statement only. So if the erroneous statement is using 3 tables, only those tables will be held/locked by the process and the SP will stop there. But in this case, the procedure moved to the next statement even if the previous statement isn't finished yet. It has become asynchronous when it should have not been.

    And one thing more, it does not happen all day. It's like somethin will trigger this and I can't find what that is.

    Additional Details :
    Sybase ASE 12.5.4 running on UNIX.

    thanks to all who read this post especially to those who will answer.

  2. #2
    Join Date
    Mar 2008
    Posts
    4
    hi guys! this problem has been bugging us for almost a month already. nobody from our DBA team can answer and no prospect in the horizon.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    What is the transaction-mode of the specified procedure?

    sp_procxmode my_procedurename

  4. #4
    Join Date
    Mar 2008
    Posts
    4
    thanks for the reply. transaction mode is Unchained.

  5. #5
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Check for locks (sp_lock), check the code that process 880 (that's the spid of the process that is blocking you, right?) is executing.

    Given the inserts, it could be an effect of iindex management (in case of APL tables), or other side effect.

    Michael

  6. #6
    Join Date
    Mar 2008
    Posts
    4
    Hi mpeppler, 880 is the CPU Time and it's just a sample. Process id 577 is not locked nor blocked by any other process. no begin tran/commit tran statement.

    my main concern is why it seemed that the statements were done asynchronously. I think that's the sympton I have to dig in but don't have the knowledge to go further.

Posting Permissions

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