Click here to Skip to main content
15,743,179 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
My code as follows.

SqlConnection _sqlConnection1 = new SqlConnection();
_sqlConnection1.ConnectionString = strConString;
SqlTransaction _transaction1 = _sqlConnection1.BeginTransaction(IsolationLevel.ReadUncommitted);

Now I'm updating a table by using sql command.

SqlCommand _sqlCmd1 = new SqlCommand("Update company set city='TT'");
_sqlCmd1.Connection = _sqlConnection1;
_sqlCmd1.Transaction = _transaction1;
int result1 = _sqlCmd1.ExecuteNonQuery();

Without Commiting the transaction I'm creating another connection.

SqlConnection _sqlConnection2 = new SqlConnection();
_sqlConnection2.ConnectionString = strConString;

SqlTransaction _transaction2 = _sqlConnection2.BeginTransaction(IsolationLevel.ReadUncommitted);

Now I'm updating the same table again

 SqlCommand _sqlCmd2 = new SqlCommand("Update company set city='ww' where city='aa'");
_sqlCmd2.Connection = _sqlConnection2;
_sqlCmd2.Transaction = _transaction2;

From the follwing line it gives a Timeout exception.

int result = _sqlCmd2.ExecuteNonQuery();

Actually my problem is like this. When I opening my program I need to create the InvoiceNo to show it to the user. For that case I'm taking the maximum number from the table and add one to it. In the same time if another user using that program, he should get the next number without repeating the same invoice number. For that case I'm inserting a record to the table when the program loads. And to keep a user is using that record I'm updating a field with '1' with the readuncommited isolation level. If user didn't generate the invoice that updated field is set to '0' and can be used in next invoice by anyone. When user generates the invoice only transaction getting commited. After I set the update statement with the isolation level whole table get locked.
Updated 28-Aug-11 19:00pm

after this line
int result1 = _sqlCmd1.ExecuteNonQuery();

the company table is locked and can only be edited (insert/delete/update) by the transaction that has locked it.

you have to either commit the transaction before _sqlCmd2.ExecuteNonQuery(); or use _transaction1 as the transaction for _sqlCmd2

Introduction to Transactions[^]
Share this answer
Specifying ReadUncommitted doesn't mean that you can update the data that is locked by some other session. It only defines that you can read uncommited data.

Without knowing anything more about your situation I would advice not to use read uncommitted isolation level unless you really have to and you know the consequences. If you like, read some explanations about using RU isolation level in queries from What should be considered when NOLOCK hint is used[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900