SQL Talk

What is Concurrency in SQL Server?

When we talk about transactions, one more thing which we need to handle is concurrency. Concurrency is nothing but is a situation where two users are trying to access the same information and while they are accessing the same information we do not want any kind of inconsistency result or abnormal behavior.

Databases are powerful systems and are potentially used by many users or applications at the same time. Allowing concurrent transactions is essential for performance but may introduce concurrency issues when two or more transactions are working with the same data at the same time.

Some of the common concurrency problems

• Dirty Reads
• Lost Updates
• Nonrepeatable Reads
• Phantom Reads

One way to solve all these concurrency problems is by allowing only one user to execute, only one transaction at any point in time. Imagine what could happen if you have a large database with several users who want to execute several transactions. All the transactions get queued and they may have to wait a long time before they could get a chance to execute their transactions. So you are getting poor performance and the whole purpose of having a powerful database system is defeated if you serialize access this way.

At this point you might be thinking, for best performance let us allow all transactions to execute concurrently. The problem with this approach is that it may cause all sorts of concurrency problems (i.e Dirty Reads, Lost Updates, Nonrepeatable Reads, Phantom Reads) if two or more transactions work with the same data at the same time.

1. Dirty Reads

A dirty read happens when one transaction is permitted to read data that has been modified by another transaction that has not yet been committed. In most cases this would not cause a problem. However, if the first transaction is rolled back after the second reads the data, the second transaction has dirty data that does not exist anymore.

Dirty Read Example : In the example below, Transaction 1, updates the value of ItemsInStock to 9. Then it starts to bill the customer. While Transaction 1 is still in progress, Transaction 2 starts and reads ItemsInStock value which is 9 at the moment. At this point, Transaction 1 fails because of insufficient funds and is rolled back. The ItemsInStock is reverted to the original value of 10, but Transaction 2 is working with a different value (i.e 10).



Transaction 1 : 
Begin Tran

Update tblInventory set ItemsInStock = 9 where Id=1

-- Billing the customer

Waitfor Delay '00:00:15'

-- Insufficient Funds. Rollback transaction

Rollback Transaction

Transaction 2 :

Set Transaction Isolation Level Read Uncommitted

Select * from tblInventory where Id=1

Read Uncommitted transaction isolation level is the only isolation level that has dirty read side effect. This is the least restrictive of all the isolation levels. When this transaction isolation level is set, it is possible to read uncommitted or dirty data. Another option to read dirty data is by using NOLOCK table hint. The query below is equivalent to the query in Transaction 2.

Select * from tblInventory (NOLOCK) where Id=1

2. Lost Updates

Lost update problem happens when 2 transactions read and update the same data. Let's understand this with an example. We will use the following table tblInventory for this example.



As you can see in the diagram below there are 2 transactions - Transaction 1 and Transaction 2. Transaction 1 starts first, and it is processing an order for 1 iPhone. It sees ItemsInStock as 10.

At this time Transaction 2 is processing another order for 2 iPhones. It also sees ItemsInStock as 10. Transaction 2 makes the sale first and updates ItemsInStock with a value of 8.

At this point Transaction 1 completes the sale and silently overwrites the update of Transaction 2. As Transaction 1 sold 1 iPhone it has updated ItemsInStock to 9, while it actually should have updated it to 7.



Example : The lost update problem example. Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window, execute Transaction 2 code. Transaction 1 is processing an order for 1 iPhone, while Transaction 2 is processing an order for 2 iPhones. At the end of both the transactions ItemsInStock must be 7, but we have a value of 9. This is because Transaction 1 silently overwrites the update of Transaction 2. This is called the lost update problem.

-- Transaction 1

Begin Tran

Declare @ItemsInStock int

Select @ItemsInStock = ItemsInStock

from tblInventory where Id=1

-- Transaction takes 10 seconds

Waitfor Delay '00:00:10'

Set @ItemsInStock = @ItemsInStock - 1

Update tblInventory

Set ItemsInStock = @ItemsInStock where Id=1

Print @ItemsInStock

Commit Transaction

-- Transaction 2

Begin Tran

Declare @ItemsInStock int

Select @ItemsInStock = ItemsInStock

from tblInventory where Id=1

-- Transaction takes 1 second

Waitfor Delay '00:00:1'

Set @ItemsInStock = @ItemsInStock - 2

Update tblInventory

Set ItemsInStock = @ItemsInStock where Id=1

