Ayende @ Rahien

Mik commented on Serialazble isolation level on rows that does not exists

Sat, 19 Jan 2008 16:06:29 GMT

Not sure why you need transaction here. Anyway, this code works with no exceptions in multythreaded environment: private static void InsertToDatabase(object state) { StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append("IF NOT EXISTS(SELECT Id FROM Items WHERE Id = @id)"); sqlQuery.Append("BEGIN"); sqlQuery.Append(" INSERT INTO Items (Id) VALUES(@id)"); sqlQuery.Append("END"); int value = 0; while (value < 12000) { value += 1; try { using (SqlConnection connection = new SqlConnection("data source=MARGARITKA\\SQLEXPRESS;integrated security=sspi;initial catalog=test")) { connection.Open(); using (SqlCommand command = connection.CreateCommand()) { command.CommandText = sqlQuery.ToString(); command.Parameters.AddWithValue("id", value); command.ExecuteNonQuery(); } } } catch (SqlException e) { if (e.Number == 1205) //transaction deadlock { Console.WriteLine("{0}: Exception!!!", Thread.CurrentThread.Name); Console.WriteLine(" - {0}", e.Message); continue; } Console.WriteLine(e); } } } and function for running threads is here: static void Main(string[] args) { string[] threadNames = new string[] {"1 - ibm", "2 - dec", "3 - intel", "4 - whatever", "5 - microsoft"}; List threads = new List(); foreach (string name in threadNames) { Thread thread = new Thread(InsertToDatabase); thread.Name = name; thread.Start("go " + name); threads.Add(thread); } foreach (Thread thread in threads) { thread.Join(); } }

Evan commented on Serialazble isolation level on rows that does not exists

Fri, 18 Jan 2008 23:32:13 GMT

Key-Range Locks: http://msdn2.microsoft.com/en-us/library/ms191272.aspx

Stefan Wenig commented on Serialazble isolation level on rows that does not exists

Fri, 18 Jan 2008 09:49:47 GMT

SQL Server usually locks only the rows that are affected by an operation (although lock escalation can lead to table locks when holding a lot of individual locks would seem less efficient). On serializable level, ranges of non-existing records need to be locked too. Whenever possible, SQL Server attempts to lock index pages to achieve this. When operations on isolation level serializable lead to table locks even with low numbers of records, you should review your indexing strategy in relation to the affected queries.

Sasha Goldshtein commented on Serialazble isolation level on rows that does not exists

Fri, 18 Jan 2008 09:16:20 GMT

The serializable isolation level was constructed to prevent phantoms. Therefore, it will not allow a transaction to insert a phantom row which would violate the semantics of another transaction with serializable isolation. (The lock in this case might be on table granularity, which is really bad contention-wise.)

Ken Egozi commented on Serialazble isolation level on rows that does not exists

Fri, 18 Jan 2008 07:48:54 GMT

What about the client putting a guid on the entity to be entered, the db having a unique index on the said guid, and then simply the first insert would work, the others would either get a unique violation error (the poor ones that would come in the milliseconds after the first) and the others would just see that the line exists and won't insert. this should work with a non restrictive lock. and having a guid on entities in distributed scenarios is a good practice anyway.