TRY/CATCH block doesn't work properly for some errors - by Ihor Kornelyuk

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 691607 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/29/2011 3:52:23 AM
Access Restriction Public

Description

1)When using BEGIN TRY/BEGIN CATCH code blocks, in some situations when an error is raised in the TRY block, the batch stops execution and ignores both the CATCH block and all subsequent statements after it. 
2) Also, if this error is in the scope of a transaction, all changes before it are automatically commited.
Sign in to post a comment.
Posted by Amarnath Naganathan on 7/21/2014 at 4:24 AM
could you please give me the solution how to unlock the database automatically if error occur in try block ? i have COMMIT TRANSACTION statement at the end of try block. As error occurs before COMMIT STATEMENT, the statement is not executed...
Posted by Amarnath Naganathan on 7/21/2014 at 4:21 AM
Hi,
I got the same problem... if error occured in try block it ignores the subsequnt statement and also it locks the database.. so i could not able to run any statement against the same database...
Posted by Microsoft on 10/5/2011 at 2:45 PM
Hello Ihor,
The behavior is due to a mix of deferred name resolution and the statement recompilation. Since you are using temporary tables, when the batch is originally compiled any statements referring to temporary tables are deferred to runtime for compilation. When the batch is actually executed, when each statement referencing the temporary table is hit we do a recompilation of that particular statement. So the first insert will go through, the 2nd insert will be recompiled and that hits the binding error which aborts the batch. Hence you end up with one row and an open transaction.
Hope this helps. You can also look in MSDN for the plan cache whitepaper that describes the statement recompilation design.

--
Umachandar, SQL Programmability Team
Posted by Ihor Kornelyuk on 10/4/2011 at 1:47 AM
Hi,

Well, to be fair - my original example didn't include "go" statements, that you've added yourself.

I'll repost my code:

CREATE TABLE #TempTable
(
ID int IDENTITY(1,1),
Name nvarchar(50)
)
BEGIN TRANSACTION AAA;
BEGIN TRY
-- Generate a constraint violation error.
INSERT INTO #TempTable VALUES('AAA')
INSERT INTO #TempTable VALUES(2,'BBB')
COMMIT TRANSACTION AAA;
END TRY
BEGIN CATCH
SELECT 'ERROR'
IF @@TRANCOUNT > 0
BEGIN
SELECT 'ROLLED BACK'
ROLLBACK TRANSACTION AAA;
END
END CATCH;
SELECT 'HERE'
SELECT * FROM #TempTable
DROP TABLE #TempTable;
GO

Please run this code and then do a separate select:
SELECT * FROM #TempTable

The table should contain - "1"    "AAA"
Posted by Microsoft on 10/3/2011 at 2:44 PM
Hello Ihor,
The repro as you provided should not insert any rows into the temporary table at all. See below:

CREATE TABLE #TempTable
(
ID int IDENTITY(1,1),
Name nvarchar(50)
)
go
BEGIN TRANSACTION AAA;
BEGIN TRY
-- Generate a constraint violation error.
INSERT INTO #TempTable VALUES('AAA')
INSERT INTO #TempTable VALUES(2,'BBB')
COMMIT TRANSACTION AAA;
END TRY
BEGIN CATCH
SELECT 'ERROR'
IF @@TRANCOUNT > 0
BEGIN
SELECT 'ROLLED BACK'
ROLLBACK TRANSACTION AAA;
END
END CATCH;
go
SELECT 'HERE'
go
SELECT * FROM #TempTable
go
DROP TABLE #TempTable;
go

Perhaps your actual repro has different scopes or logic. If you post a working sample that demonstrates the issue I can explain the behavior.

--
Umachandar, SQL Programmability Team
Posted by Ihor Kornelyuk on 10/1/2011 at 12:39 AM
Ok, thanks for info. Still could you explain how #TempTable gets it's first row filled in if the statement was supposed to be aborted at compile time. And why does the 'AAA' transaction get commited?

Thanks.
Posted by Microsoft on 9/30/2011 at 12:46 PM
Closed as explained before.

--
Umachandar, SQL Programmability Team
Posted by Microsoft on 9/30/2011 at 12:03 PM
Hi Ihor,
Thanks for your feedback. The behavior you are seeing is by design. The identity insert error (8101) is actually raised during compilation process (more specifically binding of the statements in the batch which does name resolution and enforces other semantics). So the batch is never executed in the first place.
Generally speaking, we have several connect feedback items tracking improved error handling in the engine. Hopefully, we will be able to address this sometime in the future.
As for workaround, if the insert was executed inside dynamic SQL or a different scope like another proc then you can trap it like:

BEGIN TRANSACTION AAA;
BEGIN TRY
-- Generate a constraint violation error.
INSERT INTO #TempTable VALUES('AAA')
exec ('
INSERT INTO #TempTable VALUES(2,''BBB'')');
COMMIT TRANSACTION AAA;
END TRY
BEGIN CATCH
SELECT 'ERROR'
IF @@TRANCOUNT > 0
BEGIN
SELECT 'ROLLED BACK'
ROLLBACK TRANSACTION AAA;
END
END CATCH;

Hope this helps.

--
Umachandar, SQL Programmability Team