Print @ItemsInStock

Commit Transaction

Both Read Uncommitted and Read Committed transaction isolation levels have the lost update side effect. Repeatable Read, Snapshot, and Serializable isolation levels does not have this side effect. If you run the above Transactions using any of the higher isolation levels (Repeatable Read, Snapshot, or Serializable) you will not have lost update problem. The repeatable read isolation level uses additional locking on rows that are read by the current transaction, and prevents them from being updated or deleted elsewhere. This solves the lost update problem.



For both the above transactions, set Repeatable Read Isolation Level. Run Transaction 1 first and then a few seconds later run Transaction 2. Transaction 1 completes successfully, but Transaction 2 competes with the following error.
Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

3. Nonrepeatable Reads
Non repeatable read problem happens when one transaction reads the same data twice and another transaction updates that data in between the first and second read of transaction one. 

We will use the following table tblInventory in this demo


The following diagram explains the problem : Transaction 1 starts first. Reads ItemsInStock. Gets a value of 10 for first read. Transaction 1 is doing some work and at this point Transaction 2 starts and UpdatesItemsInStock to 5. Transaction 1 then makes a second read. At this point Transaction 1 gets a value of 5, reulting in non-repeatable read problem.



Non-repeatable read example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window, execute Transaction 2 code. Notice that when Transaction 1 completes, it gets different values for read 1 and read 2, resulting in non-repeatable read.

-- Transaction 1

Begin Transaction

Select ItemsInStock from tblInventory where Id = 1

-- Do Some work

waitfor delay '00:00:10'

Select ItemsInStock from tblInventory where Id = 1

Commit Transaction

-- Transaction 2

Update tblInventory set ItemsInStock = 5 where Id = 1

Repeatable read or any other higher isolation level should solve the non-repeatable read problem.


Fixing non repeatable read concurrency problem : To fix the non-repeatable read problem, set transaction isolation level of Transaction 1 to repeatable read. This will ensure that the data that Transaction 1 has read, will be prevented from being updated or deleted elsewhere. This solves the non-repeatable read problem. 

When you execute Transaction 1 and 2 from 2 different instances of SQL Server management studio, Transaction 2 is blocked until Transaction 1 completes and at the end of Transaction 1, both the reads get the same value for ItemsInStock.

-- Transaction 1

Set transaction isolation level repeatable read

Begin Transaction

Select ItemsInStock from tblInventory where Id = 1

-- Do Some work

waitfor delay '00:00:10'

Select ItemsInStock from tblInventory where Id = 1

Commit Transaction

-- Transaction 2

Update tblInventory set ItemsInStock = 5 where Id = 1

4. Phantom Reads

Phantom read happens when one transaction executes a query twice and it gets a different number of rows in the result set each time. This happens when a second transaction inserts a new row that matches the WHERE clause of the query executed by the first transaction.

We will use the following table tblEmployees in this demo

Scrip to create the table tblEmployees

Create table tblEmployees

(

    Id int primary key,

    Name nvarchar(50)

)

Go

Insert into tblEmployees values(1,'Mark')

Insert into tblEmployees values(3, 'Sara')

Insert into tblEmployees values(100, 'Mary')

The following diagram explains the problem : Transaction 1 starts first. Reads from Emp table where Id between 1 and 3. 2 rows retrieved for first read. Transaction 1 is doing some work and at this point Transaction 2 starts and inserts a new employee with Id = 2. Transaction 1 then makes a second read. 3 rows retrieved for second read, reulting in phantom read problem.



Phantom read example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window, execute Transaction 2 code. Notice that when Transaction 1 completes, it gets different number of rows for read 1 and read 2, resulting in phantom read.

-- Transaction 1

Begin Transaction

Select * from tblEmployees where Id between 1 and 3

-- Do Some work

waitfor delay '00:00:10'

Select * from tblEmployees where Id between 1 and 3

Commit Transaction

-- Transaction 2

Insert into tblEmployees values(2, 'Marcus')

Serializable or any other higher isolation level should solve the phantom read problem.


Fixing phantom read concurrency problem : To fix the phantom read problem, set transaction isolation level of Transaction 1 to serializable. This will place a range lock on the rows between 1 and 3, which prevents any other transaction from inserting new rows with in that range. This solves the phantom read problem. 

When you execute Transaction 1 and 2 from 2 different instances of SQL Server management studio, Transaction 2 is blocked until Transaction 1 completes and at the end of Transaction 1, both the reads get the same number of rows.

