SQLServer 6.5 locking up when using temp tables

SQLServer 6.5 locking up when using temp tables

Post by Ulf Dittme » Fri, 14 May 1999 04:00:00



Hi everyone-

We have an intermittent problem with SQLServer 6.5 putting locks on tempdb
when using temp tables, and then failing to release them, causing the whole
DB to lock up pretty fast. Has anyone experienced something like this or
knows of circumstances that might mitigate it?
We're using Weblogic's JDBC driver to connect to SQLServer.
Many thanks in advance for any clues you can provide.

Ulf

 
 
 

SQLServer 6.5 locking up when using temp tables

Post by Alexander Tarasu » Fri, 14 May 1999 04:00:00


If it's not the bug of JDBC driver (completely possible) then check if you
don't use

select * into #tmp from yourtable.

This select place lock on tempdb.

-------------------------------------------
Alexander Tarasul
Microsoft Certified Solution Developer(SQL Server,VB)
Certified Borland Delphi Client Server Developer

http://pages.ripco.com/~shleym
SQL Answers located at
http://pages.ripco.com/~shleym/sql_answers.htm

---------------


>Hi everyone-

>We have an intermittent problem with SQLServer 6.5 putting locks on tempdb
>when using temp tables, and then failing to release them, causing the whole
>DB to lock up pretty fast. Has anyone experienced something like this or
>knows of circumstances that might mitigate it?
>We're using Weblogic's JDBC driver to connect to SQLServer.
>Many thanks in advance for any clues you can provide.

>Ulf


 
 
 

SQLServer 6.5 locking up when using temp tables

Post by Tony Rogerso » Sat, 15 May 1999 04:00:00


Ulf,

A change of behaviour was done between previous version of SQL Server and
6.5, in 6.5 the complete SELECT .. INTO table is done inside a transaction,
what this means is that the create table part locks sysobjects, syscolumns
and sysindexes, then for the duration of the insert part the locks are held.
This has the effect of blocking other users from creating tables.

In service pack 1 a trace flag was introduced (5302) that changes this
behaviour back to how it was before, the create table and insert are
seperate and the locking problem goes away. I use this trace flag countless
numbers of times, there is only one reported bug to do with union and I
think views.

--
Tony Rogerson MCP,SQL Server MVP
Torver Computer Consultants Ltd.


Quote:> Hi everyone-

> We have an intermittent problem with SQLServer 6.5 putting locks on tempdb
> when using temp tables, and then failing to release them, causing the
whole
> DB to lock up pretty fast. Has anyone experienced something like this or
> knows of circumstances that might mitigate it?
> We're using Weblogic's JDBC driver to connect to SQLServer.
> Many thanks in advance for any clues you can provide.

> Ulf

 
 
 

SQLServer 6.5 locking up when using temp tables

Post by Dave Ballantyn » Tue, 18 May 1999 04:00:00


Creating a temp table will put locks on sysobject, syscolumns etc in tempdb.
Therefore any other transaction will be blocked on the tempdb.  Create the
tables in a small transaction, an continue processing in another

Dave


>Ulf,

>A change of behaviour was done between previous version of SQL Server and
>6.5, in 6.5 the complete SELECT .. INTO table is done inside a transaction,
>what this means is that the create table part locks sysobjects, syscolumns
>and sysindexes, then for the duration of the insert part the locks are
held.
>This has the effect of blocking other users from creating tables.

>In service pack 1 a trace flag was introduced (5302) that changes this
>behaviour back to how it was before, the create table and insert are
>seperate and the locking problem goes away. I use this trace flag countless
>numbers of times, there is only one reported bug to do with union and I
>think views.

>--
>Tony Rogerson MCP,SQL Server MVP
>Torver Computer Consultants Ltd.



>> Hi everyone-

>> We have an intermittent problem with SQLServer 6.5 putting locks on
tempdb
>> when using temp tables, and then failing to release them, causing the
>whole
>> DB to lock up pretty fast. Has anyone experienced something like this or
>> knows of circumstances that might mitigate it?
>> We're using Weblogic's JDBC driver to connect to SQLServer.
>> Many thanks in advance for any clues you can provide.

>> Ulf

 
 
 

SQLServer 6.5 locking up when using temp tables

Post by Tony Rogerso » Wed, 19 May 1999 04:00:00


Dave,

My recommendation is not to do any create tables or select into (DDL) inside
a transaction when doing normal processing, these features where put in to
aid safe modification of database schema.

--
Tony Rogerson MCP,SQL Server MVP
Torver Computer Consultants Ltd.


> Creating a temp table will put locks on sysobject, syscolumns etc in
tempdb.
> Therefore any other transaction will be blocked on the tempdb.  Create the
> tables in a small transaction, an continue processing in another

> Dave


> >Ulf,

> >A change of behaviour was done between previous version of SQL Server and
> >6.5, in 6.5 the complete SELECT .. INTO table is done inside a
transaction,
> >what this means is that the create table part locks sysobjects,
syscolumns
> >and sysindexes, then for the duration of the insert part the locks are
> held.
> >This has the effect of blocking other users from creating tables.

> >In service pack 1 a trace flag was introduced (5302) that changes this
> >behaviour back to how it was before, the create table and insert are
> >seperate and the locking problem goes away. I use this trace flag
countless
> >numbers of times, there is only one reported bug to do with union and I
> >think views.

> >--
> >Tony Rogerson MCP,SQL Server MVP
> >Torver Computer Consultants Ltd.



> >> Hi everyone-

> >> We have an intermittent problem with SQLServer 6.5 putting locks on
> tempdb
> >> when using temp tables, and then failing to release them, causing the
> >whole
> >> DB to lock up pretty fast. Has anyone experienced something like this
or
> >> knows of circumstances that might mitigate it?
> >> We're using Weblogic's JDBC driver to connect to SQLServer.
> >> Many thanks in advance for any clues you can provide.

> >> Ulf