Windows Azure SQL数据库 - 身份自动增量列跳过值 [英] Windows Azure SQL Database - Identity Auto increment column skips values
问题描述
最近，在生产中发现了一个奇怪的问题。其中一个最初设计的表中的记录跳过了自动增量标识值超过900个数字。这在过去3个月内发生了3次。在本地调试应用程序，但无法复制。没有任何模式或趋势观察。
型号：
public class Inquiry
{
[Key]
[DatabaseGeneratedAttribute（DatabaseGeneratedOption.Identity）]
public Int64 EnquiryId {get;组;
[必需]
public int UserId {get;组; }
[必需]
public byte Bid {get;组;
...
[必需]
public DateTime Created {get;组; }
[必需]
public DateTime Modified {get;组;
}
public class EnquiryDetail
{
[Key]
public Int64 EnquiryId {get;组;
[必需]
public int CreditScore {get;组;
[必需]
public byte BidMode {get;组; }
public virtual查询查询{get;组; }
}
DBContext：
public class EscrowDb：DbContext
{
public EscrowDb（）
：base（name = DefaultConnection）
{
}
public DbSet< Inquiry>查询{get;组; }
public DbSet&EnquiryDetail> EnquiryDetails {get;组;
protected override void OnModelCreating（DbModelBuilder modelBuilder）
{
modelBuilder.Conventions.Remove< PluralizingTableNameConvention>（）;
modelBuilder.Entity< EnquiryDetail>（）
.HasRequired< Inquiry>（ed => ed.Enquiry）
.WithRequiredDependent（e => e.EnquiryDetail）;
}
}
控制器： p>
[授权]
public class EnquiryController：Controller
{
private EscrowDb _db = new EscrowDb ）;
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create（EnquiryViewModel core）
{
var inquiry = new Inquiry（）;
//一些使用传递的ViewModel设置属性的代码
...
var enquiryDetail = new EnquiryDetail（）;
//一些使用传递的ViewModel设置属性的代码
...
enquiry.EnquiryDetail = enquiryDetail;
_db.Enquiries.Add（询问）;
_db.SaveChanges（）;
}
}
所有这些代码一直工作正常，除了身份价值被几乎1000个数字的巨大差距偶然跳过。
有没有人遇到过这样的问题？请分享您的想法。
如果您需要消除这些差距，您可能会失去运气。
在开发/测试新应用程序时，我自己碰到了这个问题。我根据我读过的关于sql server 2012的sql azure发生了什么。我无法找到有关sql azure的任何文档。
从我读过的这是一个功能，作为一个错误IMO。在Sql server 2012中，Microsoft添加了创建序列的功能。序列记录在1000块中使用的值。所以让我们说你的序列正在进行... 1，2，3，4，5 ...然后你的sql服务器重新启动。顺序已经保存了块1-1000已被使用的事实，因此它将跳转到下一个1000 ....所以你的下一个值是1001，1002,1003，1004 ....这提高了性能插入时使用序列，但会导致异常的间隙。你的序列有一个解决方案。指定序列时，添加NOCACHE参数，使其不能一次保存1000个块。 请参阅此处获取更多文档。
在这个问题上，身份列似乎已经被改变为使用这个相同的范例。所以当你的服务器，或者在这种情况下，你的sql azure实例重新启动，你可以在你的标识列中获得大的差距（1000），因为它是将大块缓存为used。 sql server 2012有一个解决方案。您可以指定启动标志t272将您的身份还原为使用旧的sql server 2008 r2范例。问题是我不知道（可能不可能）如何在sql Azure中指定这一点。找不到文件。 查看此主题以获取更多信息详细信息，请访问sql server 2012。
在msdn中检查身份的文档。具体来说，服务器重启或其他故障后的连续值部分。这是它所说的：
服务器重启或其他故障后的连续值 -SQL Server
由于性能原因，可能会缓存标识值，并且某些
分配的值可能在数据库故障或服务器
重新启动期间丢失。这可能导致插入时身份值的差距。如果
的差距不可接受，那么应用程序应该使用序列
生成器与NOCACHE选项或使用自己的机制
生成键值。
所以如果你需要连续的值，你可以尝试使用nocache指定一个序列，而不是依靠你的标识列。没有尝试过这个，但听起来像你将遇到麻烦得到这与实体框架一起使用
对不起，如果这不太有帮助，但至少这是一些关于你的经历的信息。
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.
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.
Model:
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);
}
}
Controller:
[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();
}
}
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.
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.
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.
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.
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:
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.
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屋！
