How do create the transaction for particular conne

问题:

I want to create a transaction in sql for a particular connection only. It should lock down that table for all connections but allow other connections to read that table even if the transaction has began from another connection. What Isolation Level should i use for it.

Tran = CnnTran.BeginTransaction(IsolationLevel.RepeatableRead);            

回答1:

For the select query you can either use use WITH (NOLOCK) or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

READ UNCOMMITTED: Specifies that statements can read rows that have been modified by other transactions but not yet committed. Therefore, select statements can read the rows that were updated by other uncommitted transactions. So, the isolation level needs to be set on the other connection that you want to read that table.

If you want you can set this at the connection:

var myConnection = new SqlConnection();

// connection for reading uncommitted transactions
myConnection.BeginTransaction(IsolationLevel.ReadUncommitted).Commit();

Or you could just do it inside a transaction if you wish:

using (var myConnection = new SqlConnection(connectionString)) {
    myConnection.Open();
    using (var transaction = myConnection.BeginTransaction(IsolationLevel.ReadUncommitted)) {
        // do stuff
        transaction.Commit();
    }
}