×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Transaction with SELECT and UPDATE with XLOCK causing deadlocks
2

Transaction with SELECT and UPDATE with XLOCK causing deadlocks

Transaction with SELECT and UPDATE with XLOCK causing deadlocks

(OP)
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.

CODE --> T-SQL

--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 -1 

An example usage would be:

CODE --> T-SQL

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).

RE: Transaction with SELECT and UPDATE with XLOCK causing deadlocks

I would replace the table with a sequence.

See CREATE SEQUENCE https://learn.microsoft.com/en-us/sql/t-sql/statem...
and usage of the sequence by
NEXT VALUE FOR https://learn.microsoft.com/en-us/sql/t-sql/functi...

This removes the need to manage this within transaction and some isolation level, or in short eliminates most of your questions.

If you agree I'm willing to work out the details together with you here.

Chriss

RE: Transaction with SELECT and UPDATE with XLOCK causing deadlocks

(OP)
Hi Chris.

Thank you for taking the time to answer my question. Yes, I am familiar with the SEQUENCE feature in SQL Server. The problem with the vendor is that they have not been adopting features released over time.

I am eager to see what you propose. At least the other satellite applications could test the solution version. If there is a reduction in contention we might start a massive push for its implementation in the big system.

Looking forward to your suggestion...Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

RE: Transaction with SELECT and UPDATE with XLOCK causing deadlocks

Quote (TheBugSlayer)

Looking forward to your suggestion

Not sure if you look forward to finding out how much better a sequence works than the currently used mechanism or if you look forward to what I offered to do:

Quote (myself)

I'm willing to work out the details together with you here.

I still am, but as you say

Quote (TheBugSlayer)

I am familiar with the SEQUENCE feature in SQL Server.

Is there any detail that you need to know?

From the top of my hat the increment is one feature of sequences, but using the NEXT VALUE FOR feature then binds its usage to a field and it will get numbering with gaps, you can get around this with a bit trickery by using the sequence value with a table that's only used to extract it and then can implement what I think you saw with the IDENTIFIER + 50 mechanism, which "reserves" 50 seqnece numbers for own usage while the central counter immediately skips 50 ahead so other clients are not using numbers in the range of the current maximum and +50. In short client side caching of IDs can also be something you do with sequences, but then it's also much simpler to use a sequence as the major mechanism that you can use for each single ID value, So I'd not keep that client side ID caching anymore.

Chriss

Edit: Let me take back the trickery from the statement "you can get around this with a bit trickery". Still, I remember and checked the documentation of NEXT VALUE for mention very many limitations and restrictions. Also, see the remark:

Quote (Docs ONline)

When the NEXT VALUE FOR function is used in a query or default constraint, if the same sequence object is used more than once, or if the same sequence object is used both in the statement supplying the values, and in a default constraint being executed, the same value will be returned for all columns referencing the same sequence within a row in the result set.

It's still simple to SELECT NEXT VALUE FOR sequecename into a variable to be used anywhere you want to, i.e. taking the responsibility to generate IDs on the client side writing the value from the sequence into the a primary key field which does neither use an IDENTITY nor a NEXT VALUE FOR sequence as a default value. What you still use centralized is a counter, well, the sequence object, thus don't generate numbers twice unless you would reserve 50 ids and use 51 or have some other error leading to not using the number you got from the sequence. All I know in general that while it seems to be a burden to take that responsibility client side, it can also be a relief, as you know IDs in advance and can already use them not only as the future primary keys you can be sure no one else will be using, but also as foreign keys. That allows usage of complex client side buffering and caching or objects like a C# DataSet containing multiple DtaTables in which you accumulate a change you want to commit in a transaction as one database change.

RE: Transaction with SELECT and UPDATE with XLOCK causing deadlocks

In my experience, the first step to reduce deadlocks is to make sure the queries run as quickly as possible. There are many ways to do this, but I always start by looking at the execution plan for the troublesome queries. If you see any table scans or index scans, you may improve you situation by creating indexes to assist the query.

What indexes do you have on the identifier table? Examine the execution plan, are the indexes being used?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Transaction with SELECT and UPDATE with XLOCK causing deadlocks

(OP)
@Chris, thanks once more for the help.
We are exploring the SEQUENCE avenue and will soon decide if we should develop a POC in the testing environment. I'll let you know the final decision.

@gmmastros
I can't believe myself! We went deep into the woods without doing the most basic check in this instance 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.


Thank you both. BBIAB.


PS: @ is not working for tagging and I can't find how to in the posting guidelines. Please advise.
This forum has a sentimental value for me. I had not been on it for a long time because, well, thanks in part to the knowledge I have acquired here I have moved up the ranks to a managerial position where I don't get my hands dirty as much. I must have been a member for I would venture say about 10 years...but I can't confirm this. Anyway, I am glad to see that the same great people are still here sharing their knowledge with a professional community. Not sure if you offer DBA services to companies but I would have happy to recommend your services in a bit.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

RE: Transaction with SELECT and UPDATE with XLOCK causing deadlocks

Quote:


I must have been a member for I would venture say about 10 years...but I can't confirm this.

Actually...

Quote:


TheBugSlayer's (Programmer) Member Profile

After joining Tek-Tips Forums on September 22, 2002, I've logged in 1,343 times. My last login was on Wednesday, October 26, 2022. I've started 191 threads, posted 695 replies, and written 0 FAQs. Click here to see a breakdown of my posting by month and year. You can click on the table below to see my posts and/or FAQs written in any particular forum. I have saved 0 threads to My Thread Archive and 0 FAQs to My FAQ Archive (FL, US)

20 years, 1 month and 2 days. I think all this means is that you are older than you think. bigsmile

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Transaction with SELECT and UPDATE with XLOCK causing deadlocks

For your index, I would suggest:

CODE

Create Index idx_IdentifierTable_Type_Identifier On IDENTIFIERTABLE(Type, Identifier); 

You could create an index only on Type, but then SQL Server would check the index and then go back to the table for a row look up. If you put both columns in the index, SQL Server will be able to get all the information it needs from the index and won't even need to look at the table. It's important that the first column in the index is Type, otherwise the index won't really do you any good.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Transaction with SELECT and UPDATE with XLOCK causing deadlocks

Quote (gammastros)

I think all this means is that you are older than you think.

Or you skipped a few years time traveling.

Chriss

RE: Transaction with SELECT and UPDATE with XLOCK causing deadlocks

(OP)
@gmmastros,

The table already has a primary key on ObjectID. It only has 212 rows.

The execution plan has not changed after the original modification.

It remains the same when I add ObjectID to the non-clustered index (Type, ObjectID).

When I change the primary key to (Type, ObjectID) I finally get an Index Seek. Not sure what impact that will have yet as I am test and it's never had the issue we are experiencing in live. Should I keep this index configuration?

Thanks again.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

RE: Transaction with SELECT and UPDATE with XLOCK causing deadlocks

Hmmm....

Indexes on tiny tables have a lot less impact than larger tables. On the other hand, having another index on a tiny table won't have any detrimental impact either. Personally, I would probably leave it there.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close