如何仅在 sql 中为特定连接创建事务? [英] How do create the transaction for particular connection only in sql?

问题描述

我只想在 sql 中为特定连接创建一个事务.它应该为所有连接锁定该表，但允许其他连接读取该表，即使事务是从另一个连接开始的.我应该使用什么隔离级别.

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);            

推荐答案

对于选择查询，您可以使用 WITH (NOLOCK) 或 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

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

READ UNCOMMITTED:指定语句可以读取已被其他事务修改但尚未提交的行.因此，select 语句可以读取由其他未提交事务更新的行.因此，需要在要读取该表的另一个连接上设置隔离级别.

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();
    }
}

这篇关于如何仅在 sql 中为特定连接创建事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！