-- Transaction 1

Set transaction isolation level serializable

Begin Transaction

Select * from tblEmployees where Id between 1 and 3

-- Do Some work

waitfor delay '00:00:10'

Select * from tblEmployees where Id between 1 and 3

Commit Transaction

-- Transaction 2

Insert into tblEmployees values(2, 'Marcus')

Read below points:

THROW

RAISE ERROR

TRY_ CATCH

NESTED TRY CATCH

@@TRANCOUNT
XACT_STATE
XACT_ABORT

What is Exception handling in SQL Server?

Some errors are generated by the application at runtime. These errors can be generated because of various reasons and the application is not able to handle these errors, causing the program to break and throw an exception.

Usually, these are generated because of unexpected human input such as passing an unexpected data type input e.g. passing an integer at the place of a string value, dividing a number by zero, etc.

But in real-life scenarios, the program is not supposed to be broken down, but execute flawlessly. Therefore, there is a need for a mechanism that can handle the situations that affect the program’s normal execution or cause it to break. This mechanism is called exception handling or error handling.

Error handling in SQL Server gives us control over the Transact-SQL code. For example, when things go wrong, we get a chance to do something about it and possibly make it right again. SQL Server error handling can be as simple as just logging that something happened, or it could be us trying to fix an error. It can even be translating the error in SQL language because we all know how technical SQL Server error messages could get making no sense and hard to understand. Luckily, we have a chance to translate those messages into something more meaningful to pass on to the users, developers, etc.

SQL Server also provides the exception handling mechanism like any other programming language. There are different ways to handle the exceptions. We will discuss these in the upcoming sections with the help of examples.

Consider the following program which divides the two number:

USE [master]
GO
DECLARE
	@Number1 int,
	@Number2 int,
	@Result real

SET @Number1=10
SET @Number2=0

SET @Result=@Number1/@Number2
PRINT(@Result)

Types of exceptions in SQL Server

1. System Defined Exceptions
2. User-Defined Exceptions

System Defined Exceptions: These are the exceptions that are generated by the system during the program execution. The error messages are predefined in the system and the final user will see the predefined message on the console.

User-Defined Exceptions: These are the exceptions that are thrown explicitly by the programmer. The programmer can raise exceptions at any part of the SQL code according to the logic he has implemented in the program.

The programmer can also create custom messages that he finds will be interactive and meaningful to the user. He can also store these errors into log tables to maintain a log of generated exceptions.

Raising Errors with RAISERROR

1RAISERROR (N'This is message.', -- Message text. 
210, -- Severity, 
31); -- Second argument. 
4GO 

THROW 
251000, -- error number
3'This is not a valid value for unit price.', -- message
41; --state
5GO 

1Msg 51000, Level 16, State 1, Line 1 
2This is not a valid value for unit price.

Select @@ERROR

1RAISERROR(N'Message', 16, 1);
2IF @@ERROR <> 0
3PRINT 'Error=' + CAST(@@ERROR AS VARCHAR(8));
4GO

1Msg 50000, Level 16, State 1, Line 1
2Message
3Error=0

1DECLARE @Value int;
2RAISERROR(N'Message', 16, 1);
3SET @Value = @@ERROR;
4IF @Value <> 0
5PRINT 'Error=' + CAST(@Value AS VARCHAR(8));

1Msg 50000, Level 16, State 1, Line 2
2Message
3Error=50000

Try Catch in SQL Server

• We put the SQL statements that may cause an exception in the BEGIN TRY block.
• If any exception arises, the control is immediately transferred to the BEGIN CATCH block.
• In the BEGIN CATCH block, we write the statements that should be executed if an exception is occurred in the BEGIN TRY block. That means the statements inside the BEGIN CATCH block are used for handling the flow of the program in case the exception is triggered.
• If no exception arises i.e. all the SQL statements inside the BEGIN TRY block are executed, the control is not transferred to the BEGIN CATCH block.

Now let us see the general syntax for BEGIN TRY and BEGIN CATCH block.

