Transaction Isolation Level with CTE
-
How do you handle the “SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED” with common table expressions? If I add that before the CTE, I get yelled at by SQL.
Msg 319, Level 15, State 1, Line 12Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
So I’m wondering, do I have to put it with a semi-colon – which runs ok – before the CTE or after the CTE and before the select statement? Does it matter?
Thoughts or experiences?
-
Never mind, that was a stupid question. It cannot be placed after the CTE... Sorry for the brain fart!
-
You put the SET before the declaration of the CTE and properly terminate it with a ';'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;Oh, and http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Okay, since you just burst my bubble, let me ask you a question. The SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is being used on reports only and those reports are on a replciated server - not on a production server. No one is actually committing transactions except for the replication. The reason we went this way was two fold. First, the vendor recommended it because their library (over 200+ reports) uses that line of code at the beginnig of every report. Second, replication runs every hour and it takes, on occassion, up to 40+ minutes to complete. We have a very heavy financial/GL banking systems that writes a bajillion x 10 records for each transaction. When replication takes 40 minutes to complete, every one of the end users start ranting and raving about why their reports are erroring out. 99% of the time, its because of a deadock. We added that line of code to our reports that we added to the library and viola... no more deadlocking and crying form the end users.
Is using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in this manner not so bad after all?
P.S. Replication is under review with Microsoft and the DBA's as to why it takse so long, but nothing is going to happen any time soon...
-
SQL_Enthusiast (6/12/2013)
Okay, since you just burst my bubble, let me ask you a question. The SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is being used on reports only and those reports are on a replciated server - not on a production server. No one is actually committing transactions except for the replication. The reason we went this way was two fold. First, the vendor recommended it because their library (over 200+ reports) uses that line of code at the beginnig of every report. Second, replication runs every hour and it takes, on occassion, up to 40+ minutes to complete. We have a very heavy financial/GL banking systems that writes a bajillion x 10 records for each transaction. When replication takes 40 minutes to complete, every one of the end users start ranting and raving about why their reports are erroring out. 99% of the time, its because of a deadock. We added that line of code to our reports that we added to the library and viola... no more deadlocking and crying form the end users.Is using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in this manner not so bad after all?
P.S. Replication is under review with Microsoft and the DBA's as to why it takse so long, but nothing is going to happen any time soon...
The fact that you mentioned financial and banking in the same sentence, I'd have to say yes. You risk the possibility of the reports being inaccurate. You could fail to show records, show duplicate records, data that may be subsequently rolled back. Not what I'd want for financial reports at all.
You may want to look at the snap shot isolation levels instead and see how those work. This could easily result in more use of tempdb, but with financial data that is the direction I would look.
-
SQL_Enthusiast (6/12/2013)
Is using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in this manner not so bad after all?Depends. Do you like your reports accurate or potentially inaccurate. If the latter, nolock's fine, just make sure the users agree with you.
It makes no difference whether it's users or replication that's making the changes, nolock/read uncommitted allows for the possibility of missed rows and duplicate rows when there are any concurrent changes to the index that you're reading.
p.s. Take a look through your bug tracking system. How many bugs do you have, active or closed, for intermittently inaccurate results on reports? They'd have been impossible to replicate, so probably got closed as 'can't reproduce' or similar.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
SQL_Enthusiast (6/12/2013)
Is using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in this manner not so bad after all?Think of it this way.
Pretend that the replicated server is hosting your companies timesheet data. Pretend that H.R. pull a timesheet report to pay their staff. If that timesheet report was using READ UNCOMMITTED would you be happy being paid for less hours than you actually worked?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply