CODEWITHSUNDEEP
sql-serverdatabaselocks
user2986108
user2986108

Reputation: 105

Lock on table while inserting data in SQL Server database

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.

Upvotes: 6

Views: 17383

Answers (2)

JNK
JNK

Reputation: 65217

  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.

Upvotes: 6

marc_s
marc_s

Reputation: 755371

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.

Upvotes: 14

Related Questions