Devart Forums

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.

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

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 ?

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.

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

Thank you.

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