The following two procedures are quite likely to embrace in a deadlock: CREATE PROCEDURE dbo.UpdateCounter1 INT SELECT n AS ID, n AS Key1, n AS Key2, 0 AS Counter1, 0 AS Counter2ĪLTER TABLE dbo.DeadlockTest ADD CONSTRAINT PK_DeadlockTest PRIMARY KEY(ID) ĬREATE INDEX DeadlockTestKey1 ON dbo.DeadlockTest(Key1) ĬREATE INDEX DeadlockTestKey2 ON dbo.DeadlockTest(Key2) Let us set up a table with some test data: CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY) Your solution may be different, but you need to expose it to high concurrency, as I will demonstrate later. The most important point here is to stress test your solution. Second, we shall use sp_getapplock eliminate them. ![]() YMMV.įirst, let us start with a scenario where we always get a considerable amount of deadlocks. The following describes what works for me. I have no idea if this will work for you, because SQL Server is closed source, so I cannot see the source code, and as such I do not know if I have tested all possible cases. ![]() I will show how I would use sp_getapplock to eliminate deadlocks. There are several ways to accomplish that. It is possible to avoid deadlocks altogether. Also it might be better to split your table into multiple ones. Make only one stored procedure call, and increment the counter by 20. For example, if your application knows it needs 20 new IDs, do not make 20 round trips. Since our code never adds a record to this table with 0 in LastID we can make the assumption that if is 1 then the intention is append a new ID to the list, else we are updating an existing row in the list.įirst, I would avoid making a round trip to the database for every value. The code below replaces the code above from BEGIN TRANSACTION to END TRANSACTION: BEGIN TRANSACTION The general idea here is to refine the statement to eliminate unnecessary locking, and overall to make the SP more efficient. I've taken the advice that gave and modified it slightly. Anyway, this index IS used by the actual execution plan: CREATE NONCLUSTERED INDEX IX_tblIDs_IDName_LastID I've added a new index, since the existing index IX_tblIDs_Name is not being used by the SP I assume the query processor is using the clustered index since it needs the value stored in LastID. Sample executions of the stored proc: EXEC GetNextID 'SomeTestID' SET = -2 /* no need to retry since the operation completed */ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ![]() The stored procedure used to update the values stored in the table, and return the next ID: CREATE PROCEDURE nvarchar(255)ĭescription: Increments and returns the LastID value from tblIDs Some sample data: INSERT INTO tblIDs (IDName, LastID) (Īnd the nonclustered index on the IDName field: CREATE NONCLUSTERED INDEX The database itself is configured with READ_COMMITTED_SNAPSHOT = 1.įirst, here is the table: CREATE TABLE. I'm fairly certain there should be a way to access this table without any deadlocks at all. Occasionally the stored proc gets a deadlock - I believe I've built an appropriate error handler however I'm interested to see if this methodology works as I think it does, or if I'm barking up the wrong tree here. I have a table that is used by a legacy application as a substitute for IDENTITY fields in various other tables.Įach row in the table stores the last used ID LastID for the field named in IDName.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |