Stored Procedure Code to "Up To Date"

Question

Stored Procedure Code to "Up To Date"

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

gmartin (6/11/2009)
I have one server, but it has multiple databases. The main db is where this code originally lived. Yesterday, in addition to editing it, I had to move it to a different db on the same server. I also had to change my web code to look for it on the other db rather than on the main db.

Thought it was something like that. Done it myself occasionally.

Now that I have your ear, though, can I get a little info about "Set Isolation Level Read Uncommitted"?
I just discovered this feature and I have mucho use for it.

Don't get too attached to it, there are downsides to it and there's a reason it's not the default isolation level. Also it only applies to selects. All data modifications will lock.

See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

But, I'm unclear as to how long it "lives" when set.

Until the connection closes or a different isolation level is set

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gmartin SSC Rookie Points: 49 More actions · Answer 2

Cool. I only want it for Selects.

So, last question. What would be a good way to set it back to default?

Set Isolation Level Read Uncommitted

begin tran

select @Tran_Date = Tran_Date from myTable

where Tran_ID = 123456

commit

Set Isolation Level ??????? --To get it back to "default"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

gmartin (6/11/2009)
begin tran
select @Tran_Date = Tran_Date from myTable
where Tran_ID = 123456
commit

Absolutely no point in a transaction there. Transactions are so that changes happen as atomic units. There's no reason to put a transaction around a single statement and no real reason to have a transaction with only selects in it.

Why do you want that select to run read uncommitted?

Set Isolation Level ??????? --To get it back to "default"

Read Committed

Make sure you've read over those two articles first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MannySingh SSChampion Points: 12465 More actions · Answer 4

SET TRANSACTION ISOLATION LEVEL read committed

Maninder
www.dbanation.com

gmartin SSC Rookie Points: 49 More actions · Answer 5

I want to "select" without locking things up. SNAPSHOT may be better option, but that's not turned on at the moment.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 6

Dave Ballantyne (6/11/2009)
I think we've all been there , cant quite believe what you are seeing , then realising the massive cock-up.
Careful with uncommitted reads http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

Why do you do a Begin transaction and a commit for a select, you only need need to do this for a data modification, or insert. The present transaction is useless.

As for the default transaction isolation level, I'm not sure of it, so I will not say anything, fearing to get corrected! 😉

Cheers,

J-F

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

gmartin (6/11/2009)
I want to "select" without locking things up. SNAPSHOT may be better option, but that's not turned on at the moment.

If you use the default isolation, does that select cause blocking? Do you have an index in the column Tran_ID?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

gmartin SSC Rookie Points: 49 More actions · Answer 8

If you use the default isolation, does that select cause blocking? Do you have an index in the column Tran_ID?

We have indexed this thing very comprehensively. The code I gave was just a sample. We're upwards of a million lines of code combined in SQL and Web side of things. We process more than 500,000 new transactions and searches per day on this system. It's grown quite a bit over the past 4 months and it's starting to hurt a little.

Pages (Queries) that display instantly at night are now timing out during the day. If we shut down our various processing jobs, the timeouts pretty much go away. I started wrapping insert, selects, updates, etc in transactions and it helped quite a bit. Now, I have added Read Uncommitted to a couple of the biggest culprits and the problem seems to have vanished.

I want to try SNAPSHOT rather than Read Uncommitted, but that will have to wait until off hours.

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

gmartin (6/11/2009)
Cool. I only want it for Selects.
So, last question. What would be a good way to set it back to default?
Set Isolation Level Read Uncommitted
begin tran
select @Tran_Date = Tran_Date from myTable
where Tran_ID = 123456
commit
Set Isolation Level ??????? --To get it back to "default"

What about: select @Tran_Date = Tran_Date from myTable WITH (NOLOCK) ?