In my production environment, I have a stored procedure which was called by an application several time. This stored procedure was used to return a new sequence number for the 'key' passed as an argument. For example, if appliication needs to add a new customer record, this stored procedure was called and returned a new cust_num for the new record. The 'last_sequence_oid' table has the column keyname and the column storing the last sequence number used for the 'keyname'.
Recently, we experienced a blocking problem with the 'last_sequence_oid' table. By examing the sysprocesses table, I found that the blocker was the apps calling the stored procedure. The blocker sysprocesses record has shown that the blocked column was 0, waittime was 0, waittype was 0x000, and the status was 'AWATING FOR COMMAND'.
If I killed the application process, all the login sessions blocked were able to continue.
We could not reproduce the blocking problem in test environment. And blocking problem occasionally occurred in production environment. SQL Profiler trace did not show errors or exceptions.
Can anyone share his experience with me to investigate this problem.
Regards,
-- Peter Lo
Thanks
Do you know the bug #? and where I can obtain the patch
