Use transactions for select statements

Transactions are usually when you have insert, update or delete statements with the “atomic” behaviour. However, in a highly concurrent application, it could happen that data you’ve read is already modified.

In this situation, you need to use a transaction to wrap your selects with the correct isolation level.

It’s usually advisable to set the deadlock property to low to avoid impacting behaviours other than select.


...

CREATE procedure [Event].[ReadEvents]
...
  @startDate datetime = NULL,
  @endDate datetime = NULL,
...
  
  AS
begin

...

  set nocount on;
  set deadlock_priority low;
  set transaction isolation level serializable;

  begin transaction;
 
...
    
commit transaction;    
    
end

GO

Use transactions for select statements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s