How to Set Isolation Level on SQLcommand/Sqlconnection Initialized with No Transaction

How to set isolation level on SqlCommand/SqlConnection initialized with no transaction

On the BeginTransaction method: (MSDN link)

And if you just want to use hints in your SP at the table level, use WITH(NOLOCK) - but use at your own risk.

How to set isolation level on SqlCommand/SqlConnection initialized with no transaction

On the BeginTransaction method: (MSDN link)

And if you just want to use hints in your SP at the table level, use WITH(NOLOCK) - but use at your own risk.

Avoiding Deadlocks within SQL transaction

I don't see any reason why you would make the SELECT query as part of the transaction to solve the deadlock or time out issue. Setting the ReadUncommitted isolation level on first sql connection myConnection that you have thought is also not the right approach. I see there are two possible solutions:

1. First Solution: Setting isolation level IsolationLevel.ReadUncommitted on the transaction myTrans you have started will not help. If you are comfortable with dirty reads then you should actually be setting this isolation level on the second SQL connection myConnection2 that you are establishing for firing select query on User table. To set the isolation level for the select query through myConnection2 you need to use with (nolock) table level hint. So your query will start to look like:

   string sSelect = "SELECT FirstName, LastName FROM User WITH (NOLOCK) WHERE ID = 123";
   

   You can get more details here.
   Also, read about the consequences of dirty read here which is a side effect of using this particular isolation level.

2. Second solution: Default isolation level of SQL Server is Read Committed. So when you start firing query through a new SQL connection using a variable named myConnection2 it is working on ReadCommitted isolation level. The default behavior exhibited by ReadCommitted isolation level is Blocking Read i.e. if there are uncommitted changes on a table (which can be committed or rollbacked due to an active transaction) then your select statement on User table will get blocked. It will wait for the transaction to finish so that it can read the newly updated data or the original data in case of a rollback. If it doesn't do such a blocking read then it will end up doing dirty read which is a well known concurrency issue with databases.
   
   If you do not want your SELECT statements to get blocked and want to keep going with last committed value of a row then there is a new database level setting in SQL Server named READ_COMMITTED_SNAPSHOT. Here is how you can change it using SQL script:

   ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON
   

   Quoting Pinal Dave from his article here:

If you are having problem with blocking between readers (SELECT) and
writers (INSERT/UPDATE/DELETE), then you can enable this property
without changing anything from the application. Which means
application would still run under read committed isolation and will
still read only committed data.

Note: This is a database level setting and will affect all the transactions on your database using READCOMMITTED isolation level.

In my opinion you should go with first solution. Also there are few key points which you should keep in mind to avoid deadlocks in SQL Server queries. Quoting Pinal Dave from here:

• Minimize the size of transaction and transaction times.
• Always access server objects in the same order each time in application.
• Avoid cursors, while loops, or process which requires user input while it is running.
• Reduce lock time in application.
• Use query hints to prevent locking if possible (NoLock, RowLock)
• Select deadlock victim by using SET DEADLOCK_PRIORITY.

How to set transaction isolation level using classic ASP?

As it is said in the documentation:

Note: The IsolationLevel settings will not work until next time
BeginTrans is called.

It is a kind of strange, but I need to the following:

conn.BeginTrans
... sql statement is executed here
conn.CommitTrans

Even thought the T-SQL statement is a SELECT. Also, after the conn.CommitTrans it is still using the default isolation level (the one specified in the context of the database).

SQL Connection Won't Run During Transaction

As discussed, first thing is that if you don't need two connections you can just use one so everything runs in the same transaction.

If you need this set of SQL run in parallel with other processes or threads running similar queries, you may want to revisit the isolation and locking of your statements.

You are probably running the first transaction as Serializable isolation and doing updates on it. Consider using ReadCommited (or Snapshot ReadCommited). But that would depend on your actual work.

From SSMS, try running first

SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED

That should avoid the locks created by fist transaction, but this is just so you can see why its happening, is not a good idea to use that isolation level!

Testing SQL methods that don't use transactions?

You can use a TransactionScope to add the connections automatically in to a transaction

public void testInsertRow()
{
    using(TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
    {
        SqlConnection c = new SqlConnection("connection.string.here");
        insertRow(c);
        // do something here to evaluate what happened, e.g. query the DB

        //do not call scope.Complete() so we get a rollback.
    }   
}

Now this will cause tests to block each other if you have multiple parallel tests running. If you database is set up to support it you could do Snapshot isolation so updates from concurrent tests won't lock each other out.

public void testInsertRow()
{
    using(TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, 
                                                        new TransactionOptions(IsolationLevel = IsolationLevel.Snapshot))
    {
        SqlConnection c = new SqlConnection("connection.string.here");
        insertRow(c);
        // do something here to evaluate what happened, e.g. query the DB

        //do not call scope.Complete() so we get a rollback.
    }   
}

Generic way to use SQL Server WITH NO LOCK

You could set the Transaction Isolation Level

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED

However, don't forget that NOLOCK means your queries can potentially return dirty or duplicated data, or miss out data altogether. If it's an option for you, I would suggest investigating the READ_COMMITTED_SNAPSHOT database option to allow you to avoid locking issues while returning queries with consistent results.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED not taking? Or am I looking in the wrong way?

The problem here is that the SqlConnection.BeginTransaction that does not take parameters defaults to read committed. I guess we didn't understand what the "default isolation level" text is on that page.

That page has this text:

If you do not specify an isolation level, the default isolation level is used. To specify an isolation level with the BeginTransaction method, use the overload that takes the iso parameter (BeginTransaction). The isolation level set for a transaction persists after the transaction is completed and until the connection is closed or disposed. Setting the isolation level to Snapshot in a database where the snapshot isolation level is not enabled does not throw an exception. The transaction will complete using the default isolation level.

(my highlight)

Here's a LINQPad script that demonstrates:

void Main()
{
    using (var conn = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated security=true"))
    {
        conn.Open();
        Dump(conn, "after open");

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
            cmd.ExecuteNonQuery();
        }

        Dump(conn, "after set iso");

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "BEGIN TRANSACTION";
            cmd.ExecuteNonQuery();
        }

        Dump(conn, "after sql-based begin transaction");

        using (var cmd = new SqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "COMMIT";
            cmd.ExecuteNonQuery();
        }

        Dump(conn, "after sql-based commit");

        var trans = conn.BeginTransaction();

        Dump(conn, "after .net begin transaction", trans);

        trans.Commit();

        Dump(conn, "after .net commit");
    }
}

public static void Dump(SqlConnection connection, string title, SqlTransaction transaction = null)
{
    using (var cmd = new SqlCommand())
    {
        cmd.Connection = connection;
        if (transaction != null)
            cmd.Transaction = transaction;
        cmd.CommandText = "SELECT transaction_isolation_level FROM sys.dm_exec_sessions WHERE session_id = @@SPID";
        Debug.WriteLine(title + "=" + Convert.ToInt32(cmd.ExecuteScalar()));
    }
}

It will output:

after open=2
after set iso=1
after sql-based begin transaction=1
after sql-based commit=1
after .net begin transaction=2
after .net commit=2

Here you can see that manually beginning and committing a transaction through SQL would not change the isolation level, but beginning a transaction in .NET without explicitly stating the isolation level still changes it to read committed.

Since everywhere we read, starting a transaction without explicitly stating the isolation level said that it inherited the isolation level of the session, I guess we didn't understand that .NET would not do the same.

---

Related Topics