Executing Multiple Insert into query says table locked

Question

I trying to move table1 data which have 3000000 rows to table2.

Table rows are moved on the basis of row number, So i execute three concurrent Insert into queries by dividing the rows into 1000000 each records for each three queries based on row numbers in where condition.

My destination table is table2 and source is table1 for all 3 Queries.

Now problem is while i executing 3 queries, first one is running while other two queries fall in error that states table is blocked.

Please help me what i did wrong, and how can i execute three queries concurrently !!!!!

Answer 1

Given the large number of rows, there must be lock escalation and one session is obtaining an exclusive lock on the table, while the other sessions are denied access. You can try this -

    USE MASTER;
    ALTER DATABASE <yourDB>;
    SET ALLOW_SNAPSHOT_ISOLATION ON;
    GO

    USE <yourDB>;
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
    GO

    <your SQL here>

This will allow you to run multiple update statements against the same table, without locking.