Locking on Tempdb...

[arijitchatterjee123]

Hi Group,
I am facing a problem regarding locking. I have created a Stored
Procedure in my Database. In this Stored Procedure Temprary Tables get
created and after that values are inserted in these tables.But this
Stored Procedure is called from Java Portal. When then Stored Procedure
execute from Java end new transaction begins. So all these temporary
tables are created in tempDB and locking the entire Database.

So at the same time if another user fires the same Stored Procedure
from portal end its give the error "Lock time out". But if the prev
Stored Procedure execution gets competed before this request then no
error comes.

So is there any way from database to restrict to lock the tempDB
database????
Can you provide some valuable advice?

Thanks in advance.
Regards
Arijit Chatterjee

[Erland Sommarskog]

(ar*****************@yahoo.co.in) writes:

I am facing a problem regarding locking. I have created a Stored
Procedure in my Database. In this Stored Procedure Temprary Tables get
created and after that values are inserted in these tables.But this
Stored Procedure is called from Java Portal. When then Stored Procedure
execute from Java end new transaction begins. So all these temporary
tables are created in tempDB and locking the entire Database.

So at the same time if another user fires the same Stored Procedure
from portal end its give the error "Lock time out". But if the prev
Stored Procedure execution gets competed before this request then no
error comes.

So is there any way from database to restrict to lock the tempDB
database????
Can you provide some valuable advice?

This sounds strange. Unless you are running SQL 6.5 that is. In 6.5
creating temp tables in transactions would block any other process
that tried to create a temp table.

But in SQL 2000, this should not happen, unless there is something
more to it. You should examine exactly what the blocked processes are
waiting for. I have a tool on my web site, that can help you do this,
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[arijitchatterjee123]

Hi Erland,
Thanks for your reply. Is there any way to prevent the tempdb for
making any locks on any objects.Thanks in advance. I am trying to do
R&D with the support of your product.
Regards
Arijit Chatterjee

[arijitchatterjee123]

Hi Group,
Continuation of the Problem...But I am trying to highlit is different
manner..Using Sqlserver 2000 with Service pack 3
After login When I am going to Current Activities under Management and
doing refresh it is fine.
But from my Database end under Query Analyzer I am typing these lines

begin tran t1
create table #t1 (a int)
insert into #t1 values(1)
---------But not commiting still now..
And again I am going to Current Activities under Management and doing
refresh but now it is telling
Error 1222: Lock request time out period exceeded.

Now plese guide me how to solve this..without commiting the
transaction..I can say this transaction process can take 1 hour also..
That is why it has not commited still now.. That means on that time no
one can interact with the tempDB?
Any advice is most welcome..Thanks in advance.
Regards
Arijit Chatterjee

[Erland Sommarskog]

(ar*****************@yahoo.co.in) writes:

Continuation of the Problem...But I am trying to highlit is different
manner..Using Sqlserver 2000 with Service pack 3
After login When I am going to Current Activities under Management and
doing refresh it is fine.
But from my Database end under Query Analyzer I am typing these lines

begin tran t1
create table #t1 (a int)
insert into #t1 values(1)
---------But not commiting still now..
And again I am going to Current Activities under Management and doing
refresh but now it is telling
Error 1222: Lock request time out period exceeded.
Don't use Current Activity. It tries to look up the name of the temp
table, but uses READ COMMITTED isolation level to do this, so it gets
blocked. If you use my aba_lockinfo you will not get blocked, because
I read the system tables with READ UNCOMMITTED. (Dirty reads, which in
this special case is the right level.)
Now plese guide me how to solve this..without commiting the
transaction..I can say this transaction process can take 1 hour also..
That is why it has not commited still now.. That means on that time no
one can interact with the tempDB?

That's a foregone conclusion. Special operations like Current Activity
will block. Normal usage will not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[arijitchatterjee123]

Hi Erland ,
Thanks for your reply.I run your aba_lockinfo and getting the response
for there three columns as below.
Lock_Type,Owner_Type,Object

aba_lockinfo 0,0,0--Running this

