Windows Azure SQL 数据库 - 标识自动递增列跳过值 [英] Windows Azure SQL Database - Identity Auto increment column skips values

问题描述

目前正在使用实体框架 5 开发 ASP.Net MVC 4 应用程序.在初始开发阶段使用 CodeFirst.但是现在已经禁用了自动迁移并直接使用 SSMS 和编写 POCO 设计新表.一切正常.

Currently working on an ASP.Net MVC 4 application using Entity Framework 5. Used CodeFirst for initial development phase. But have now disabled the Automatic Migrations and designing new tables directly using SSMS and writing POCO. Everything is working good.

最近，在生产中发现了一个奇怪的问题.最初设计的表之一中的记录跳过了超过 900 个数字的自动递增标识值.这种情况在过去 3 个月内发生了 3 次.在本地调试应用程序但无法重现.没有观察到任何模式或趋势.

Recently, identified a weird issue in Production. The records in one of the initially designed tables skipped auto-increment identity value by more than 900 numbers. This has happened 3 times within last 3 months. Debugged the application locally but could not reproduce. There isn't any pattern or trend observed.

型号:

public class Enquiry
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public Int64 EnquiryId { get; set; }

    [Required]
    public int UserId { get; set; }

    [Required]
    public byte Bid { get; set; }

    ...

    [Required]
    public DateTime Created { get; set; }

    [Required]
    public DateTime Modified { get; set; }
}

public class EnquiryDetail
{
    [Key]
    public Int64 EnquiryId { get; set; }

    [Required]
    public int CreditScore { get; set; }

    [Required]
    public byte BidMode { get; set; }

    public virtual Enquiry Enquiry { get; set; }
}

DBContext:

public class EscrowDb : DbContext
{

    public EscrowDb()
        : base("name=DefaultConnection")
    {

    }
    public DbSet<Enquiry> Enquiries { get; set; }
    public DbSet<EnquiryDetail> EnquiryDetails { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        modelBuilder.Entity<EnquiryDetail>()
            .HasRequired<Enquiry>(ed => ed.Enquiry)
            .WithRequiredDependent(e => e.EnquiryDetail);
    }
}

控制器:

[Authorize]
public class EnquiryController : Controller
{
    private EscrowDb _db = new EscrowDb();

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Create(EnquiryViewModel core)
    {
       var enquiry = new Enquiry();
       // Some code to set properties using passed ViewModel
       ...

       var enquiryDetail = new EnquiryDetail();
       // Some code to set properties using passed ViewModel
       ...

       enquiry.EnquiryDetail = enquiryDetail;

       _db.Enquiries.Add(enquiry);
       _db.SaveChanges();
    }
}

到目前为止，除了标识值被几乎 1000 个数字的大间隙偶尔跳过之外，所有这些代码都运行良好.

All this code has been working fine so far except the identity value getting skipped sporadically by large gaps of almost 1000 numbers.

有人遇到过这样的问题吗?请分享您的想法.

Has anybody come across such kind of issue? Please share your thoughts.

推荐答案

如果您需要消除这些差距，那么您可能不走运.

You may be out of luck here if you need to eliminate these gaps.

我在开发/测试新应用程序时自己遇到了这个问题.根据我阅读的有关 sql server 2012 的内容，我正在直觉 sql azure 中发生的事情.我无法找到有关 sql azure 的任何文档.

I hit this issue myself as I am developing/testing a new application. I'm intuiting what's happening here in sql azure based on what I've read about sql server 2012. I have not been able to find any documentation about this for sql azure.

据我所知，这是一个在 IMO 中被视为错误的功能.在 Sql server 2012 中，Microsoft 添加了创建序列的功能.序列记录了在 1000 块中使用了哪些值.所以假设您的序列正在进行……1、2、3、4、5……然后您的 sql server 重新启动.那么序列已经保存了块 1-1000 已经被使用的事实，所以它会跳转到下一个 1000....所以你的下一个值是 1001, 1002, 1003, 1004 .... 这提高了性能使用序列时插入，但会导致不寻常的空白.对于您的序列，有一个解决方案.在指定序列时添加NOCACHE"参数，以便它不会一次保存 1000 个块.请参阅此处了解更多文档.

From what I've read this is a feature that comes across as a bug IMO. In Sql server 2012 Microsoft added the ability to create sequences. Sequences record what values have been used in blocks of 1000. So lets say your sequence was progressing... 1, 2, 3, 4, 5... and then your sql server restarts. Well the sequence has already saved the fact that the block 1-1000 have already been used so it jumps you to the next 1000.... so your next value are 1001, 1002, 1003, 1004.... This improves performance of inserts when using sequences, but can result in unusual gaps. There is a solution to this for your sequence. When specifying you sequence add the "NOCACHE" parameter so that it doesn't save blocks of 1000 at a time. See here for more documentation.

这成为一个问题的地方是 Identity 列似乎已更改为使用相同的范例.因此，当您的服务器或在这种情况下您的 sql azure 实例重新启动时，您的标识列中可能会出现很大的间隙(1000 个)，因为它将大块缓存为已使用".对于 sql server 2012，有一个解决方案.您可以指定启动标志 t272 以将您的身份恢复为使用旧的 sql server 2008 r2 范例.问题是我不知道(可能不可能)如何在 sql Azure 中指定它.找不到文档.查看此线程了解更多信息有关 sql server 2012 的详细信息.

Where this becomes an issue is that the Identity columns seem to have been changed to use this same paradigm. So when your server, or in this case your sql azure instance restarts you can get large gaps (1000's) in your identity columns because it is caching large blocks as "used". There is a solution to this for sql server 2012. You can specify a startup flag t272 to revert your identity to using the old sql server 2008 r2 paradigm. The problem is that I'm unaware (it may not be possible) of how to specify this in sql Azure. Can't find documentation. See this thread for more details on sql server 2012.

查看 msdn 中的身份文档. 特别是服务器重启或其他故障后的连续值"部分.这是它所说的:

Check the documentation of identity here in the msdn. Specifically the section "Consecutive values after server restart or other failures". Here is what it says:

服务器重启或其他故障后的连续值 –SQL Server可能出于性能原因和某些原因缓存标识值在数据库故障或服务器期间分配的值可能会丢失重新开始.这可能会导致插入时标识值出现间隙.如果间隙是不可接受的，那么应用程序应该使用一个序列带有 NOCACHE 选项的生成器或使用它们自己的机制来生成关键值.

Consecutive values after server restart or other failures –SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use a sequence generator with the NOCACHE option or use their own mechanism to generate key values.

因此，如果您需要连续的值，您可以尝试使用 nocache 指定一个序列，而不是依赖您的身份列.自己没试过，但听起来你很难让它与实体框架一起工作.

So if you need to have consecutive values you could try specifying a sequence with nocache instead of relying on your identity column. Haven't tried this myself, but sounds like you'll have trouble getting this to work with entity framework.

对不起，如果这没有多大帮助，但至少它是关于您的体验的一些信息.

Sorry if this doesn't help much, but at least it's some info on what your experiencing.

这篇关于Windows Azure SQL 数据库 - 标识自动递增列跳过值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！