May 11, 2011 at 7:42 am
Hi Friends,
One of the servers I work as DBA has recently reported performance problem. We found out sessions in sleeping state with open transactions.
We further analyszed the stored procedure which was running. Following is the sample code.
BEGIN TRAN TRAN_NAME
BEGIN TRY
DELETE from TABLE A
DELETE from TABLE B
UPDATE TABLE C
IF XACT_STATE()=1
BEGIN
COMMIT TRAN TRAN_NAME
END
ELSE IF XACT_STATE()=-1
BEGIN
ROLLBACK TRAN TRAN_NAME
END
END TRY
BEGIN CATCH
Declare @ErrorMsg varchar(300);
set @ErrorMsg='SOME_TEXT' + @@ERROR;
IF XACT_STATE()=-1
ROLLBACK TRAN TRAN_NAME
RAISERROR (@ErrorMsg,18,6)
END CATCH
--
Since my understanding on XACT_STATE() is limited, I cannot find out what is the issue will the catch block will have XACT_STATE()=1? Is this the problem?
Thanks in advance
May 12, 2011 at 8:28 am
Your assumption is correct. The XACT_STATE() can be 1 or -1.
You need to test for XACT_STATE != 0
The probability of survival is inversely proportional to the angle of arrival.
May 13, 2011 at 4:41 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply