Lock on table while inserting data in SQL Server database

Question

I have question on lock on table in SQL Server while inserting data using multiple processes at a single time into same table.

Here are my questions on this,

1. Is it default behavior of SQL server to lock table while doing insert?
2. if yes to Q1, then how we can implicitly mention while inserting data.
3. If I have 4 tables and one table has foreign keys from rest of the 3 tables, on this scenario do I need to use the table lock explicitly or without that I can insert records into those tables?

Please help me to understand the same.

Scolecite

Re 2 Do you mean "explicitly mention"? Re 3 Explain exactly what you are doing to what tables in what order. Also If you know certain shared actions cannot conflict say so and why. – Skippet

Answer 1

Is it default behavior of SQL server to lock table while doing insert?

No. SQL Server by default locks by row - so new rows being inserted are locked - but not the whole table.

This will change if you insert more than 5000 rows in a single transaction. In that case, keeping that many individual locks just becomes too much and SQL Server will do a lock escalation and lock the entire table instead.

Vulcan

so this means in sql server it does not allow insert 2 rows to a table simultaniouly. how about oracal db does it also use row locks? – Hackworth

@user2781812: sure SQL Server allows multiple inserts at once - as long as no single transaction locks the whole table! – Vulcan

Do you have a MSDN reference? – Disorder

@alswl: technet ok? Lock Escalation (Database Engine) – Vulcan

Answer 2

1. It depends on the size of the table and settings on the clustered index if one exists. If the CI has page locks and row locks allowed, then those are the defaults. You will see lock escalation if multiple pages are locked, potentially up to a table lock.

2. If you want to explicitly lock the table, you can use the TABLOCK hint on the insert (INSERT INTO MyTABLE WITH (TABLOCK)...

3. I'm not sure what you mean here.

Durnan