SQL Server lock due to select while inserting

Question

I want to fetch some rows from a table while insertion is being done in that same table. While doing this operation, the table is being locked.

How can I fix this?

kindly help me out.

Thanks in advance

check out the `with (nolock)` lock hint if you don't mind the possibility of dirty reads on your select statements — Tom, Aug 07 '12 at 10:02
The INSERT operation should only lock that it's inserting - not the whole table - unless it inserts a lot of rows (more than 5000) in which case it might do a "lock escalation" and just lock the whole table. Try to insert in group of less than 5000 rows to avoid lock escalation — marc_s, Aug 07 '12 at 10:19
Locking and blocking is there to adhere to the consistency principle. It's a good thing. I agree with @marc_s, ensure you're not escalating to a table-level lock. — , Aug 07 '12 at 12:48

score 2 · Accepted Answer · edited May 23 '17 at 10:34

2

In case if you are using SQL Server 2008, then there is a provision to hint SQL Server to take row level lock.

edited May 23 '17 at 10:34

Community

answered Aug 07 '12 at 12:53

sundar

1 Answers1