By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,310 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,310 IT Pros & Developers. It's quick & easy.

Question about Transaction IsolationLevel

P: n/a
I am using transactions on my website and the Isolation Level is
ReadCommitted. Since the website has a lot of traffic this may be
causing it to lock up every now and again.

Can somebody tell me what IsolationLevel I should use if I want the user
to be able to read while a record is being inserted/updated, as I'm not
sure that the Isolation Level I am using is the right one to go for.
Any assistance would be really appreciated.

Cheers,

Mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
A better solution is to set the isolation level according to your needs for
that transaction and change your *other* web site queries to perform "dirty"
reads. This can be done by setting the transaction level to READ
UNCOMMITTED. This is actually a good practice and should be done if you
don't care about reading uncommitted data.

Add this to the top of your stored proc:

--Allow dirty reads
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Kevin Cunningham

"Mike P" <mr*@telcoelectronics.co.uk> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
I am using transactions on my website and the Isolation Level is
ReadCommitted. Since the website has a lot of traffic this may be
causing it to lock up every now and again.

Can somebody tell me what IsolationLevel I should use if I want the user
to be able to read while a record is being inserted/updated, as I'm not
sure that the Isolation Level I am using is the right one to go for.
Any assistance would be really appreciated.

Cheers,

Mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 16 '05 #2

P: n/a
Mike,

It depends on what you want to accomplish. If you want users to be able to
read data from the inserts and updates before anything is committed, then
you can use ReadUncommitted, which would give you what is called a "dirty
read". That is it will not create any locks. However, it will show
uncommitted records, which may or may not exist depending on if the
transaction that is making the DML request rolls back or commits. If you
want the user to only be able to read changes that have been committed to
the database, then stick with ReadCommitted.

For a more detailed look at the different isolation levels and what impact
it may have to your specific application see:
http://media.datadirect.com/download...f/locking.html

Patrick Altman
"Mike P" <mr*@telcoelectronics.co.uk> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
I am using transactions on my website and the Isolation Level is
ReadCommitted. Since the website has a lot of traffic this may be
causing it to lock up every now and again.

Can somebody tell me what IsolationLevel I should use if I want the user
to be able to read while a record is being inserted/updated, as I'm not
sure that the Isolation Level I am using is the right one to go for.
Any assistance would be really appreciated.

Cheers,

Mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 16 '05 #3

P: n/a
Do these locks occur at table level or row level (i.e. do they lock the
whole table or just the particular row)?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.