October 6, 2008 at 10:59 pm
Cross posting from MSDN Forums
Hello,
There is something that I'm seeing in my tests that is not intuitive to me. I would imagine that a single statement such as INSERT INTO SELECT is always atomic, which seems not to be the case.
If we have a simple table with a guid as the primary key and the second column, of varchar(max), then as simple statement as this:
INSERT INTO deleteme
SELECT #t.id, 'test' FROM #t
LEFT JOIN deleteme ON deleteme.id = #t.id
WHERE deleteme.id IS NULL
will fail with the primary key violation in the concurrent scenario (i.e. when inserting the same key that is absent in deleteme from several thread at the same time) with the default transaction isolation level of READ COMMITTED:
Error: Violation of PRIMARY KEY constraint 'PK_DeleteMe'. Cannot insert duplicate key in object 'dbo.deleteme'.
This is quite surprising, inserting a row that is not already there must be quite a common task that many people do all the time. Because of this I expect that there should be a simple resolution.
What happening here is two threads are trying to insert exactly the same ID in deleteme at the same time. So the SELECT part of the statement runs in both threads makes sure that the certain ID is not in the deleteme yet and then both threads try to run the INSERT part of the statement. One thread does this first successfully and the other throws a error (primary key violation) because now there is the key it's trying to insert in the delete me there. One would think that the check/insert should be atomic, but obviously it is not.
The code above works fine if modified like this:
INSERT INTO deleteme
SELECT #t.id, 'test' FROM #t
LEFT JOIN deleteme WITH (UPDLOCK, SERIALIZABLE) ON deleteme.id = #t.id
WHERE deleteme.id IS NULL
But this effectively mean holding a strong lock until the end of the current transaction. If a transaction happens to be long this is not going to perform well.
Here is a sample c# code that I created to provide a minimal sample of the problem:
/* This code is to illustrate the concurrency issue in a minimal example
* Create a table in your database with the following definition:
* =======[Cut]========
CREATE TABLE [dbo].[DeleteMe](
[id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_DeleteMe_id] DEFAULT (newid()),
[Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] PRIMARY KEY CLUSTERED
([id] ASC)
WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
* =======[Cut]========
* Clear/Truncate the table before the test if you are paranoid about generating duplicate guids:
* DELETE deleteme
* The following error is returned if you let this code to run for awhile:
* Error: Violation of PRIMARY KEY constraint 'PK_DeleteMe'. Cannot insert duplicate key in object 'dbo.deleteme'.
*/
using System;
using System.Text;
using System.Threading;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace TestSQLConcurency
{
class Program
{
static void Main(string[] args)
{
// Put your connection string here (or use mine)
string connectionString
= "Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=TestDb;Data Source=localhost";
// This is the number of threads trying to insert a guid at the same time.
int numberOfThreads = 10;
// The number of guids to insert. On my tests 100 was enough to genreate the error
// in most cases.
int numberOfCycles = 1000;
// This is how we dump the exception.
AppDomain.CurrentDomain.UnhandledException += new UnhandledExceptionEventHandler(CurrentDomain_UnhandledException);
// This is the actual SQL to execute, examine it carefully
string commandText =
@"CREATE TABLE #t(
[id] [uniqueidentifier] NOT NULL
)
-- In this case we are just putting one only row in the temporary table
-- in a real life situation there can be more
insert into #t values ('%param%')
-- this is the default isolation level, it is set explicitly here
-- just to demonstrate that it is the actual level the transaction is executed under
set transaction isolation level read committed
-- The error is reproducible either with or without the begin/commit pair
begin transaction
insert into deleteme
select #t.id, 'test' from #t
left join deleteme on deleteme.id = #t.id
where deleteme.id is null
commit transaction
drop table #t
";
// CommandExecutor is a helper class the main goal of which is to keep
// the connections that we are using open. Admittedly connection pooling works
// just as well, but we have no visibility for it, so it's here to be on the
// safe side
CommandExecutor[] ces = new CommandExecutor[numberOfThreads];
for (int k = 0; k < numberOfThreads; k++)
{
ces[k] = new CommandExecutor(connectionString);
}
// This is to be completely sure that we don't have clashing GUIDS.
// The probability that we do have them is very small but this way
// we'll be 100% sure we don't have them is we start with an empty table
List<Guid> usedGuidsList = new List<Guid>();
for (int k = 0; k < numberOfCycles; k++)
{
Guid guid = Guid.NewGuid();
// This is our unique guid safegard
if (usedGuidsList.Contains(guid))
{
Console.WriteLine("Duplicate guid {0}", guid);
continue;
}
else
{
usedGuidsList.Add(guid);
}
for (int j = 0; j < numberOfThreads; j++)
{
ces[j].CommandText = commandText.Replace("%param%", guid.ToString());
ces[j].Guid = guid.ToString();
Thread t = new Thread(new ParameterizedThreadStart(ExecuteThread));
t.Name = string.Format("Thread {0}", j);
ces[j].Thread = t;
t.Start(ces[j]);
}
}
// Wait for all the threads to finish before exiting
for (int k = 0; k < numberOfThreads; k++)
{
ces[k].Thread.Join();
}
}
static void CurrentDomain_UnhandledException(object sender, UnhandledExceptionEventArgs e)
{
// If an exception has happened dump it to the console and exit.
Exception ex = (Exception) e.ExceptionObject;
StringBuilder sb = new StringBuilder();
while (ex != null)
{
sb.AppendLine(ex.Message);
sb.AppendLine(ex.StackTrace);
ex = ex.InnerException;
}
Console.Write("Error: {0}", sb.ToString());
Environment.Exit(-1);
}
static void ExecuteThread(object param)
{
// Make sure that only one threas used a connection at a time
// i.e. next thread in the same connection slot will wait for
// the previous thread to finish
lock (param)
{
CommandExecutor ce = (CommandExecutor)param;
Console.WriteLine("{0} {1}", ce.Guid, ce.Thread.Name);
ce.Execute();
}
}
class CommandExecutor
{
private SqlConnection m_connection;
private SqlCommand m_command;
private string m_guid;
private Thread m_thread;
public CommandExecutor(string connectionString)
{
m_connection = new SqlConnection(connectionString);
m_connection.Open();
}
public void Execute()
{
m_command.ExecuteNonQuery();
}
public string CommandText
{
set
{ lock (this)
{
m_command = new SqlCommand(value, m_connection);
}
}
}
public string Guid
{
get { return m_guid; }
set { m_guid = value; }
}
public Thread Thread
{
get { return m_thread; }
set { m_thread = value; }
}
}
}
}
If anyone knows a generic solution to this problem please share.
Also in SQL Server Books Online it is stated :
•Serializable (the highest level, where transactions are completely isolated from one another
This is not entirely correct. Two Serializable transactions still can deadlock on each other. You can reproduce this if you change the SQL code above to:
INSERT INTO deleteme
SELECT #t.id, 'test' FROM #t
LEFT JOIN deleteme WITH (SERIALIZABLE) ON deleteme.id = #t.id
WHERE deleteme.id IS NULL
or just change the transaction isolation level to SERIALIZABLE and run it multi-threaded using the sample above.
Andrew.
October 7, 2008 at 1:09 am
zespri (10/6/2008)
Also in SQL Server Books Online it is stated :
•Serializable (the highest level, where transactions are completely isolated from one another
This is not entirely correct. Two Serializable transactions still can deadlock on each other. You can reproduce this if you change the SQL code above to:
That's not saying that they can't deadlock. Serializable deadlocks easier than any of the other isolation levels.
What it's saying is that any changes made by one transaction are completely hidden from any other until the commit occurs.
As for the concurrency problem, it is an atomic operation (it succeeds or fails as a single unit). The problem is that the select takes a shared lock out which is compatible with other shared locks. Hence two threads can be reading at the same time. When the insert starts, that lock is converted to an exclusive lock for the insert.
The Serializable hint shouldn't be needed, unless there's more to this than I can see. It should be sufficient to do the select with the xlock hint, thus taking an exclusive lock straight out. You will, obviously, have less concurrency.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 5:02 am
Gail,
thank you for your response. I tried the xlock hint like this
insert into deleteme
select #t.id, 'test' from #t
left join deleteme with (xlock) on deleteme.id = #t.id
where deleteme.id is null
I still observed the very same problem with this error:
Error: Violation of PRIMARY KEY constraint 'PK_DeleteMe'. Cannot insert duplicate key in object 'dbo.deleteme'.
This is kind of tricky. This exclusive lock would be taken on what? On the row that is missing in deleteme table? The serializable hint enforces key range lock, so that no other thread can insert a record with a given key into the deleteme table. It seems that just xlock is not enough.
As far as isolation of serializable transaction concerned, I think you are right this is what MSDN meant. Perhaps it's just clumsy wording. In my understanding if two serializable transaction deadlock on each other they can't be called "completely isolated", but I also can see what the authors of the article could mean by that.
Thank you again for replying.
Andrew.
October 7, 2008 at 5:31 am
Good point. You may need the serialisable then. Leave the xlock. It saves SQL having to convert an updlock into an xlock for the insert.
As for the isolated, in terns of transactions that has a very specific meaning. It's taken from the ACID requirements for transactions
A - Atomicity - The operation succeeds or fails as a unit
C - Consistency - The operation will always leave the DB in a consistent state
I - Isolation - Changes made by one transaction aren't visible to another.
D - Durability - Changes persist after they have been committed
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 6:59 am
This is what I'm seeing (sp_lock) in the Management Studio when I'm running the statement with the updlock:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
58 8 0 0 DB S GRANT
58 8 629577281 1 KEY (b90139dd8476) X GRANT
58 8 629577281 1 PAG 1:201 IX GRANT
58 8 629577281 1 KEY (1b0366f2ff9e) RangeS-U GRANT
58 8 629577281 0 TAB IX GRANT
And this is with the xlock:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
58 8 0 0 DB S GRANT
58 8 629577281 1 KEY (b90139dd8476) RangeX-X GRANT
58 8 629577281 1 PAG 1:201 IX GRANT
58 8 629577281 1 KEY (1b0366f2ff9e) RangeX-X GRANT
58 8 629577281 0 TAB IX GRANT
Of course I can't run half a statement so I can't tell what locks are taken after the select but before the insert. (Could look at the profiler though)
And for comparison, the locks without the serializable:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
58 8 0 0 DB S GRANT
58 8 629577281 1 KEY (b90139dd8476) X GRANT
58 8 629577281 1 PAG 1:201 IX GRANT
58 8 629577281 0 TAB IX GRANT
These locks are looking quite different on our production system where the table in question is big (both in terms of records and columns) and more than one record is getting inserted. On this table I'm seeing (ffffffffffff) key range locks which is effectively locking the whole table, similar to this:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
58 8 0 0 DB S GRANT
58 8 629577281 1 KEY (b90139dd8476) X GRANT
58 8 629577281 1 PAG 1:126 IX GRANT
58 8 629577281 1 KEY (ffffffffffff) RangeS-U GRANT
58 8 629577281 0 TAB IX GRANT
Are you convinced, that xlock is better here, since it seems that with updlock some locks are less restrictive (thus better for concurrency)? Also I'm not quite sure what is the second KEY lock is (1b0366f2ff9e), the first one (b90139dd8476) is obviously corresponds to the key being inserted.
EDIT: I was able to find out that second KEY lock represents the range from the the key being inserted to the next key (in ascending order) that exists in the table as of the moment of query.
EDIT2: Very interesting reading here: SQL Server 2005 Waiting and Blocking Issues. It gives at least two very useful things: 1) a way to find out what row the hash may resulted from (this is how I have found out the EDIT1. 2) (ffffffffffff) is not locking the whole table - it is locking the range above the highest key present in the table. Now that makes more sense. If we are inserting a key that is bigger than any key that is already there this is the lock we are getting
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply