TheBugSlayer
Programmer
Hello all,
We inherited an application that was rewritten from SmallTalk with a SQL Server in the backend that causes a lot deadlocks and runs slows at times for that reason.
The database has many tables, all of which have an ObjectID INT key value. However, the value is not auto-generated. Instead, it is tracked by wheel table, along with other categories. So, if say, to create a patient, the latest ObjectID is select from the table, then it is incremented by X=1 or X=50 depending on the process, and the resulting value is saved. The process that ObjectID + 50 is trying to minimize hits to the table and chances of locking it I can only guess.
Many processes run similar code with the same intention, some from a stored procedure and some inline.
An example usage would be:
Here is an example of the deadlocks. In it Process 138 the deadlock victim on the left and Process 73 on the right both call GetNextOID to use it in a different table each:
We're looking to improve this code seen as switching to an auto-increment ID field is not possible. We're considering:
1- Standardizing the code. Put it all in one SP and have all apps call the SP.
2- The Type = 'ObjectID' being the most deadlock-prone, moving it to a table of its own
In your opinion,
1- what is the proper way of approaching this table that needs to be locked, updated and released quickly and frequently?
2- what is the best transaction isolation level if necessary for a case like this?
3- does creating a table with one row for the ObjectID reduce contention and locking on the table?
Thank you kindly for your advice.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
We inherited an application that was rewritten from SmallTalk with a SQL Server in the backend that causes a lot deadlocks and runs slows at times for that reason.
The database has many tables, all of which have an ObjectID INT key value. However, the value is not auto-generated. Instead, it is tracked by wheel table, along with other categories. So, if say, to create a patient, the latest ObjectID is select from the table, then it is incremented by X=1 or X=50 depending on the process, and the resulting value is saved. The process that ObjectID + 50 is trying to minimize hits to the table and chances of locking it I can only guess.
Many processes run similar code with the same intention, some from a stored procedure and some inline.
Code:
--1
BEGIN TRANSACTION
SELECT IDENTIFIER FROM IDENTIFIERTABLE WITH(XLOCK) WHERE TYPE = 'ObjectID'
UPDATE IDENTIFIERTABLE WITH(XLOCK) SET IDENTIFIER = IDENTIFIER + 50 WHERE TYPE = 'ObjectID'
COMMIT TRANSACTION
--2
BEGIN TRANSACTION
SELECT IDENTIFIER FROM IDENTIFIERTABLE WITH(XLOCK) WHERE TYPE = 'XYZ'
UPDATE IDENTIFIERTABLE WITH(XLOCK) SET IDENTIFIER = IDENTIFIER + 1 WHERE TYPE = 'ObjectID'
COMMIT TRANSACTION
--3
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Begin TRAN T1
SELECT @OID = identifier from identifiertable /* WITH (UPDLOCK) */ where type = 'ObjectID'
update identifiertable set updatetime = Current_TimeStamp, identifier = identifier + 1 where type = 'ObjectID'
If @@ERROR <> 0 goto OnError
Commit Tran T1
--4
CREATE Procedure [dbo].[GetNextOID]
@OID int Output as
-- Declare variables
-- DECLARE @returnCode Int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Begin TRAN T1
SELECT @OID = identifier from identifiertable /* WITH (UPDLOCK) */ where type = 'ObjectID'
update identifiertable set updatetime = Current_TimeStamp, identifier = identifier + 1 where type = 'ObjectID'
If @@ERROR <> 0 goto OnError
Commit Tran T1
IF @@RowCount > 0
--BEGIN
-- SELECT @returnCode = 0
RETURN 0
--END
ELSE
--BEGIN
--SELECT @returnCode = -1
RETURN -1
--END
/* Return -1 on Error */
OnError:
Rollback Tran T1
Return -1An example usage would be:
Code:
DECLARE @OID int
--Get the next ObjectID using code in section (4) of above code segment
EXECUTE GetNextOID @OID Output
INSERT INTO SomeTable (ObjectID,...) VALUES(@OID, ...)Here is an example of the deadlocks. In it Process 138 the deadlock victim on the left and Process 73 on the right both call GetNextOID to use it in a different table each:
We're looking to improve this code seen as switching to an auto-increment ID field is not possible. We're considering:
1- Standardizing the code. Put it all in one SP and have all apps call the SP.
2- The Type = 'ObjectID' being the most deadlock-prone, moving it to a table of its own
In your opinion,
1- what is the proper way of approaching this table that needs to be locked, updated and released quickly and frequently?
2- what is the best transaction isolation level if necessary for a case like this?
3- does creating a table with one row for the ObjectID reduce contention and locking on the table?
Thank you kindly for your advice.
MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).
![[surprise]](/data/assets/smilies/surprise.gif "[surprise] [surprise]")
. There is no index on the Type column as you can see in the image. This is a low-hanging fruit, so I will make that change right away and check the frequency of xml_deadlock_report extended event. 
![[bigsmile]](/data/assets/smilies/bigsmile.gif "[bigsmile] [bigsmile]")
