SQL Transaction Anomaly

Problem:  When performing a transaction within a TRY..CATCH block, part of the instructions within the transaction gets executed even if an error occurs.  This anomaly breaks the ACID rules of atomicity.  A solution or workaround is needed to keep transactions in an ACID state.

Workaround:  Though the reasons at this time cannot be explained, the work around is to declare variables within the TRY..CATCH block and NOT outside the TRY..CATCH block.  By declaring within the TRY..CATCH block, the code follows the rules of ACID and errors are CAUGHT within the CATCH statement.

  1. Problem
    1. Begin by creating the database, Transactions, and run this script below:  This script will be called A1.

      USE Transactions;

      IF     EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stocks]’) AND type in (N’U’))
      DROP TABLE [dbo].[Stocks];

      CREATE TABLE dbo.Stocks (

      StockID int IDENTITY(1,1),

      StockName varchar(50),

      OpenPrice money,

      ClosePrice money

      )

      ;

      INSERT INTO dbo.Stocks

      SELECT ‘Walmart’,21.58,22.98 UNION

      SELECT ‘Target’,17.32,15.23 UNION

      SELECT ‘Taco Bell’,4.58,12.98 UNION

      SELECT ‘Microsoft’,7.15,8.15 UNION

      SELECT ‘Apple’,10.79,9.89
      ;

    2. Now run this script below to observe the anomaly.    This script will be called A2.


      DECLARE @StockName VARCHAR(50) = ‘Spencer’
      DECLARE @NewOpenPrice MONEY = 10000000000000000000000000.11
      DECLARE @NewClosePrice MONEY = 9.22BEGIN TRYSET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTIONIF EXISTS (   SELECT        StockNameFROM          [dbo].[Stocks]WHERE         StockName = @StockName)

      BEGIN

      UPDATE [dbo].[Stocks]

      SET           OpenPrice = @NewOpenPrice, ClosePrice = @NewClosePrice

      WHERE  StockName = @StockName

      END

      ELSE

      BEGIN

      INSERT INTO [dbo].[Stocks]

      VALUES (@StockName, @NewOpenPrice, @NewClosePrice)

      END

      COMMIT TRANSACTION

      END TRY

      BEGIN CATCH

      BEGIN TRANSACTION

      ROLLBACK TRANSACTION

      PRINT ‘You broke it, but I fixed it’

      END CATCH

      SELECT        *

      FROM          [dbo].[Stocks]

    3. You will notice the following error message.
      SqlTransactionAnomalyA3
    4. The error message was expected because the value for @NewOpenPrice does not meet the constraint requirements of the datatype, MONEY.  However, notice that the error is never caught and the code performs the transaction.  Click the Results tab and view the data as shown below.  Instead of rolling back as designed, the code compensates by giving a NULL value where the error occurred.   This result is unacceptable.
      SqlTransactionAnomalyA4
    5. Now reset [dbo].[Stocks] by running the script in A1.   After doing that, we’ll hardcode values in the A2 script to force the CATCH block to execute.  Go to the ELSE statement in the A2 script and instead of receiving the values from the variables, hardcode THE SAME values in this statement as shown below.
      SqlTransactionAnomalyA5
    6. Run the script.  Notice that the CATCH block executes, rolling back the transaction and gives out the error message.
      SqlTransactionAnomalyA6
    7. Click the results and observe that no data was inserted into the table.  That’s because the transaction was ROLLED BACK.
      SqlTransactionAnomalyA7
    8. Why did the transaction go through when the values of the variables were erroneous, but when the same values are hardcoded in the logic, the error was caught and passed to the CATCH block?  Unfortunately, there isn’t a logical explanation at this time but there is a workaround to have this logic follow the ACID rules.   Please see the section:  Workaround.
  2. Workaround
    1. Rerun the script A1 to reset [dbo].[Stocks] to its original state.

      USE Transactions;IF     EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stocks]’) AND type in (N’U’))DROP TABLE [dbo].[Stocks];CREATE TABLE dbo.Stocks (StockID int IDENTITY(1,1),StockName varchar(50),OpenPrice money,ClosePrice money)

      ;

      INSERT INTO dbo.Stocks

      SELECT ‘Walmart’,21.58,22.98 UNION

      SELECT ‘Target’,17.32,15.23 UNION

      SELECT ‘Taco Bell’,4.58,12.98 UNION

      SELECT ‘Microsoft’,7.15,8.15 UNION

      SELECT ‘Apple’,10.79,9.89
      ;

    2. Now run this script below.  It uses the same logic as script A2 above, but declares the variables INSIDE the TRY..CATCH block.  Make sure that the declared variables are INSIDE the TRY..CATCH block and OUTSIDE the TRANSACTION statements.

      BEGIN TRYDECLARE @StockName VARCHAR(50) = ‘Spencer’
      DECLARE @NewOpenPrice money = 10000000000000000000000000.11
      DECLARE @NewClosePrice money = 9.22SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTIONIF EXISTS (   SELECT        StockNameFROM          [dbo].[Stocks]WHERE         StockName = @StockName)BEGIN

      UPDATE [dbo].[Stocks]

      SET    OpenPrice = @NewOpenPrice, ClosePrice = @NewClosePrice

      WHERE  StockName = @StockName

      END

      ELSE

      BEGIN

      INSERT INTO [dbo].[Stocks]

      VALUES (@StockName, @NewOpenPrice, @NewClosePrice)

      END

      COMMIT TRANSACTION

      END TRY

      BEGIN CATCH

      BEGIN TRANSACTION

      ROLLBACK TRANSACTION

      PRINT ‘You broke it, but I fixed it’

      END CATCH

      SELECT        *

      FROM          [dbo].[Stocks]

    3. You should get these results below.  Notice that the insert never took place.  Now Click the Messages tab.
      SqlTransactionAnomalyB3
    4. Notice that the message of the CATCH block was printed out, meaning that a ROLL BACK did take place.
      SqlTransactionAnomalyB4
    5. As a final test, change the value of @NewOpenPrice to 1000.11 as shown in this script below.  This data is acceptable and meets the constraints of the data types.
      SqlTransactionAnomalyB5
    6. Now run the script and you should get a new row added into the Stocks table.
      SqlTransactionAnomalyB6
    7. Click the Messages tab to be sure the CATCH block wasn’t executed.  It should appear like this below.  This concludes the demonstration of the problem and a workaround to the SQL Transaction Anomaly.
      SqlTransactionAnomalyB7

Leave a Reply