Resolving temp table locking issues

Resolving temp table locking issues

Post by Zidga » Sat, 19 Jun 2004 19:39:34

I am working on enhancing the performance of a very large
transactional sql svr database application. The app has used temp
tables frequently against a SQL Server 2000 database. The database is
experiencing slowdowns and lengthy blocks, particularly when it is

Recently I have found many articles on the web warning about
performance issues and locking problems with temp tables. Each
article explains it differently. One article simply said not to use
them because it locks the tempdb. Another just said "objects" are
locked. Another specified the tempdb is locked only while the
database is trying to define the temporary table on a SELECT INTO
command. Still another said it was locked during a transaction.

I tried a number of tests in Query Analyzer to validate the locking,
trying to ascertain what was locked and when. So far I have not been
able to validate how significant the locking problem is and whether
this could be the source of the blocks.

I would appreciate a clarification on this, and possibly some sample
code, perhaps against the Northwinds database, that could show the
effect of the locking. If it is a problem, I would like to
demonstrate it to the other developers. The questions are:

Is the tempdb only locked during the time the table is being created?

Is it locked during the entire time records are being loaded in the
Select Into process?

Is the tempdb locked during transactions using it?

I appreciate advice from the experts. Thanks

Resolving temp table locking issues

Post by Val Mazu » Sun, 20 Jun 2004 11:06:14


I might be wrong, but I do not think temporary table locks any records on a
underlying table. When you open temporary table, after it is populated with
the data from the actual table(s), there is no more direct connection
between the actual table and temporary one. It means that if you make any
changes in a temporary table, then do not affect any actual data, which
means temporary table cannot hold any locks against underlying data. I would
expect that tempdb would be locked during creating of the temp table, but
not after this, because it would violate the purpose of the temporary
tables, when multiple users could create temporary tables at same time.
Temporary tables is just some sort of cache, which is released after
connection is dropped or temp table deleted explicitly. It is possible that
pages of the records could be locked when you populate temp table using
SELECT INTO statement, but it depends on engine. I do not see any point for
the locking.
Another story could during transaction. But even if you opened transaction,
that does not mean that records will be locked. Database engine locks some
records or pages of records when application tries to modify record(s). If
you just select, then engine should not hold any locks.

Val Mazur
Microsoft MVP