Configure the isolation level to allow ReadUncommited while updating data

Question

I begin a transaction with SqlConnection.BeginTransaction() and I do a DELETE and some INSERTs. There is any configuration I can do on the isolation level to allow any query to read the data on a "dirty way" during the transaction?

Basically I want to prevent locks while I update the data. The problem is; I can't control the SELECTs. If I define a ReadUncommited isolation level on my transaction will the external querys have rights to read the data without waiting or is required to define it on this querys?

For example:

try
{                         
   connection.Open();
   transaction=connection.BeginTransaction(IsolationLevel.ReadUncommited);            

   // DELETE
   foreach (int i in fibarray)
   {
       // INSERTS
   }
   transaction.Commit();
}
catch (Exception ex)
{
   if (transaction.Connection != null)
       transaction.Rollback();
}

Meanwhile, the SELECTS on another machine I have not the access.

Each connection/session establishes for itself what level of dirtiness it's willing to put up with. There's no way for a *different* connection to suddenly force a connection to see dirtier data (or, conversely, to be more strict) — Damien_The_Unbeliever, Dec 18 '13 at 13:33
@Damien_The_Unbeliever So there is no way to tell my update commands not to block. It depends entirely on the "`SELECT`s" side. — SysDragon, Dec 18 '13 at 14:38
Yes, the `SELECT`s have all of the control over their locking/blocking behaviour. And `UPDATE`s always have to apply some exclusive locks to complete successfully. — Damien_The_Unbeliever, Dec 18 '13 at 14:47
@Damien_The_Unbeliever Well, that was the information I was asking for, so if you don't mind add it as an answer. — SysDragon, Dec 18 '13 at 14:55

score 2 · Accepted Answer · answered Dec 18 '13 at 14:57

Each connection/session establishes for itself what level of dirtiness it's willing to put up with. There's no way for a different connection to suddenly force a connection to see dirtier data (or, conversely, to be more strict)

The SELECTs have all of the control over their own locking/blocking behaviour. And UPDATEs always have to apply some exclusive locks to complete successfully. So if you cannot change the SELECTs, then you can't overcome your current situation.

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

Try WITH(NOLOCK) clause on the other SELECTS. This clause will read dirty data.

Example

SELECT ....
FROM <tablename> WITH(NOLOCK) 
WHERE ......

More info here

EDIT: BTW, setting the isolation level ReadUncommited will make the current transaction read uncommitted data, it doesn't control the isolation level of transactions of of the current scope.

edited May 23 '17 at 10:32

Community

1
1

answered Dec 18 '13 at 13:51

fossilcoder

59
6

Yep, the problem presented is that I cannot control the `SELECT`s. – SysDragon Dec 18 '13 at 14:37
Did you tried the WITH(NOLOCK) on the SELECT statements outside the transaction scope? That will solve your problem. – fossilcoder Dec 18 '13 at 17:45
Please read the question. I have no access to the `SELECT` statements, and I just told you before. You're not understanding the question. – SysDragon Dec 18 '13 at 23:58
Sorry, I misunderstood you, next time specify that you can't change or modify the `SELECT` statement, 'access' word was misleading. – fossilcoder Dec 19 '13 at 14:35
Probably I didn't express myself properly. My bad. – SysDragon Dec 19 '13 at 14:44

Configure the isolation level to allow ReadUncommited while updating data

2 Answers2