Question about Transaction IsolationLevel

[Mike P]

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

 

[Kevin C]

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

 

[Patrick Altman]

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/docs/odbc/odbcref/locking.html

Patrick Altman

 

[Mike P]

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