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.
dBforums
>
Unusual Blocking - Please Help Explain
03-31-08, 22:48
Linear Mode
