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.

[sourcecode language="sql"]

...

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

[/sourcecode]