I want to insert few million records into a table in my production database. I am not updating any existing rows.I am just doing
INSERT INTO TAB1
This table has an Identity column as primary key and a non clustered key.
When i do this i get blocking and then it gets escalated to chain of bolocking.
I know that by default SQL Server doesn't do
TABLE lock and default isolation level is READ COMMITTED.
Since i am inserting new rows into the table,i am sure that the application is not acquiring any shared lock(select while insert scenario).
I am confused as to why i am getting this blocking and then chain of block.
I am thinking of giving an hint to perform ROW LEVEL lock only or use any other lock hints.
The target table into which i am inserting rows is being replicated using
transactional replication.I am not sure whether it is affecting the insert process.