August 12, 2008 at 10:49 am
Hi All,
SQL Server is : MS SQL Server 2000
In complex stored procedures we are using one or two temp tables. It was working fine initially. But DBA suggested us to add the following lines in the SP where we create and drop temp tables.
BEGIN TRANSACTION
SELECT @nCount = COUNT(id)
FROM tempdb.dbo.syscolumns (UPDLOCK)
SELECT @nCount = COUNT(id)
FROM tempdb.dbo.sysindexes (UPDLOCK)
Create table #Test_Table
(
field1 CHAR(2),
field2 DATETIME,
field3 DATETIME
)
COMMIT TRANSACTION
.
.
.
.
.
BEGIN TRANSACTION
SELECT @nCount = COUNT(id)
FROM tempdb.dbo.syscolumns (UPDLOCK)
SELECT @nCount = COUNT(id)
FROM tempdb.dbo.sysindexes (UPDLOCK)
drop table #Test_Table
COMMIT TRANSACTION
Right now , when large no of users are accessing the SPs , We are getting frequent deadlocks.
I just want to know you insights on this practice.
August 12, 2008 at 11:07 am
Locking the system tables appears to be an in-appropriate solution but there are some additional activities.
For SQL Server 2000, please see "Concurrency enhancements for the tempdb database" at http://support.microsoft.com/kb/328551. These techniques do not apply to 2005/2008.
There was a similar problem titled "Query hangs when inside a transaction" at "http://www.sqlservercentral.com/Forums/Topic542212-65-1.aspx". The last post indicates that MS was being contacted but there was no follow-up post, so a private message was sent to the Marios Philippopoulos, the original poster, requesting the findings and any solution.
SQL = Scarcely Qualifies as a Language
August 12, 2008 at 11:14 am
Marios Philippopoulos responded to me and wrote:
"Not yet unfortunately. Still working with MS on the issue. "
SQL = Scarcely Qualifies as a Language
August 12, 2008 at 11:16 am
Jayakrishnan (8/12/2008)
In complex stored procedures we are using one or two temp tables. It was working fine initially. But DBA suggested us to add the following lines in the SP where we create and drop temp tables.
Please ask him what the rational behind that suggestion is. I can't see a good reason to do it offhand.
I'm guessing he's trying to prevent contention, but if so, there are better ways
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply