交易的隔离级别 [英] Isolation level for a transaction

问题描述

我的代码如下.

My code as follows.

SqlConnection _sqlConnection1 = new SqlConnection();
_sqlConnection1.ConnectionString = strConString;
_sqlConnection1.Open();
SqlTransaction _transaction1 = _sqlConnection1.BeginTransaction(IsolationLevel.ReadUncommitted);

现在，我正在使用sql命令更新表.

Now I''m updating a table by using sql command.

SqlCommand _sqlCmd1 = new SqlCommand("Update company set city='TT'");
_sqlCmd1.Connection = _sqlConnection1;
_sqlCmd1.Transaction = _transaction1;
            
int result1 = _sqlCmd1.ExecuteNonQuery();

在不提交事务的情况下，我正在创建另一个连接.

Without Commiting the transaction I''m creating another connection.

SqlConnection _sqlConnection2 = new SqlConnection();
_sqlConnection2.ConnectionString = strConString;

_sqlConnection2.Open();
SqlTransaction _transaction2 = _sqlConnection2.BeginTransaction(IsolationLevel.ReadUncommitted);

现在，我再次更新同一张表

Now I''m updating the same table again

 SqlCommand _sqlCmd2 = new SqlCommand("Update company set city='ww' where city='aa'");
_sqlCmd2.Connection = _sqlConnection2;
_sqlCmd2.Transaction = _transaction2;

从下面的行给出了超时异常.

From the follwing line it gives a Timeout exception.

int result = _sqlCmd2.ExecuteNonQuery();

其实我的问题是这样的.当我打开程序时，我需要创建InvoiceNo来显示给用户.在这种情况下，我要从表中获取最大数量并添加一个.如果另一个用户同时使用该程序，则他应该获得下一个号码，而不必重复相同的发票号码.在这种情况下，我将在程序加载时在表中插入一条记录.为了使用户能够使用该记录，我将使用未提交的隔离级别将字段"1"更新为"1".如果用户未生成发票，则已更新字段设置为"0"，任何人都可以在下一张发票中使用该字段.当用户生成发票时，仅交易被提交.在我将隔离级别设置为更新语句后，整个表将被锁定.

Actually my problem is like this. When I opening my program I need to create the InvoiceNo to show it to the user. For that case I''m taking the maximum number from the table and add one to it. In the same time if another user using that program, he should get the next number without repeating the same invoice number. For that case I''m inserting a record to the table when the program loads. And to keep a user is using that record I''m updating a field with ''1'' with the readuncommited isolation level. If user didn''t generate the invoice that updated field is set to ''0'' and can be used in next invoice by anyone. When user generates the invoice only transaction getting commited. After I set the update statement with the isolation level whole table get locked.

推荐答案

在此行之后

after this line

int result1 = _sqlCmd1.ExecuteNonQuery();

公司表已锁定，并且只能由已对其进行锁定的事务进行编辑(插入/删除/更新).

您必须在_sqlCmd2.ExecuteNonQuery();之前提交事务，或将_transaction1用作_sqlCmd2
的事务
交易简介 [

the company table is locked and can only be edited (insert/delete/update) by the transaction that has locked it.

you have to either commit the transaction before _sqlCmd2.ExecuteNonQuery(); or use _transaction1 as the transaction for _sqlCmd2

Introduction to Transactions[^]

指定ReadUncommitted并不意味着您可以更新已锁定的数据在其他会议上.它仅定义您可以读取未提交的数据.

在不了解您的情况的情况下，我建议您不使用读取未提交隔离级别，除非您确实必须这样做并且知道后果.如果愿意，请阅读在查询中使用RU隔离级别的一些解释使用NOLOCK提示时应该考虑的问题 [ ^ ]

Specifying ReadUncommitted doesn''t mean that you can update the data that is locked by some other session. It only defines that you can read uncommited data.

Without knowing anything more about your situation I would advice not to use read uncommitted isolation level unless you really have to and you know the consequences. If you like, read some explanations about using RU isolation level in queries from What should be considered when NOLOCK hint is used[^]

这篇关于交易的隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！