S Sess NV_DAILY
Sch-M Xact tempdb.dbo.#4AC307E8
Sch-M Xact tempdb.dbo.#inputbuffer____________00010000003D
Sch-M Xact tempdb.dbo.#objects________________00010000003D
Sch-M Xact tempdb.dbo.#result_____________00010000003D
Sch-M Xact tempdb.dbo.#t1_____________________00000000003D
Sch-M Xact tempdb.dbo.DF__#result__last__4F87BD05
Sch-M Xact tempdb.dbo.PK__#result__4E9398CC
IX Xact tempdb.dbo.syscolumns
X Xact tempdb.dbo.syscolumns.ncsyscolumns
X Xact tempdb.dbo.syscolumns.syscolumns
IX Xact tempdb.dbo.syscomments
X Xact tempdb.dbo.syscomments.syscomments
IX Xact tempdb.dbo.sysdepends
IX Xact tempdb.dbo.sysindexes
X Xact tempdb.dbo.sysindexes.sysindexes
IX Xact tempdb.dbo.sysobjects
X Xact tempdb.dbo.sysobjects.ncsysobjects
X Xact tempdb.dbo.sysobjects.ncsysobjects2
X Xact tempdb.dbo.sysobjects.sysobjects
IX Xact tempdb.dbo.syspermissions
IX Xact tempdb.dbo.sysproperties
Sch-M Xact tempdb.dbo.UQ__#objects__526429B0
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
IX Xact tempdb.MISSING?
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?

NV_DAILY is my Database.And after that the tempdb can't create any
#table on the same name until that procedure commits the
transaction.Any advice is acceptable.Thanks in advance.
Regards
Arijit Chatterjee

[arijitchatterjee123]

Hi Erland ,
Thanks for your reply.I run your aba_lockinfo and getting the response
for there three columns as below.
Lock_Type,Owner_Type,Object

aba_lockinfo 0,0,0--Running this

S Sess NV_DAILY
Sch-M Xact tempdb.dbo.#4AC307E8
Sch-M Xact tempdb.dbo.#inputbuffer____________00010000003D
Sch-M Xact tempdb.dbo.#objects________________00010000003D
Sch-M Xact tempdb.dbo.#result_____________00010000003D
Sch-M Xact tempdb.dbo.#t1_____________________00000000003D
Sch-M Xact tempdb.dbo.DF__#result__last__4F87BD05
Sch-M Xact tempdb.dbo.PK__#result__4E9398CC
IX Xact tempdb.dbo.syscolumns
X Xact tempdb.dbo.syscolumns.ncsyscolumns
X Xact tempdb.dbo.syscolumns.syscolumns
IX Xact tempdb.dbo.syscomments
X Xact tempdb.dbo.syscomments.syscomments
IX Xact tempdb.dbo.sysdepends
IX Xact tempdb.dbo.sysindexes
X Xact tempdb.dbo.sysindexes.sysindexes
IX Xact tempdb.dbo.sysobjects
X Xact tempdb.dbo.sysobjects.ncsysobjects
X Xact tempdb.dbo.sysobjects.ncsysobjects2
X Xact tempdb.dbo.sysobjects.sysobjects
IX Xact tempdb.dbo.syspermissions
IX Xact tempdb.dbo.sysproperties
Sch-M Xact tempdb.dbo.UQ__#objects__526429B0
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
Sch-M Xact tempdb.MISSING?
IX Xact tempdb.MISSING?
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?
X Xact tempdb.MISSING?

NV_DAILY is my Database.And after that the tempdb can't create any
#table on the same name until that procedure commits the
transaction.Any advice is acceptable.Thanks in advance.
Regards
Arijit Chatterjee

[Erland Sommarskog]

(ar*****************@yahoo.co.in) writes:

NV_DAILY is my Database.And after that the tempdb can't create any
#table on the same name until that procedure commits the
transaction.Any advice is acceptable.Thanks in advance.

Try this:

BEGIN TRANSACTION
CREATE TABLE #temp(a int NOT NULL PRIMARY KEY)

Run from two windows. This does not block on the machine I tested.

I'm sorry, but you will have to examine more in detail, why you get
blocking. Maybe there is something special in your code that gives you
blocking. But I can't sit and guess from a distance what may be.

You could post the entire output from aba_lockinfo. Please put in an
attachment, so that the lines don't get wrapped.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[arijitchatterjee123]

Thanks Erland ,
Thanks for your response.
Reg
Arijit Chatterjee