TransactionScope锁定表和IsolationLevel [英] TransactionScope locking table and IsolationLevel

问题描述

我想在我的项目中使用 TransactionScope 。我读了一下它，我发现它在数据库中创建一个隐式事务。我想知道 TransactionScope 是否锁定它操作的表？

I want to use TransactionScope in my project. I read about it and I found that it creates an implicit transaction in the database. I want to know if that TransactionScope locks tables that it manipulates?

例如在此代码中：

using (Entities ent = new Entities())
{
    using (TransactionScope tran = Common.GetTransactionScope())
    {
        var oldRecords = ent.tblUser.Where(o => o.UserID == UserID);

        foreach (var item in oldRecords)
        {
           ent.tblUser.DeleteObject(item);
        }

和

public static TransactionScope GetTransactionScope()
{
    TransactionOptions transactionOptions = new TransactionOptions();
    transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
    return new TransactionScope(TransactionScopeOption.Required, transactionOptions);
}

是 tblUser 锁定直到完成命令问题？

在显式中是 IsolationLevel

感谢

推荐答案

> SQL Server 执行锁定 - 如果需要。 任何 UPDATE 或 DELETE 操作必须获得独占锁<

It's SQL Server that does the locking - if needed. Any UPDATE or DELETE operation must get an exclusive lock on those rows it affects - if those are locked already by another transaction, it cannot do that.

因此，在你的情况下，如果你删除了一些行数据库，SQL Server默认情况下将锁定仅这些行 - 那些正在被删除。它不锁定整个表。这是除非同时删除大量的行 - 如果您在单个事务中删除了 5'000 行，SQL Server将尝试执行锁升级，并锁定整个表（而不是保留和管理5000个单独的行锁）。

So in your case, if you're deleted a number of rows from the database, SQL Server by default will lock those rows only - those that are being deleted. It doesn't lock the whole table. This is unless you delete a very large number of rows at once - if you delete more then 5'000 rows in a single transaction, SQL Server will try to do a lock escalation and lock the whole table (instead of keeping and managing 5000+ individual row locks).

隔离级别只定义< >读取将锁定一行 - 默认情况下（ READ COMMITTED ），该行只有在正在读取 - 通常是非常非常短的时间。使用隔离级别 REPEATABLE READ ，共享锁将保持到当前事务结束， SERIALIZABLE 将不仅锁定正在读取的行，而且锁定整个行范围中。但是：仅会影响 READ 操作 - 它对 DELETE 或 UPDATE 语句（除了在一行上有共享锁可能会阻止 DELETE 获取它需要的独占锁）

The isolation level only defines how long reading will lock a row - by default (READ COMMITTED), the row will have a shared lock only for the time it's being read - typically a very very short time. With isolation level REPEATABLE READ, the shared lock will be held until the end of the current transaction, and SERIALIZABLE will not only lock the rows being read, but entire ranges of rows. But again: that only affects READ operations - it has no direct impact on the DELETE or UPDATE statements (other than having a shared lock on a row might prevent the DELETE from acquiring it's exclusive lock that it needs)

这篇关于TransactionScope锁定表和IsolationLevel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！