检索使用标识ID最大的TransactionScope [英] Retrieve the maximum IDENTITY id using transactionscope

问题描述

下面是code：

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    DBContext.AddtoSomeTable(record);
    System.Threading.Thread.Sleep(5000);
    DBContext.SaveChanges();

    MAX_ID = (from t in DBContext.SomeTable
              select t.ID).Max();

    scope.Complete();
}

据我所知，在完成范围前， MAX_ID 应该是新记录的ID，这是刚刚插入。即使有两个用户在同一时间提交，以后应该等到第一个的的TransactionScope 完成。

As far as I know, before the scope is completed, the MAX_ID should be the new record's ID, which is just inserted. Even if there are two users submitting at the same time, the later one should wait until the first one's TransactionScope is completed.

但是，什么情况是两个用户获得相同的 MAX_ID 。这意味着第一人的记录被插入到数据库后，再第二个的记录被插入，然后第一人得到他的 MAX_ID 。

But, what happens is the two users get the same MAX_ID. That means after the first person's record is inserted into the database, then the second one's record is inserted, and then the first person gets his MAX_ID.

我的表被锁以避免脏数据的事务处理过程中想。任何人都可以解释一下吗？

I thought during a transaction the table was locked to avoid dirty data. Can anyone explain this?

编辑：
ID字段是自动递增

the ID field is auto incremented

推荐答案

不同类型和范围的锁在操作过程中采取和锁定范围将发生变化，由于锁升级为锁的数量增加。在一个单一的表，如果，在一个事务中，要插入/更新/删除单行，独占行锁将最有可能采取。该表为您更新/插入/删除多个行，一旦你积累了一定数量的行锁，锁升级的将他们提升到页锁。厚积薄发页锁的自给自足，你会升级到表锁。

Locks of different types and scopes are taken during an operation and the lock scope will change due to lock escalation as the number of locks increases. If, in a single transaction, you are inserting/update/delete a single row in a single table, an exclusive row lock will most likely be taken. As you update/insert/delete more rows in that table, once you accumulate a certain number of row locks, lock escalation will promote them to page locks. Accumulate a sufficiency of page locks and you'll upgrade to a table lock.

我不知道你想从someTable 选择MAX（ID），但是如果你希望它反映了你的<$ C $做什么C>的SaveChanges（）操作只是做了，最有可能的，你要创建一个竞争条件。

I don't know what you're trying to do with select max(id) from someTable, but if you expect it to reflect what your SaveChanges() operation just did, most likely, you're creating a race condition.

假设你正在使用SQL Server，内置功能的 SCOPE_IDENTITY（） 会给你

Assuming you're using SQL Server, the built-in function scope_identity() will give you

插入到相同范围的标识列的最后一个标识值。
  作用域是一个模块：一个存储过程，触发器，函数或批处理

the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch."

不过：如果在同一范围内，你，说，行插入两个不同的表，每一个都具有标识列，

However: if, in the same scope, you, say, insert a row into two different tables, each having an identity column,

begin transaction
insert foo () ...
insert bar () ...
commit transaction

SCOPE_IDENTITY（）给你刚才生成的范围的最后一个值（表栏在这个例子中）。

scope_identity() gives you just the last value that your scope generated (for the table bar in this example).

这篇关于检索使用标识ID最大的TransactionScope的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！