Hi,

I would like to set the Isolation level to Read Uncommited during the fetching of my TypedList, without transaction, because I am only reading.

What is the best way?

Rogelio

You shouldn't have to set an isolation level if you're only fetching without a transaction, you should set the isolation level at the transaction which is active at that same moment.

Frans,

Thank you for your answer.

The point is the following, I did a test with SQL Server and two Query Analyzer instances (two connections).

1. Connection 1 with a transaction updating a record and a wait of ten seconds before the commit. Isolation Read Commited.

2. Connection 2 with a Select of the record being updating by Connection 1.

As soon as I started the batch of Connection 1 and started the batch of Connection 2, the 10 seconds of Conn. 1 gave me the time to start the second one.

With the SQL server default of Read Commited, the second connection got locked until Conn. 1 finished. Then I add a set isolation level to read uncommited to the second conn., before the Select statement. The results were different, conn. 2 finished before conn. 1, with the data changed by conn. 1.

As I am developing multi-user application, I do not want a transaction changing records, locks my other users that are reading only.

I was thinking may be you can change the logic of the DataAdapter to change the isolation level to the one that is specificied in it; but only when the isolation level is not the default and there is not transaction active. Something like: Set Isolation "the one specificied in isolationlevel property" Select ..........

Rogelio

level level

Hi,

About my last post. I know it will gave me dirty read records and the transactions can rollback; but there situations where that does not matter. For example when you are reading the customer's code and name to show in a dropdown control, you do not want the reading to be locked by order user updating the due balance of somes customers.

Rogelio

Rogelio wrote:

The point is the following, I did a test with SQL Server and two Query Analyzer instances (two connections).

1. Connection 1 with a transaction updating a record and a wait of ten seconds before the commit. Isolation Read Commited.

2. Connection 2 with a Select of the record being updating by Connection 1.

As soon as I started the batch of Connection 1 and started the batch of Connection 2, the 10 seconds of Conn. 1 gave me the time to start the second one.

With the SQL server default of Read Commited, the second connection got locked until Conn. 1 finished. Then I add a set isolation level to read uncommited to the second conn., before the Select statement. The results were different, conn. 2 finished before conn. 1, with the data changed by conn. 1.

True, a transaction places X locks on the rows affected which lock out any reader of another connection till the X locks are removed.

As I am developing multi-user application, I do not want a transaction changing records, locks my other users that are reading only.

I was thinking may be you can change the logic of the DataAdapter to change the isolation level to the one that is specificied in it; but only when the isolation level is not the default and there is not transaction active. Something like: Set Isolation "the one specificied in isolationlevel property" Select ..........

If you don't want a transaction to lock out other users, create the transaction with the ReadUncommitted isolation level, for example: adapter.StartTransaction(...) // do things here, also recursive saves adapter.Commit();

this will make sure your transaction won't lock out readers. Though this shouldn't be necessary: row changes/deletes should be done at the end of a routine so the changes can be pushed to the database and the time readers are locked out is minimized. I mean by this that should avoid starting a transaction, do a lot of reading, open forms wait for user input etc. etc. and after that committing it. Transactions should be started right before some activity on the database has to be performed and should be closed right after that and reads should preferably be done outside the transaction.

[quotenick="Otis"]

Rogelio wrote:

If you don't want a transaction to lock out other users, create the transaction with the ReadUncommitted isolation level, for example: adapter.StartTransaction(...) // do things here, also recursive saves adapter.Commit();

What transaction you are talking about, in my example, we have only one transaction (the one in connection 1)? In connection 2 I do not want to start a transaction because I am just reading for a report. If I create the connection 1's transaction with ReadUncommited, that any way will block other users reading records that have been changed by it, until the Commit. The ReadCommited setting in connection 1, will allow connection 1 to read records that had been changed by other transactions not matter if the other transactions have commited it or not.

Rogelio

Rogelio wrote:

Otis wrote:

Rogelio wrote:

If you don't want a transaction to lock out other users, create the transaction with the ReadUncommitted isolation level, for example: adapter.StartTransaction(...) // do things here, also recursive saves adapter.Commit();

What transaction you are talking about, in my example, we have only one transaction (the one in connection 1)?

yes

In connection 2 I do not want to start a transaction because I am just reading for a report. If I create the connection 1's transaction with ReadUncommited, that any way will block other users reading records that have been changed by it, until the Commit. The ReadCommited setting in connection 1, will allow connection 1 to read records that had been changed by other transactions not matter if the other transactions have commited it or not.

Hmm. It is my understanding that if you start a transaction with isolation level ReadUncommitted, OTHER connections can read your changes as well, i.e.: you're not blocking other readers. It furthermore doesn't honor locks set by other transactions.