Numerous deadlocks occurred involving Stored procedures using Temp tables

Numerous deadlocks occurred involving Stored procedures using Temp tables

Post by Domini Josep » Fri, 20 Nov 1998 04:00:00

Hi  Everybody

I am working on an Internet Database application

I have some questions related to the temporary table (#tables) usage in
some of the Stored procedures

1) Numerous deadlocks occurred involving the above Stored procedures. Is
it due to the Temp table Usage.
2) As a result of a large number of* connections - attempts to
open more connections resulted
    in the max (1000) connection being exceeding.

Hope to get some answers

Thanx  in advance

Domini Joseph


Numerous deadlocks occurred involving Stored procedures using Temp tables

Post by Neil Pik » Sat, 21 Nov 1998 04:00:00

Domini - the likely cause is :-

Quote:> Subject: Numerous deadlocks occurred involving Stored procedures using Temp tables

Q.      Why is my application locking up in SQL Server?  If I check the locks I am
seeing syscolumns being blocked?
(v1.0   9.11.1998)

A.      This is due to a documented change in SQL 6.5 because tables created by
using SELECT INTO hold to the ACID (atomicity, consistency, isolation, durability)
transaction properties. This also means that system resources, such as pages,
extents, and locks, are held for the duration of the SELECT INTO statement. With
larger system objects, this leads to the condition where many internal tasks can be
blocked by other users performing SELECT INTO statements. For example, on
high-activity servers, many users running the SQL Enterprise Manager tool to monitor
system processes can block on each other, which leads to a condition where the SEM
application appears to stop responding.  (This happens on tempdb which is the
biggest problem with this new feature for most users)

You can revert to the old 6.0 and below behaviour where these locks are not held by
applying at least ServicePack 1 and then setting traceflag 5302 on startup.

It is recommended that you amend your application to not use select into, or if you
do, create the table using "SELECT ... INTO .... WHERE 1=0" to create the table and
then use standard inserts to populate the table.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see


1. Problem using shape recordsets with stored procedure that uses a temp table


i use a stored procedure to get data. This sp uses a temporary table to get
the correct results. There is no problem with this sp retrieving the data
directly into an ado recordset, but if i want to use this sp in a shape
command the ODBC driver raises an error that he could not find the temporary
In normal the temporary table is created in the sp and will be deleted after
executing them.

Is there anywhere a documentation on using temporary tables with a
hierarchical recordset, or better knows anybody the solution for this

The used shape command:
SHAPE {{ CALL sp_GetProductsByCategory (10,3,12) }} AS Products
APPEND ({{ CALL sp_GetMediaFiles() }} AS Mediafiles RELATE 'ProductID' TO
'ProductID') AS Mediafiles

The raised error:
Microsoft OLE DB Provider for ODBC Drivers: -2147467259
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name

Thanks a lot for reply

2. Rep Server question (limiting access to rep defs)

3. SQL Server 2000 and Windows 2000 Server SP3

4. insert into a global temp table using stored procedure

5. Oracle

6. Creating a Temp table using results from a store procedure

7. Troubleshooting ResultSet in jdbc

8. Some question about using temp table in stored procedure

9. Problem: Recordset is closed after calling a stored procedure that uses temp tables

10. ? about using temp tables in a stored procedure

11. using RDO 2.0 and VB5: Getting Data from temp tables created in stored procedure