Posted by Erland Sommarskog on 06/20/05 18:32

(arijitchatterjee123@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, esquel@sommarskog.se

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

[Back to original message]