SQL Server - Transaction is getting locked in case of error

0
votes
BEGIN TRANSACTION;
BEGIN TRY
    ALTER TABLE dbo.SomeLogs
    ADD SomeID NVARCHAR(250) NULL

    ALTER TABLE dbo.SomeLogs
    ADD SomeID NVARCHAR(250) NULL
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH 
    ROLLBACK TRANSACTION;
    DECLARE @Msg NVARCHAR(MAX);
    SELECT @Msg = ERROR_MESSAGE();
    RAISERROR('Error Occured: %s', 20, 101, @Msg) WITH LOG;
END CATCH;

Running the above query gives the following error which is correct as I am trying to add same column twice.

Msg 2705, Level 16, State 4, Line 6 Column names in each table must be unique. Column name 'SomeID' in table 'dbo.SomeLogs' is specified more than once.

But the problem is that the SomeLogs table is locked. When I try to do SELECT on SomeLogs I get this error.

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

ADDITIONAL INFORMATION:

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3000&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

Why is the catch block not catching this error? And how to avoid table getting locked?

3
sqlsql-serversql-server-2008transactionslocking
do you have to add a new field everytime you run the script or this is only a one time script? – RoMEoMusTDiE
@maSTAShuFu - if i remove that field then there is no error and transaction won't be locked. But I am trying to simulate the scenario when there is an error and see whether it gets to CATCH or not. It doesn't. – Varun Sharma
@maSTAShuFu - Its a one time script but i want to make sure if there is an error for some unknown reason, it won't lock the tables. – Varun Sharma

3 Answers

1
votes
Try..Catch

block will not catch this error because it's compilation error, and compilation errors cannot be catched within the current scope. The table remains locked because locks are not released until transaction is committed or rolled back. When xact_abort is set to off(default for SSMS sessions) transaction is not rolled backed when the compilation error occurs, that is by (bad!) design, and to fix this you should use 

set xact_abort on;

You can catch this error in outer scope, for example, if you wrap this code in stored procedure or dynamic code, executing sp/dynamic code within try..catch

1
votes

Using TRY…CATCH with XACT_STATE

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

The following example shows how to use the TRY…CATCH construct to handle errors that occur inside a transaction. The XACT_STATE function determines whether the transaction should be committed or rolled back. In this example, SET XACT_ABORT is ON. This makes the transaction uncommittable when the constraint violation error occurs.

1
votes

You can specify SET XACT_ABORT ON to automatically rollback the transaction in the event of an error or attention (i.e. client query cancel or timeout). The general TRY/CATCH pattern I suggest in stored procedures and batches in SQL 2012 and later:

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    --do stuff
    COMMIT;
END TRY
BEGIN CATCH 
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

See Erland Sommarskog's error handling article for detailed explanation.