March 8, 2004 at 9:26 am
I assume you need to solve the problem and can then take time to analyse and discuss the problem.
First of all, locate all instances in your C++ code where you embed SQL (ie. look for the DELETE statement in the C++ code)
Second, rewrite the code to use the stored procedure version of the delete statement.
Third, alter the stored procedure to do the following:
LABEL1:
IF EXISTS(SELECT *
FROM tempdb..sysobjects
WHERE [name] = 'INSTANCE_REF_DELETEINPROGRESS'
AND [type] = 'U')
BEGIN
WAITFOR DELAY '000:00:01'
GOTO LABEL1
END
CREATE TABLE Tempdb..INSTANCE_REF_DELETEINPROGRESS
(InProgress tinyint)
*******************************************************************
BEGIN TRAN T1
Your normal code here
COMMIT TRAN T1
*******************************************************************
IF EXISTS(SELECT *
FROM tempdb..sysobjects
WHERE [name] = 'INSTANCE_REF_DELETEINPROGRESS'
AND [type] = 'U')
DROP TABLE tempdb..Tempdb..INSTANCE_REF_DELETEINPROGRESS
If you are familiar with C++ then you understand the concept of a mutex. That is what this code duplicates where the existance of the temp table is the mutex 'ON' state and the non-existence of the tempdb table is the mutex 'OFF' state.
Once this works and your users are happy then you can continue the academics of deadlocks and design a better solution.
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply