Explicit lock to table(s) in SQL Server?

Question

Is it possible to do an explicit shared lock on a table in SQL Server?

I need to do the following:

• shared lock table
• copy table to temp table, for manipulation
• exclusive lock table
• copy stuff from tempTable to table
• release all locks

Basically, I want to be sure that nothing is added to the table, while I'm doing stuff to the temp table, but I still want it to be readable.

For some reason, putting the whole thing in a transaction causes a deadlock in some of the stuff I do to the temp table, so that is not an option.

Solution

The only way to hold table locks is to keep a transaction open. So we may need more information about this "deadlock in some of the stuff I do to the temp table".

Depending on the task, the temp table may not be necessary at all - it sounds like you're building something from a procedural mindset rather than a set-based one.

The basic sketch for what you've outlined would be:

/* Create temp table here */
BEGIN TRANSACTION
INSERT INTO #Table (/* Columns */)
SELECT /* Columns */ FROM SourceTable WITH (UPDLOCK,HOLDLOCK)

/* Work within temp table here */
UPDATE st
SET
   /* Set new column values based on temp table */
FROM
   SourceTable st
       inner join
   #Table t
       on
           /* Key column join here */
COMMIT

You need to specify an UPDATE lock rather than a shared lock, which is effectively "A shared lock now, but I intend to go exclusive later on within the same transaction", to prevent a possible deadlock.