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.

Well, there's an obvious deadlock in the above, if two "things" try to perform the above task at the same time - I.e. they'd both have shared locks, so neither could obtain an exclusive lock. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Mar 3, 2011 at 7:52
The problem is, I get a deadlock even when running the whole thing in the same transaction. I will create a seperate question on the deadlock problem, since it seems I need to avoid that one, instead of trying to work around it. — Joda
– Joda, Commented Mar 3, 2011 at 8:15

Damien_The_Unbeliever · Accepted Answer · 2011-03-03 08:00:49Z

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.

Collectives™ on Stack Overflow

Explicit lock to table(s) in SQL Server?

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related