• BEGIN TRY  
     -- statements that may cause exceptions
  END TRY  
  Code language: SQL (Structured Query Language) (sql)

  Then you use a BEGIN CATCH...END CATCH block immediately after the TRY block:

  BEGIN CATCH  
     -- statements that handle exception
  END CATCH  
  Code language: SQL (Structured Query Language) (sql)

  The following illustrates a complete TRY CATCH construct:

  BEGIN TRY  
     -- statements that may cause exceptions
  END TRY 
  BEGIN CATCH  
     -- statements that handle exception
  END CATCH  
  Code language: SQL (Structured Query Language) (sql)

  If the statements between the TRY block complete without an error, the statements between the CATCH block will not execute. However, if any statement inside the TRY block causes an exception, the control transfers to the statements in the CATCH block.

  The CATCH block functions

  Inside the CATCH block, you can use the following functions to get the detailed information on the error that occurred:

  • ERROR_LINE() returns the line number on which the exception occurred.
  • ERROR_MESSAGE() returns the complete text of the generated error message.
  • ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
  • ERROR_NUMBER() returns the number of the error that occurred.
  • ERROR_SEVERITY() returns the severity level of the error that occurred.
  • ERROR_STATE() returns the state number of the error that occurred.

  Note that you only use these functions in the CATCH block. If you use them outside of the CATCH block, all of these functions will return NULL.

  Nested TRY CATCH constructs

  You can nest TRY CATCH construct inside another TRY CATCH construct. However, either a TRY block or a CATCH block can contain a nested TRY CATCH, for example:

  BEGIN TRY
      --- statements that may cause exceptions
  END TRY
  BEGIN CATCH
      -- statements to handle exception
      BEGIN TRY
          --- nested TRY block
      END TRY
      BEGIN CATCH
          --- nested CATCH block
      END CATCH
  END CATCH
  Code language: SQL (Structured Query Language) (sql)

  SQL Server TRY CATCH examples

  First, create a stored procedure named usp_divide that divides two numbers:

  CREATE PROC usp_divide(
      @a decimal,
      @b decimal,
      @c decimal output
  ) AS
  BEGIN
      BEGIN TRY
          SET @c = @a / @b;
      END TRY
      BEGIN CATCH
          SELECT  
              ERROR_NUMBER() AS ErrorNumber  
              ,ERROR_SEVERITY() AS ErrorSeverity  
              ,ERROR_STATE() AS ErrorState  
              ,ERROR_PROCEDURE() AS ErrorProcedure  
              ,ERROR_LINE() AS ErrorLine  
              ,ERROR_MESSAGE() AS ErrorMessage;  
      END CATCH
  END;
  GO
  Code language: SQL (Structured Query Language) (sql)

  In this stored procedure, we placed the formula inside the TRY block and called the CATCH block functions ERROR_* inside the CATCH block.

  Second, call the usp_divide stored procedure to divide 10 by 2:

  DECLARE @r decimal;
  EXEC usp_divide 10, 2, @r output;
  PRINT @r;
  Code language: SQL (Structured Query Language) (sql)

  Here is the output

  5
  Code language: SQL (Structured Query Language) (sql)

  Because no exception occurred in the TRY block, the stored procedure completed at the TRY block.

  Third, attempt to divide 20 by zero by calling the usp_divide stored procedure:

  DECLARE @r2 decimal;
  EXEC usp_divide 10, 0, @r2 output;
  PRINT @r2;
  Code language: SQL (Structured Query Language) (sql)

  The following picture shows the output:

  

  Because of division by zero error which was caused by the formula, the control was passed to the statement inside the CATCH block which returned the error’s detailed information.
  
  

  SQL Serer TRY CATCH with transactions

  Inside a CATCH block, you can test the state of transactions by using the XACT_STATE() function.

  • If the XACT_STATE() function returns -1, it means that an uncommittable transaction is pending, you should issue a ROLLBACK TRANSACTION statement.
  • In case the XACT_STATE() function returns 1, it means that a committable transaction is pending. You can issue a COMMIT TRANSACTION statement in this case.
  • If the XACT_STATE() function return 0, it means no transaction is pending, therefore, you don’t need to take any action.

  It is a good practice to test your transaction state before issuing a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement in a CATCH block to ensure consistency.

  Using TRY CATCH with transactions example

  First, set up two new tables sales.persons and sales.deals for demonstration:

  CREATE TABLE sales.persons
  (
      person_id  INT
      PRIMARY KEY IDENTITY, 
      first_name NVARCHAR(100) NOT NULL, 
      last_name  NVARCHAR(100) NOT NULL
  );
  
  CREATE TABLE sales.deals
  (
      deal_id   INT
      PRIMARY KEY IDENTITY, 
      person_id INT NOT NULL, 
      deal_note NVARCHAR(100), 
      FOREIGN KEY(person_id) REFERENCES sales.persons(
      person_id)
  );
  
  insert into 
      sales.persons(first_name, last_name)
  values
      ('John','Doe'),
      ('Jane','Doe');
  
  insert into 
      sales.deals(person_id, deal_note)
  values
      (1,'Deal for John Doe');
  Code language: SQL (Structured Query Language) (sql)

  Next, create a new stored procedure named usp_report_error that will be used in a CATCH block to report the detailed information of an error:

  CREATE PROC usp_report_error
  AS
      SELECT   
          ERROR_NUMBER() AS ErrorNumber  
          ,ERROR_SEVERITY() AS ErrorSeverity  
          ,ERROR_STATE() AS ErrorState  
          ,ERROR_LINE () AS ErrorLine  
          ,ERROR_PROCEDURE() AS ErrorProcedure  
          ,ERROR_MESSAGE() AS ErrorMessage;  
  GO
  Code language: SQL (Structured Query Language) (sql)

  Then, develop a new stored procedure that deletes a row from the sales.persons table:

  CREATE PROC usp_delete_person(
      @person_id INT
  ) AS
  BEGIN
      BEGIN TRY
          BEGIN TRANSACTION;
          -- delete the person
          DELETE FROM sales.persons 
          WHERE person_id = @person_id;
          -- if DELETE succeeds, commit the transaction
          COMMIT TRANSACTION;  
      END TRY
      BEGIN CATCH
          -- report exception
          EXEC usp_report_error;
          
          -- Test if the transaction is uncommittable.  
          IF (XACT_STATE()) = -1  
          BEGIN  
              PRINT  N'The transaction is in an uncommittable state.' +  
                      'Rolling back transaction.'  
              ROLLBACK TRANSACTION;  
          END;  
          
          -- Test if the transaction is committable.  
          IF (XACT_STATE()) = 1  
          BEGIN  
              PRINT N'The transaction is committable.' +  
                  'Committing transaction.'  
              COMMIT TRANSACTION;     
          END;  
      END CATCH
  END;
  GO
  Code language: SQL (Structured Query Language) (sql)

  In this stored procedure, we used the XACT_STATE() function to check the state of the transaction before performing COMMIT TRANSACTION or ROLLBACK TRANSACTION inside the CATCH block.

  After that, call the usp_delete_person stored procedure to delete the person id 2:

  EXEC usp_delete_person 2;
  Code language: SQL (Structured Query Language) (sql)

  There was no exception occurred.

  Finally, call the stored procedure usp_delete_person to delete person id 1:

  EXEC usp_delete_person 1;
  Code language: SQL (Structured Query Language) (sql)

  The following error occurred:

  

  In this tutorial, you have learned how to use the SQL Server TRY CATCH construct to handle exceptions in stored procedures.

