April 16, 2010 at 3:00 pm
I have one that's driving me crazy here... We have a primary key distribution process that is controlled by one server in a multi-server ETL for data warehousing environment. Up to four SQL Server Enterprise 2000 servers running ETL (and multiple processes from each of those) can hit the stored procedure that hits the table responsible for guaranteeing uniqueness in PK values. It does this by handing out PK ranges to the requesting ETL processes and keeping track of the changes in the master table.
When this process was created back in 2002, we realized we needed to use a table lock to prevent deadlock scenarios. With the table lock at the beginning of the stored procedure, we eliminated the deadlocks and haven't seen one since... until now. Recently, a customer running our stuff upgraded to a new, faster SQL Server. Suddenly, deadlocks are being sprinkled through the nightly load cycles like hand-grenades cast out of a moving car. Where and when they'll hit is relatively random, but it seems they hit one or two servers once a load cycle.
Is it possible that the new installation of SQL Server on the new server is ignoring the table lock hint? Or is it possible that the speed of the new server is somehow working to create an environment right for the deadlocks to appear?
Below is the code. I sure could use some advice. Thanks in advance!!!
CREATE PROCEDURE sg_UpdateTableMax
@tabname CHAR(30),
@server INT,
@minval INT output,
@maxval INT output
AS
BEGIN
DECLARE @ret INT, @interval INT, @ins_err INT, @updt_err INT, @rowcnt_var INT
-- set range interval here
set @interval = 50000
DECLARE tabmax CURSOR FOR
SELECT MAX_VALUE FROM TABLE_MAX_MASTER WITH (XLOCK,HOLDLOCK)
WHERE TABLE_NAME = @tabname
FOR UPDATE OF MAX_VALUE
BEGIN TRANSACTION
OPEN tabmax
FETCH NEXT FROM tabmax INTO @ret
IF ( @@FETCH_STATUS <> 0 ) -- no match
BEGIN
INSERT INTO TABLE_MAX_MASTER VALUES ( @tabname, @interval + 1 )
select @ins_err = @@ERROR
if ( @ins_err <> 0 )
BEGIN
RAISERROR ( '%d - Error inserting record into TABLE_MAX_MASTER for %s',
18, 1, @ins_err, @tabname ) WITH LOG
ROLLBACK TRANSACTION
CLOSE tabmax
DEALLOCATE tabmax
RETURN(1)
END
ELSE BEGIN
SET @maxval = @interval
SET @minval = 1
END
END
ELSE BEGIN
SET @minval = @ret
SET @maxval = @minval + @interval - 1
UPDATE TABLE_MAX_MASTER SET MAX_VALUE = @maxval + 1 WHERE CURRENT OF tabmax
select @updt_err = @@ERROR, @rowcnt_var = @@ROWCOUNT
if ( @updt_err <> 0 )
BEGIN
RAISERROR ( '%d - Error updating record into TABLE_MAX_MASTER for %s',
18, 1, @updt_err, @tabname ) WITH LOG
ROLLBACK TRANSACTION
CLOSE tabmax
DEALLOCATE tabmax
RETURN(2)
END
END
INSERT INTO TABLE_MAX_HISTORY VALUES ( @tabname, @minval, @maxval, current_timestamp, @server )
select @ins_err = @@ERROR
if ( @ins_err <> 0 )
BEGIN
RAISERROR ( '%d - Error inserting record into TABLE_MAX_HISTORY for %s',
18, 1, @ins_err, @tabname ) WITH LOG
ROLLBACK TRANSACTION
CLOSE tabmax
DEALLOCATE tabmax
RETURN(1)
END
COMMIT TRANSACTION
CLOSE tabmax
DEALLOCATE tabmax
RETURN(0)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
April 19, 2010 at 2:03 am
Have you tried to put the 'begin transaction' just after the set @interval = 50000 line ?
April 20, 2010 at 3:03 am
Does the table TABLE_MAX_MASTER have any indexes?
If not, based on the manner of access, I would suggest that clustered index on the TABLE_NAME column would seem appropriate.
An index is important as what may be happening is that the select is xlock'ing records it reads over checking for the TABLE_NAME matching the @tabname passed, i.e. a Table Scan, which may mean it's locking more records and thus there is more locks from which deadlocks can occur.
April 20, 2010 at 5:12 am
enable the deadlock traces and send the information in logs. Then somebody can tell whats happening.
April 20, 2010 at 11:00 am
You say you want a table lock. If so, you need to use TABLOCKX, not XLOCK.
April 21, 2010 at 10:12 am
Thanks for the replies!
The problem is, indeed, missing indexes on the table sitting on the server that is misbehaving. I will be switching the pk distribution process back to that server this weekend after adding the missing indexes.
Thanks, everyone 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply