November 7, 2008 at 6:04 am
Is it a good practice to set the default isolation level in stored procedures? I know that is a broad question and if so, what is the best one to use?
Our system is about 50/50 between OLTP and querying/reporting and the sql server default is read committed.
From my experience, I've found it a good practice to default the select only sps to read uncommitted and the update/delete/insert sps to read committed.
November 7, 2008 at 7:11 am
If you use the read uncommitted, you can, not only get partially updated records, but duplicate or missing records since page splits and reorders due to changes in the data referenced by the indexes being used in queries. While you can justify getting "Fred" instead of "Dave" in the result set for most businesses, you can't justify getting "Fred" and "Fred" or never finding "Fred" at all.
I would avoid it utterly. If the database in question has inserts & updates you should either use snapshot or read committed, as you are. If you get into a true reporting system, then you mark the database read only and you don't have to sweat locking & blocking from that point forward.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 7, 2008 at 9:18 am
Another question - in the sps we've inherited, the developers used the nolock hint for the read only sps (dirty data is okay). In you're opinion, is this better than changing the isolation level to uncommitted?
November 7, 2008 at 9:35 am
I wouldn't recommend that either for the same reasons. Itzik Ben-Gan has a great set of example scripts that show how using NOLOCK & other dirty read mechanisms can lead to duplicate data or missing data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply