perform concurrent inserts into same table

Question

I have 2 inserts to run on the same destination table. The ID field in TestTable is the identity column. All the first names in tableA begin with the letter A. All the first names in tableB begin with the letter B. When I run both of the following at the same time I was expecting both of the statements to perform inserts at the same time.

INSERT INTO [dbo].[TestTable]
select top 5,000,000 FirstName
from tableA

INSERT INTO [dbo].[TestTable]
select top 5,000,000 FirstName
from tableB

Instead it looks like the first statement finishes before the second statement begins.

SELECT top 1 min([ID])
FROM [dbo].[TestTable]
where [FirstName] like 'A%'
--result is 1

SELECT top 1 max([ID])
FROM [dbo].[TestTable]
where [FirstName] like 'A%'
--result is 5,000,000

SELECT top 1 min([ID])
FROM [dbo].[TestTable]
where [FirstName] like 'B%'
--result is 5,000,001

SELECT top 1 max([ID])
FROM [dbo].[TestTable]
where [FirstName] like 'B%'
--result is 10,000,000

How do perform inserts simultaneously?

Answer 1

I'm not sure from your explanation whether or not you are running these queries in the same window or in separate windows. If they are in the same window, the first operation is always completed before the second begins. But even if they are runnin in different windows, you are likely to see this behavior. That is due to lock escalation.

If an operation generates too many locks at the same level, by default it will escalate the locks. It does this because each lock takes some memory and too many of them eat up all your memory.

So when your first query starts running, each row inserted from tableA gets an exclusive row level lock in TestTable. But rapidly there are lots of these locks created. Very quickly, you exceed the escalation level and SQL changes the locks to an exclusive TABLE lock on TestTable. Now SQL no longer needs all those row level locks and that relieves a lot of memory pressure on the system.

But now the first process has a table lock on TestTable, so the second process is blocked until the first process finishes.

You can turn this behavior off. To do this run

Alter Table dbo.Testtable SET (LOCK_ESCALATION = DISABLE)

Now both processes can continue to insert rows at the same time. However, depending on what else is going on in your system, how much memory you have, etc, disabling lock escalation can slow your system significantly.

If you do disable lock escalation, it will remain off for that table until you turn it back on with

Alter Table dbo.testtable SET (LOCK_ESCALATION = AUTO)

Tom

Answer 2

Try executing these commands from two different tabs in SSMS although I don't think you would get concurrent inserts even in this case. You may want to use SSIS instead and schedule a job to run the package.

Answer 3

Hi，@Thirston Third

Welcome to Microsoft T-SQL Q&A Forum!

I don't think your idea is theoretically possible because all operations in sql server are log based. Whichever opens the log file first will write the content there. Any operation should have a very small lock. Maybe you can try to use the transaction method to solve it:

START TRANSACTION;  
   INSERT INTO tableA  
   SELECT  ... FROM table1;  
  
   INSERT INTO tableA  
   SELECT ....  
      FROM table2  INNER JOIN TABLE1  
                  ON......;-- (if columns not unique)  
COMMIT;  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

I don't have the time to test this now, but I think you may be jumping to conclusion. The IDENTITY numbers may be allocated at an early stage, so the inserts may still run in parallel.

By the way, does the target table a have a clustered index on the ID column, or is it a heap?