will delete operation block insert ?

I wonder if when we do delete in sql table, will it cause insert being blocked ?
Who is Participating?
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Yes, it will..
"An INSERT statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. For more information, see Locking in the Database Engine."
Ref: http://msdn.microsoft.com/en-us/library/ms174335.aspx
lcohanConnect With a Mentor Database AnalystCommented:
I suggest you batch up your DELETE's and use the WITH(ROWLOCK) hint for the delete statement itself then manually test your delete batch size starting low to high before adding them up to production code/environment.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.