· 7 years ago · Jan 02, 2019, 04:02 PM
1CREATE TABLE dbo.ID_Stub (
2ID int IDENTITY(1,1) NOT NULL,
3IDReference nchar(25) NULL,
4AdditionalID int NULL,
5CreatedBy int NOT NULL,
6CreatedOn datetime NOT NULL,
7CONSTRAINT PK_ID_Stub PRIMARY KEY CLUSTERED (ID) WITH
8(
9 PAD_INDEX = OFF,
10 STATISTICS_NORECOMPUTE = OFF,
11 SORT_IN_TEMPDB = OFF,
12 IGNORE_DUP_KEY = OFF,
13 ONLINE = OFF,
14 ALLOW_ROW_LOCKS = ON,
15 ALLOW_PAGE_LOCKS = ON,
16 FILLFACTOR = 90
17)
18);
19GO
20
21CREATE NONCLUSTERED INDEX idx_IDReference ON dbo.ID_Stub (IDReference) WITH
22(
23 PAD_INDEX = OFF,
24 STATISTICS_NORECOMPUTE = OFF,
25 SORT_IN_TEMPDB = OFF,
26 DROP_EXISTING = OFF,
27 ONLINE = OFF,
28 ALLOW_ROW_LOCKS = ON,
29 ALLOW_PAGE_LOCKS = ON,
30 FILLFACTOR = 70
31);
32GO
33
34SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
35BEGIN TRANSACTION
36
37-- If a reference has been provided...
38IF ISNULL(@IDReference, '') > ''
39BEGIN
40 IF @ID IS NULL
41 BEGIN
42 -- Attempt to locate record based on provided reference.
43 SELECT @ID = MAX(ID)
44 FROM dbo.IDs I
45 WHERE I.IDReference = LTRIM(RTRIM(@IDReference))
46 AND I.CreatedBy = @CreatedBy
47 AND I.AdditionalID = @AdditionalID
48
49 IF @ID IS NULL
50 BEGIN
51 -- If there is no corresponding record, the subsequent ID creation after the stub failed.
52 SET @OriginallyCreated =
53 (
54 SELECT MAX(CreatedOn)
55 FROM dbo.ID_Stub IDS
56 WHERE IDS.IDReference = LTRIM(RTRIM(@IDReference))
57 AND IDS.CreatedBy = @CreatedBy
58 AND IDS.AdditionalID = @AdditionalID
59 );
60
61 -- Delete the stub record if created more than 90 seconds ago.
62 IF @OriginallyCreated IS NOT NULL
63 BEGIN
64 IF DATEDIFF(s, @OriginallyCreated, GETDATE()) < 90
65 SELECT @FailureMessage = 'The ID for reference ' + RTRIM(@IDReference) + ' is still being processed. Please try later.';
66 ELSE
67 DELETE dbo.ID_Stub
68 WHERE IDReference = LTRIM(RTRIM(@IDReference))
69 AND CreatedBy = @CreatedBy
70 AND AdditionalID = @AdditionalID;
71 END
72 END
73 END
74 ELSE
75 BEGIN
76 IF NOT EXISTS
77 (
78 SELECT ID
79 FROM dbo.IDs I
80 WHERE I.ID = @ID
81 AND I.CreatedBy = @CreatedBy
82 )
83 SELECT @FailureMessage = 'You have no record stored against the supplied ID (' + RTRIM(CONVERT(Char, @ID)) + ')';
84 ELSE
85 IF NOT EXISTS
86 (
87 SELECT ID
88 FROM dbo.IDs I
89 WHERE I.ID = @ID
90 AND I.IDReference = @IDReference
91 AND I.CreatedBy = @CreatedBy
92 )
93 SELECT @FailureMessage = 'The ID does not match the reference you supplied.';
94 END
95END
96ELSE -- No ID Reference provided.
97BEGIN
98 IF @ID IS NOT NULL
99 BEGIN
100 IF NOT EXISTS
101 (
102 SELECT ID
103 FROM dbo.IDs I
104 WHERE I.ID = @ID
105 AND I.CreatedBy = @CreatedBy
106 )
107 SELECT @FailureMessage = 'You have no record stored against the supplied ID (' + RTRIM(CONVERT(Char, @ID)) + ').';
108 END
109END
110
111IF @FailureMessage <> ''
112BEGIN
113 ROLLBACK;
114 RETURN 1;
115END
116
117-- If it's a new submission, create a stub for it.
118IF @ID IS NULL
119BEGIN
120 INSERT dbo.ID_Stub (IDReference, AdditionalID, CreatedBy, CreatedOn)
121 VALUES (@IDReference, @AdditionalID, @CreatedBy, GETDATE());
122
123 SELECT @ID = SCOPE_IDENTITY();
124END
125
126COMMIT
127SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
128
129<deadlock>
130 <victim-list>
131 <victimProcess id="process1e7fd5d8108"/>
132 </victim-list>
133 <process-list>
134 <process id="process1e7fd5d8108" taskpriority="0" logused="336" waitresource="KEY: 30:72057596372844544 (4f8ff66d381b)" waittime="4265" ownerId="1879373839" transactionname="user_transaction" lasttranstarted="2018-12-29T22:19:51.357" XDES="0x1dfe81c9538" lockMode="RangeI-N" schedulerid="6" kpid="2084" status="suspended" spid="182" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-29T22:19:51.307" lastbatchcompleted="2018-12-29T22:19:51.293" lastattention="1900-01-01T00:00:00.293" clientapp=".Net SqlClient Data Provider" hostname="<obfuscated>" hostpid="3648" loginname="<obfuscated>" isolationlevel="serializable (4)" xactid="1879373839" currentdb="30" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
135 <executionStack>
136 <frame procname="<obfuscated>" line="640" stmtstart="68006" stmtend="68296" sqlhandle="0x03001e002acd7f07eca57a012ea9000001000000000000000000000000000000000000000000000000000000"> INSERT dbo.ID_Stub (IDReference, AdditionalID, CreatedBy, CreatedOn) VALUES (@IDReference, @AdditionalID, @CreatedBy, GETDATE(); </frame>
137 </executionStack>
138 <inputbuf> Proc [Database Id = 30 Object Id = 125816106] </inputbuf>
139 </process>
140 <process id="process1fd327c7c28" taskpriority="0" logused="336" waitresource="KEY: 30:72057596372844544 (4f8ff66d381b)" waittime="4265" ownerId="1879373837" transactionname="user_transaction" lasttranstarted="2018-12-29T22:19:51.357" XDES="0x1e76d97ebd8" lockMode="RangeI-N" schedulerid="3" kpid="9084" status="suspended" spid="208" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-29T22:19:51.300" lastbatchcompleted="2018-12-29T22:19:51.293" lastattention="1900-01-01T00:00:00.293" clientapp=".Net SqlClient Data Provider" hostname="<obfuscated>" hostpid="3648" loginname="<obfuscated>" isolationlevel="serializable (4)" xactid="1879373837" currentdb="30" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
141 <executionStack>
142 <frame procname="<obfuscated>" line="640" stmtstart="68006" stmtend="68296" sqlhandle="0x03001e002acd7f07eca57a012ea9000001000000000000000000000000000000000000000000000000000000"> INSERT dbo.ID_Stub (IDReference, AdditionalID, CreatedBy, CreatedOn) VALUES (@IDReference, @AdditionalID, @CreatedBy, GETDATE(); </frame>
143 </executionStack>
144 <inputbuf> Proc [Database Id = 30 Object Id = 125816106] </inputbuf>
145 </process>
146 </process-list>
147 <resource-list>
148 <keylock hobtid="72057596372844544" dbid="30" objectname="<obfuscated>.<obfuscated>.ID_Stub" indexname="idx_IDReference" id="lock1e566d4a380" mode="RangeS-S" associatedObjectId="72057596372844544">
149 <owner-list>
150 <owner id="process1fd327c7c28" mode="RangeS-S"/>
151 <owner id="process1fd327c7c28" mode="RangeI-N" requestType="convert"/>
152 </owner-list>
153 <waiter-list>
154 <waiter id="process1e7fd5d8108" mode="RangeI-N" requestType="convert"/>
155 </waiter-list>
156 </keylock>
157 <keylock hobtid="72057596372844544" dbid="30" objectname="<obfuscated>.<obfuscated>.ID_Stub" indexname="idx_IDReference" id="lock1e566d4a380" mode="RangeS-S" associatedObjectId="72057596372844544">
158 <owner-list>
159 <owner id="process1e7fd5d8108" mode="RangeS-S"/>
160 <owner id="process1e7fd5d8108" mode="RangeI-N" requestType="convert"/>
161 </owner-list>
162 <waiter-list>
163 <waiter id="process1fd327c7c28" mode="RangeI-N" requestType="convert"/>
164 </waiter-list>
165 </keylock>
166 </resource-list>
167</deadlock>