Azure UNIQUE KEY约束崩溃的SQL Server数据库

After launching promo campaign of our software yesterday we've met some strange problems with our SQL Server database. This error occurs when users did signup for an account at our website. When it was low user traffic everything seemed to be ok and worked properly but once traffic increased to ~10 signups at a time everything goes down. Once it get down each following request to database threw an error. The only way to fix it was restarting website (until the next crash and so on).

在昨天推出我们软件的促销活动之后，我们遇到了一些我们的SQL Server数据库的奇怪问题。用户在我们的网站上注册帐户时会发生此错误。当用户流量很低时，一切似乎都很好并且工作正常但是一旦流量增加到大约10次注册，一切都会下降。一旦它下来，每个跟随数据库的请求都会引发错误。修复它的唯一方法是重启网站（直到下一次崩溃等）。

This is the error:

这是错误：

Violation of UNIQUE KEY constraint ‘AK_Users_Username’. Cannot insert duplicate key in object ‘dbo.Users’. The duplicate key value is (username). The statement has been terminated.

违反UNIQUE KEY约束'AK_Users_Username'。无法在对象'dbo.Users'中插入重复键。重复键值为（用户名）。该语句已终止。

(Even though we check the username before adding it)

（即使我们在添加之前检查用户名）

Also some another error occurred:

还发生了一些其他错误：

New transaction is not allowed because there are other threads running in the session.

不允许新事务，因为会话中还有其他线程在运行。

This is a piece of code which adds User and its License to database:

这是一段将用户及其许可证添加到数据库的代码：

var db = new CBEntities();

var user = db.Users.Add(new User
        {
            Username = model.Username,
            Firstname = model.FirstName,
            Lastname = model.LastName,
            Email = model.Email,
            Password = model.Password,
            EmailConfirmed = 0,
            Country = model.Country,
            EmailSubscribed = model.Newsletter != null && model.Newsletter.Value ? 1 : 0,
            Role = "User",
            SignUpDate = DateTime.UtcNow
        });

db.SaveChanges();

//create a initial trial license for user
var lic = db.Licenses.Add(new License
        {
            ExpirationDate = DateTime.UtcNow.AddDays(21),
            UserID = user.ID,
            Key = "",
            PackageID = coupon.ID,
            Status = (int)LicenseStatus.Active,
            Type = (int)LicenseTypes.TRIAL
        });

db.SaveChanges();
user.LicenseID = lic.ID;

db.SaveChanges();

Site and database are running on Azure hosting. We did scale a website to STANDARD plan and it was using even several instances with 2 cores and 3.5 Gb memory. It didn't solve the error. Database currently has STANDARD service tier with S2 perf. level. (It's linked to website)..

站点和数据库在Azure主机上运行。我们确实将网站扩展到STANDARD计划，它甚至使用了具有2个内核和3.5 Gb内存的多个实例。它没有解决错误。数据库目前具有带有S2 perf的STANDARD服务层。水平。 （它链接到网站）..

When there are no high users traffic everything works ok!

当没有高用户流量时，一切正常！

Please help us find a solution

请帮我们找到解决方案

3 个解决方案

#1

Your code is structured like this.

您的代码结构如下。

1. Check For Duplicate Username
2. 检查重复的用户名
3. Add a User
4. 添加用户
5. Save the User to database
6. 将用户保存到数据库
7. Add a License
8. 添加许可证
9. Save the License to database
10. 将许可证保存到数据库
11. Associate the License with the user
12. 将许可证与用户关联

Here each action like create user, create license and associate license are individually atomic actions. What you should have is that all these actions together should be considered atomic. I would strongly recommend to have a transaction boundary to consider user creation, license creation and its association one single atomic unit rather than treating them separately.

这里创建用户，创建许可证和关联许可证等每个操作都是单独的原子操作。你应该拥有的是所有这些行动应该被认为是原子的。我强烈建议有一个事务边界来考虑用户创建，许可证创建及其与单个原子单元的关联，而不是单独处理它们。

Regarding the unique key error

关于唯一键错误

Violation of UNIQUE KEY constraint ‘AK_Users_Username’. Cannot insert duplicate key in object ‘dbo.Users’. The duplicate key value is (username). The statement has been terminated.

违反UNIQUE KEY约束'AK_Users_Username'。无法在对象'dbo.Users'中插入重复键。重复键值为（用户名）。该语句已终止。

In the above sequence, there can be 2 different connections which check for the same username say 'abc.def' and try to create a user. However only one of these requests will be able to create a new user record in the database.

在上面的序列中，可以有2个不同的连接，检查相同的用户名如'abc.def'并尝试创建用户。但是，这些请求中只有一个能够在数据库中创建新的用户记录。