Throw custom exception in SQL Server stored procedure

In SQL Server, you can also throw custom exceptions. Throwing a custom exception means you can cause an exception to encounter an error at any part of your code according to your logic.

We use the THROW statement or the RAISERROR function for throwing a custom exception. Both the functions do almost the same work. However, there are some differences between the two.

• The RAISERROR was introduced in SQL Server 7.0 and the THROW statement was introduced in the SQL Server 2012. Microsoft suggests using the THROW statement as it is easier to use and has more functionalities than the RAISERROR function.

• The RAISERROR function keeps the record of only the last exception thrown. However, it is not the case with the THROW statement. The THROW statement keeps the record of all the recent exceptions.

• You can also use the THROW function without using any parameters specified. But, you cannot do this with the RAISERROR function.
• The THROW statement threw an error. It takes three parameters:
  • Error number, which should be always greater than or equal to 50000 and less than the INT range.
  • Error message, which you want to show to the user. It should be enough informative to the user.
  • Error state, the current state of the error. It can have values between 0 to 255 as it supports the TINYINT data type.
• You can also use the THROW() function without any parameter to throw an exception and transfer the control to the CATCH block, but there must be a CATCH block to handle the exception.
  

Note

1. A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.

2. The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.

3. Each TRY block is associated with only one CATCH block and vice versa

4. TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.

5. TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.

6. XACT_STATE function within the TRY..CATCH block can be used to check whether an open transaction is committed or not. It will return -1 if the transaction is not committed else returns 1.