Using READ UNCOMMITTED with UNION ALL
I have a stored procedure under development with multiple operators UNION ALL
. This is historical data and I was prompted to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
. Putting aside the discussion of whether this is the correct method, I would like to know if I only need to specify this ISOLATION LEVEL at the top of the stored procedure, or if I need to specify it after each UNION ALL, since they are 'different queries?
Example:
Alter procedure dbo.ExampleProcedure as
declare @StartDate datetime
declare @EndDate datetime
insert into myDB.DBO.InboundCalls
select I.Date, I.System, Count(*) as calls
from
(select Date, System, CallID from System1CallData C
Left Join someothertables as S on C.CallID = S.CallID
where (C.date >= @StartDate and C.date < @EndDate)) as I
Group by I.Date, I.System
Union ALL
select I.Date, I.System, Count(*) as calls
from
(select Date, System, CallID from System2CallData C
Left Join someothertables as S on C.CallID = S.CallID
where (C.date >= @StartDate and C.date < @EndDate)) as I
group by I.Date, I.System
Union ALL
select I.Date, I.System, Count(*) as calls
from
(select Date, System, CallID from System3CallData C
Left Join someothertables as S on C.CallID = S.CallID
where (C.date >= @StartDate and C.date < @EndDate)) as I
Group by I.Date, I.System
Order by I.Date asc, I.System asc, calls asc
So, do I put SET TRANSACTION ISOLATION LEVEL
after Alter Procedure dbo.ExampleProcedure as
, or before the first SELECT
, or before each nested one SELECT
? Thanks in advance for any guidance!
source to share
I would like to know if this ISOLATION LEVEL only needs to be specified once at the top of the stored procedure ...
Only once at the top of the procedure, unless of course you switch isolation levels within the procedure. The isolation level returns to the previous level when SP exits.
If you issue a SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger when an object returns control, the isolation level is reset to the level in effect when the object was called. For example, if you install REPEATABLE READ in batch mode, and then the batch is called by a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting falls back to REPEATABLE READ when the stored procedure returns control to the batch.
Using the "read uncommitted" isolation level is probably not risky from the historical perspective. I would assume that the person instructing you to use this isolation level knows the risks and believes it is safe.
Historical data usually either does not change at all, or changes at known intervals. (Say, quarterly or daily at 1:00 am.) I would expect that relatively few people have insert rights for these tables, and almost nobody has update and delete rights.
You can also test running three separate insert statements within a single transaction, instead of inserting the union of three select statements. The ORDER BY clause is probably a bad idea in production.
source to share