How can I create a transaction for a specific connection in sql only?
I want to create a transaction in sql for a specific connection only. It should lock this table for all connections, but allow other connections to read this table, even if the transaction started on a different connection. What isolation level should I use for this.
Tran = CnnTran.BeginTransaction(IsolationLevel.RepeatableRead);
source to share
You can use WITH (NOLOCK)
or to prompt for selection SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
.
READ UNCOMMITTED: Indicates that statements can read rows that have been modified by other transactions but not yet executed. Therefore, select statements can read rows that have been updated by other uncommitted transactions. Thus, the isolation level must be set on the other connection that you want to read in this table.
If you want, you can set this on connection:
var myConnection = new SqlConnection();
// connection for reading uncommitted transactions
myConnection.BeginTransaction(IsolationLevel.ReadUncommitted).Commit();
Or you can just do it inside a transaction if you like:
using (var myConnection = new SqlConnection(connectionString)) {
myConnection.Open();
using (var transaction = myConnection.BeginTransaction(IsolationLevel.ReadUncommitted)) {
// do stuff
transaction.Commit();
}
}
source to share