The second record will fail with the unique key constraint violation.

第二条记录将因唯一键约束违规而失败。

You can minimise such errors by doing another check for the username again just before you create your user. This will not completely remove the error but minimise such occurrences.

您可以在创建用户之前再次检查用户名，从而最大限度地减少此类错误。这不会完全消除错误，但会最大限度地减少这种情况。

To completely Remove such errors

要完全删除此类错误

1. Start a Serializable transaction
2. 启动Serializable事务
3. Check the username again
4. 再次检查用户名
5. Do normal processing...
6. 做正常处理......
7. Associate the License with the user
8. 将许可证与用户关联
9. Commit the transaction
10. 提交交易

Note: Using Serializable scope may cause blocking as this is the most isolated scope.

注意：使用Serializable作用域可能会导致阻塞，因为这是最孤立的作用域。

Reference Article on Serializable

关于Serializable的参考文章

Hope this helps

希望这可以帮助

#2

You haven't enclosed the DbContext in a using statement. It should be disposed of like this:

您没有将DbContext包含在using语句中。它应该像这样处理：

using (var db = new CBEntities()) {

  var user = db.Users.Add(new User
          {
              Username = model.Username,
              Firstname = model.FirstName,
              Lastname = model.LastName,
              Email = model.Email,
              Password = model.Password,
              EmailConfirmed = 0,
              Country = model.Country,
              EmailSubscribed = model.Newsletter != null && model.Newsletter.Value ? 1 : 0,
              Role = "User",
              SignUpDate = DateTime.UtcNow
          });

  db.SaveChanges();

  //create a initial trial license for user
  var lic = db.Licenses.Add(new License
          {
              ExpirationDate = DateTime.UtcNow.AddDays(21),
              UserID = user.ID,
              Key = "",
              PackageID = coupon.ID,
              Status = (int)LicenseStatus.Active,
              Type = (int)LicenseTypes.TRIAL
          });

  db.SaveChanges();
  user.LicenseID = lic.ID;

  db.SaveChanges();
}

Disposing the DbContext closes the connection to the database. That would explain why not disposing the DbContext causes a too many connections error.

处理DbContext会关闭与数据库的连接。这可以解释为什么不处理DbContext会导致连接错误太多。

When you have little activity on the site, the connections time-out which explains why you don't have the error under a light load.

当您在网站上的活动很少时，连接超时就解释了为什么在轻负载下没有错误的原因。

#3

As mentioned in the previous answers, you should always enclose your DbContext in using statements. Not only in this piece of code, but every time you use it.

如前面的答案所述，您应该始终将DbContext包含在using语句中。不仅在这段代码中，而且每次使用它时。

The Unique Key constraint problem however can be caused by various other reasons. We cannot see the code you are using for checking the already existing usernames, but I guess it is written in C#. When you insert a new line SQL Server checks the user name constraint for you. There are a lot of differences in string comparison in SQL server compared to C#.

然而，唯一键约束问题可能由各种其他原因引起。我们无法看到您用于检查已存在的用户名的代码，但我想它是用C＃编写的。当您插入新行时，SQL Server会为您检查用户名约束。与C＃相比，SQL Server中的字符串比较存在很多差异。

First of all SQL Server relies on the default collation of the DB if no collation is specified on the column. The default collation of SQL Azure is SQL_Latin1_General_CP1_CI_AS. CI means case insensitive AS means Accent Sensitive. This means that two string values are considered equal when they only differ in the casing. So user123 is equal to USER123 and User123 and so on.

首先，如果在列上未指定排序规则，SQL Server将依赖于数据库的默认排序规则。 SQL Azure的默认排序规则是SQL_Latin1_General_CP1_CI_AS。 CI表示不区分大小写的AS表示Accent Sensitive。这意味着当两个字符串值仅在外壳中不同时，它们被认为是相等的。因此user123等于USER123和User123，依此类推。

The other thing that can cause problems is the handling of trailing spaces. SQL Server compares stings basically by comparing their characters one by one. If one string is shorter than the other it adds extra spaces to the end of the shorter one to make their length equal. This way 'User123' is equal to 'User123 ' and to any string that starts with User123 and ends with a number of spaces. This means that you should always trim the usernames before saving them.

另一个可能导致问题的是处理尾随空格。 SQL Server基本上通过逐个比较它们的字符来比较stings。如果一个字符串比另一个字符串短，则在较短字符串的末尾添加额外的空格以使它们的长度相等。这种方式'User123'等于'User123'以及以User123开头并以多个空格结束的任何字符串。这意味着您应该在保存之前始终修剪用户名。