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]
Comments