asked on

READ_COMMITTED_SNAPSHOT Equivalence For SQL Server 2000

I know that SQL Server 2005 has the READ_COMMITTED_SNAPSHOT option setting. If a transaction is used in VB.NET with isolation level set to Uncommitted, the tables still seem to be locked, and I can't figure out how to keep the server from doing that at the server level, instead of the procedure level.

Bob

Racim BOUDJAKDJI

Setting the database on READ_COMMITTED_SNAPSHOT allows to activate row versionning on ALL transactions.

By running...
alter database <yourdatabase> set read_committed_snapshot on

all selects issued AFTER an uncommited transaction will run because they will access the last commited version of the rows stored in tempdb instead of the tables...It's equivalent to ORACLE Read Consistency

Hope this helps....

ASKER CERTIFIED SOLUTION

Racim BOUDJAKDJI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Learned

ASKER

There is something strange going on here, because I set the transaction level to ReadUncommitted, which should allow dirty reads, but the records are still be locked. Maybe cuz I don't use transactions with VB.NET very much, I am not doing something right here, but who knows.

Bob

Racim BOUDJAKDJI

Do you do that in the same connexion?

Bob Learned

ASKER

Yes, only 1 SqlConnection object defined.

Bob

Racim BOUDJAKDJI

That's strange...It should work...Only thing I can see is provider related...Do you use ODBC, OLEDB, SQLExecDirect?

Racim BOUDJAKDJI

<<Yes, only 1 SqlConnection object defined.>>Do you close the connexion at least once AFTER you set the transaction isolation level to uncommitted?

Bob Learned

ASKER

Pertinent information:
ASP.NET 1.1
VB.NET 2003
SQL Server 2000
SqlClient: SqlConnection and SqlTransaction
Isolation level: Read Uncommitted

Dim trans As SqlTransaction = conn.BeginTransaction(IsolationLevel.ReadUncommitted)

I realize that this is question that is going to make you think about transactions and isolation levels and record/table locks, so good luck, because I couldn't find any answers for myself.

Bob

Racim BOUDJAKDJI

My concern is about HOW the server actually perceives the instruction

conn.BeginTransaction(IsolationLevel.ReadUncommitted) and if it correctly implements it server side...You may want to catch the instruction on the server using Profiler to make it is properly parsed by the provider...Otherwise, you could try to run the SQL transaction statement on the server and see what happens...If no problem happens then, one can strongly assume the issue is related to parsing the right instruction to the server as opposed to a server problem...Could you validate these points?

Bob Learned

ASKER

I haven't used the Profiler. I am a programmer, and I hadn't spent much time dealing with the DBA stuff. There was always someone else around to deal with that part, so I didn't learn anything. I don't have that luxury how.

Do you have the steps to check that in the Profiler?

Bob

Racim BOUDJAKDJI

All you need to do is set up a simple Profiler session on the server and catch the process server side...If you don't feel confortable with profiler, you may want to take a look at this link

http://vyaskn.tripod.com/analyzing_profiler_output.htm

Bob Learned

ASKER

You know, it is nice when you can use a question as a reference for a reason to upgrade to 2005. I was trying to convince someone, and they wanted to know if it was possible with 2000 to set a database option for how it processes transactions and isolation.

Thank you for that information about the profiler.

Bob

Racim BOUDJAKDJI

You are correct it is a justification to go to 2005...MS is slowly catching up with Oracle...But watch out with performance hit on tempdb IO