ReadCommited with OLEDB (MS SQL)

cosxp · Post by **cosxp** » Tue 24 Aug 2010 17:28

What would be best approach to control let other user can read while records are updated in other transaction ?

Example: test_table
RecKey value
1 AA
2 BB
3 CC

When user A run start transaction and
run sql as update test_table set value 'DD' where reckey =3

Until user A issues commit transaction,
All other users trying to access this table gets hang when
it tries to access Reckey 3.

Is it possible let other user can read while records are in updating
transaction not commited yet ?

Currently I get hangs after RecKey 2 fetched and waiting infinitely
until user A commited or rolled back.

Dimon · Post by **Dimon** » Wed 25 Aug 2010 11:30

To solve the problem, you should use the TUniTransaction component for working with transactions and set the TUniTransaction.IsolationLevel property to ilReadUnCommitted.

cosxp · Post by **cosxp** » Wed 25 Aug 2010 19:01

Thanks for suggetion and it works as dirty read mode (Readuncommited)

What I really want to do is, I want be able to read last commited records while it's being updated by user's Intransaction.

I tried all different isolation level and it seems readuncommited (dirty read) only works but this one gives the record value as is could be commited or rolled back later.

Any other suggestion ?

Dimon · Post by **Dimon** » Thu 26 Aug 2010 11:27

UniDAC supports all SQL Server transaction isolation levels. Only the ilReadUnCommitted level allows reading records that was changed and uncommitted. We can't change this behaviour because it is the specificity of SQL Server.

cosxp · Post by **cosxp** » Thu 26 Aug 2010 14:15

It seems starting sql server version 2005,
read_commited_snapshot is the one I'm looking for.

Thank you.

halenpolen · Post by **halenpolen** » Mon 06 Sep 2010 08:33

cosxp wrote:Thanks for suggetion and it works as dirty read mode (Readuncommited)

What I really want to do is, I want be able to read last commited records while it's being updated by user's Intransaction.

I tried all different isolation level and it seems readuncommited (dirty read) only works but this one gives the record value as is could be commited or rolled back later.

Any other suggestion ?

try this way:
select * from test_table with(nolock) where reckey =3