Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server database on Azure UNIQUE KEY constraint crash

Tags:

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).

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.

(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)..

When there are no high users traffic everything works ok!

Please help us find a solution