Lock on table while inserting data in SQL Server database

sql-server database locks
user2986108 picture user2986108 · Jul 10, 2014 · Viewed 11.5k times · Source

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.

Answer

marc_s picture marc_s · Jul 10, 2014

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.

Related questions

SQL Server database: INSERT query blocked with wait type LCK_M_IX

Our Java application cleans up the data from log table through a cron job. Here is the query which is executed for clean up: DELETE FROM AU_TRANSACTIONDATA WHERE AU_ACTIVITYENDTIME != 0 AND AU_ACTIVITYENDTIME <= 1464570001151 We have an index on …

Read More
Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More