title: SQL Server - Transaction is getting locked in case of error
body: <pre><code>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;
  </code></pre>
  
  <p>Running the above query gives the following error which is correct as I am trying to add same column twice.</p>
  
  <p>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.</p>
  
  <p><strong>But the problem is that the SomeLogs table is locked. When I try to do SELECT on SomeLogs I get this error.</strong></p>
  
  <hr>
  
  <p>Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)</p>
  
  <p>For help, click: <a href="http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;LinkId=20476" rel="nofollow noreferrer">http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;LinkId=20476</a></p>
  
  <hr>
  
  <p>ADDITIONAL INFORMATION:</p>
  
  <p>Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)</p>
  
  <p>For help, click: <a href="http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;ProdVer=11.00.3000&amp;EvtSrc=MSSQLServer&amp;EvtID=1222&amp;LinkId=20476" rel="nofollow noreferrer">http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&amp;ProdVer=11.00.3000&amp;EvtSrc=MSSQLServer&amp;EvtID=1222&amp;LinkId=20476</a></p>
  
  <hr>
  
  <p><strong>Why is the catch block not catching this error?</strong> <strong>And how to avoid table getting locked?</strong></p>