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

问题描述

目前使用实体框架5.使用codeFirst初始发展阶段上的ASP.Net MVC 4应用工作。但是，现在已经禁用了自动迁移，并直接在设计新表使用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();
    }
}

这一切code已工作的罚款，到目前为止，除了标识值越来越近的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 Azure上我读过有关SQL Server 2012.我一直没能找到这方面有任何文档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.

从我读过这是要像一个bug IMO的功能。 SQL Server 2012中微软加入到创建序列的能力。序列记录什么样的价值观已在1000块被用来因此，可以说你的序列进展... 1，2，3，4，5 ...然后你的SQL服务器重新启动。那么序列已经保存的事实，块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.

如果这成为一个问题是，标识列似乎已更改为使用相同的模式。所以你的服务器，或在这种情况下，你的SQL实例蔚蓝重新启动时，你可以在你的身份列获得较大的差距（1000年），因为它是缓存大盖帽二手。还有就是一个解决方案为SQL Server 2012可以指定一个启动标志T272恢复您的身份来使用旧的SQL Server 2008 R2的范例。问题是，我不知道如何在SQL Azure中指定此（它可能无法）。无法找到文档。 <一href=\"https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity\">See该线程的详细信息的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.

所以，如果你需要你可以尝试用非缓存指定的顺序，而不是依赖于您的身份列连续值。没有尝试过这个自己，<一个href=\"http://social.msdn.microsoft.com/Forums/en-US/a44d5ef8-3dd3-4f3f-bb0c-2f21c4ee6e68/ef5-$c$c-first-does-not-support-sequences-in-sql-server-2012\">but听起来像是你将有麻烦这与实体框架的工作。

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屋！