August 2, 2012 at 10:57 am
Hi,
I've got a system that is deadlocking when there is a sudden increase in activity. Not horrific amounts of transactions, 100s a second.
The table that is deadlocking is 3 columns wide and only had 3 rows in it. It is designed to allocate a unique number to each transaction being processed (it's Point of Sale system).
The table schema is:
Create Table NextNumber(GUID uniqueidentifier not null primary key, TerminalType varchar(50),NextNumber bigint)
Other than the PK there are no other indexes.
I know uniqueidentifier is a horrible PK, but we have that impossed on us by the application we developing for (MS CRM)
The query is:
SELECT @nextNumber = NextNumber FROM NextNumber with (tablock,xlock)
WHERE TerminalType = @nextNumberType
We need the locks to prevent 2 transactions selecting the same number.
Is it as simple as the locks aren't being released in time?
Would Pining the table in memory help (PINTABLE ?) or is that irrelevant?
I've no evidence of another process using the table although there must be something that increments the value, I'm waiting to hear on that front.
Until then any help is appreciated.
Thanks
August 2, 2012 at 12:32 pm
You know one piece of the deadlock - how did you discover that piece?
Did you derive it from the deadlock graph? If not, can you capture the deadlock graph and then we will have both pieces of the puzzle in the deadlock.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply