0

SQL Server locks a table while inserting preventing multiple parallel inserts

I'm trying to insert into a target table from multiple source tables in parallel to speed up the process. The source tables have 5 million+ records.

I have no indexes, id columns or anything defined on the table.

create table target(col1 varchar(255), col2 varchar(255),col3 varchar(255))

Process 1: 

insert into target (col1, col2, col3) as select t1_1, t1_2, current_timestamp from table1;

Process 2: 

insert into target (col1, col2, col3) as select t2_1, t2_2, current_timestamp from table2;

Process 3: 

insert into target (col1, col2, col3) as select t3_1, t3_2, current_timestamp from table3;

I expected the inserts to work in parallel.

The first process works, but others failed. But I keep receiving the following error:

1204, b'The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.DB-Lib error message 20018, severity 19:\nGeneral SQL Server error: Check messages from the SQL Server\n'

Does SQL Server allows multiple concurrent processes to insert large volumes of data to be inserted in parallel ? If so, how do I prevent these locks?

SS_DBA
  • 2,403
  • 1
  • 11
  • 15
jvergara
  • 101
  • 2
  • 4
  • Inserting 5 million plus rows repeatedly is going to be slow. Why not use [Bulk Insert](https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017) Also, once you are done a table with no indexes and more than 15 million rows is going to be horrific to query. You also should not try to do these all at the same time. The amount of locking required to insert 5 million rows is pretty signficant. – Sean Lange Oct 07 '19 at 15:51
  • Give this one a read: https://stackoverflow.com/questions/39404875/inserting-large-number-of-records-without-locking-the-table – critical_error Oct 07 '19 at 17:01

0 Answers0