Lock Table While Inserting

Lock table while inserting

BEGIN TRY
 BEGIN TRANSACTION t_Transaction

 TRUNCATE TABLE LargeTable

 INSERT INTO LargeTable
 SELECT * 
 FROM viewLargeView
  WITH (HOLDLOCK)

 COMMIT TRANSACTION t_Transaction
END TRY 
BEGIN CATCH
  ROLLBACK TRANSACTION t_Transaction
END CATCH

Does inserting data into SQL Server lock the whole table?

Holy cow, you've got a lot of questions in here, heh. Here's a few answers:

When inserting a record into this table, does it lock the whole table?

Not by default, but if you use the TABLOCK hint or if you're doing certain kinds of bulk load operations, then yes.

So if you are querying any data from the table will it block until the insert is done (I realise there are ways around this, but I am talking by default)?

This one gets a little trickier. If someone's trying to select data from a page in the table that you've got locked, then yes, you'll block 'em. You can work around that with things like the NOLOCK hint on a select statement or by using Read Committed Snapshot Isolation. For a starting point on how isolation levels work, check out Kendra Little's isolation levels poster.

How long will it take before it causes a deadlock? Will that time depend on how much load is on the server, e.g. if there is not much load will it take longer to cause a deadlock?

Deadlocks aren't based on time - they're based on dependencies. Say we've got this situation:

• Query A is holding a bunch of locks, and to finish his query, he needs stuff that's locked by Query B
• Query B is also holding a bunch of locks, and to finish his query, he needs stuff that's locked by Query A

Neither query can move forward (think Mexican standoff) so SQL Server calls it a draw, shoots somebody's query in the back, releases his locks, and lets the other query keep going. SQL Server picks the victim based on which one will be less expensive to roll back. If you want to get fancy, you can use SET DEADLOCK_PRIORITY LOW on particular queries to paint targets on their back, and SQL Server will shoot them first.

Is there a way to monitor and see what is locked at any particular time?

Absolutely - there's Dynamic Management Views (DMVs) you can query like sys.dm_tran_locks, but the easiest way is to use Adam Machanic's free sp_WhoIsActive stored proc. It's a really slick replacement for sp_who that you can call like this:

sp_WhoIsActive @get_locks = 1

For each running query, you'll get a little XML that describes all of the locks it holds. There's also a Blocking column, so you can see who's blocking who. To interpret the locks being held, you'll want to check the Books Online descriptions of lock types.

If each thread is doing queries on single tables, is there then a case where blocking can occur? So isn't it the case that a deadlock can only occur if you have a query which has a join and is acting on multiple tables?

Believe it or not, a single query can actually deadlock itself, and yes, queries can deadlock on just one table. To learn even more about deadlocks, check out The Difficulty with Deadlocks by Jeremiah Peschka.

Related Topics