Paste Expression / Saved Dead Lock Proc

Question

I have an insert statement that has blocked the use of linq. So I put it in a saved proc so that the surrounding statements affect it.

Now Stored Proc is locked. Something in the insert statement is locking according to the server profile. He claims that two of these insert statements were awaiting the release of the PK index:

When I put the code in a stored procedure, it now states that this stored proc has come to a standstill with another instance of this stored proc.

Here is the code. The select statement is similar to the one used by linq when it executed its own query. I just want to see if an element exists, and if not, insert it. I can find a system with either PK or some search values.

SET NOCOUNT ON; BEGIN TRY SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SPFindContractMachine DECLARE @id int; set @id = (select [m].pkID from Machines as [m] WHERE ([m].[fkContract] = @fkContract) AND (( (CASE WHEN @bByID = 1 THEN (CASE WHEN [m].[pkID] = @nMachineID THEN 1 WHEN NOT ([m].[pkID] = @nMachineID) THEN 0 ELSE NULL END) ELSE (CASE WHEN ([m].[iA_Metric] = @lA) AND ([m].[iB_Metric] = @lB) AND ([m].[iC_Metric] = @lC) THEN 1 WHEN NOT (([m].[iA_Metric] = @lA) AND ([m].[iB_Metric] = @lB) AND ([m].[iC_Metric] = @lC)) THEN 0 ELSE NULL END) END)) = 1)); if (@id IS NULL) begin Insert into Machines(fkContract, iA_Metric, iB_Metric, iC_Metric, dteFirstAdded) values (@fkContract, @lA, @lB, @lC, GETDATE()); set @id = SCOPE_IDENTITY(); end COMMIT TRANSACTION SPFindContractMachine return @id; END TRY BEGIN CATCH if @@TRANCOUNT > 0 ROLLBACK TRANSACTION SPFindContractMachine END CATCH 

Answer 1

Any procedure that follows a pattern:

 BEGIN TRAN check if row exists with SELECT if row doesn't exist INSERT COMMIT 

going to face difficulties in production, because there is nothing to prevent the simultaneous execution of two protectors, and both reach the conclusion that they should be inserted. In particular, at the isolation level of serialization (as in your case) this template is guaranteed to be a dead end.

A much better template is to use unique database restrictions and always INSERT, to fix errors duplicating key errors. It is also significantly more productive.

Another alternative is to use the MERGE statement :

 create procedure usp_getOrCreateByMachineID @nMachineId int output, @fkContract int, @lA int, @lB int, @lC int, @id int output as begin declare @idTable table (id int not null); merge Machines as target using (values (@nMachineID, @fkContract, @lA, @lB, @lC, GETDATE())) as source (MachineID, ContractID, lA, lB, lC, dteFirstAdded) on (source.MachineID = target.MachineID) when matched then update set @id = target.MachineID when not matched then insert (ContractID, iA_Metric, iB_Metric, iC_Metric, dteFirstAdded) values (source.contractID, source.lA, source.lB, source.lC, source.dteFirstAdded) output inserted.MachineID into @idTable; select @id = id from @idTable; end go create procedure usp_getOrCreateByMetrics @nMachineId int output, @fkContract int, @lA int, @lB int, @lC int, @id int output as begin declare @idTable table (id int not null); merge Machines as target using (values (@nMachineID, @fkContract, @lA, @lB, @lC, GETDATE())) as source (MachineID, ContractID, lA, lB, lC, dteFirstAdded) on (target.iA_Metric = source.lA and target.iB_Metric = source.lB and target.iC_Metric = source.lC) when matched then update set @id = target.MachineID when not matched then insert (ContractID, iA_Metric, iB_Metric, iC_Metric, dteFirstAdded) values (source.contractID, source.lA, source.lB, source.lC, source.dteFirstAdded) output inserted.MachineID into @idTable; select @id = id from @idTable; end go 

This example separates the two cases, since T-SQL queries should never try to resolve two different solutions in the same query (the result is never optimized). Since the two tasks at hand (get by mahcine id and get by metrics) are completely separate, there must be separate procedures, and the caller must call apropiate, and not pass the flag. This example assumes how to achieve the (possibly) desired result using MERGE, but, of course, the correct and optimal solution depends on the actual scheme (determining the table, indices and coordinates in place) and on the real requirements (it is not clear that the procedure should be performed if the criteria have already been agreed upon, and @id is not displayed?).

By eliminating SERIALIZABLE isolation, this is no longer guaranteed by a deadlock, but can still slow down. Of course, the solution to the deadlock completely depends on the scheme that was not indicated, therefore, in this context it is impossible to provide a solution to the deadlock. There is a sledgehammer of blocking all possible strings (the power of UPDLOCK or even TABLOCX), but such a solution will kill throughput with heavy use, so I can not recommend it without knowing the use case.

Answer 2

Get rid of the transaction. It does not help you, but it hurts you. This should fix your problem.

Answer 3

How about this SQL? It moves validation of existing data and insertion into a single statement. Thus, when two threads are running, they are not stuck waiting for each other. In the best case, thread two is at an impasse, waiting for thread one, but as soon as thread one ends, thread two can work.

 BEGIN TRY BEGIN TRAN SPFindContractMachine INSERT INTO Machines (fkContract, iA_Metric, iB_Metric, iC_Metric, dteFirstAdded) SELECT @fkContract, @lA, @lB, @lC, GETDATE() WHERE NOT EXISTS ( SELECT * FROM Machines WHERE fkContract = @fkContract AND ((@bByID = 1 AND pkID = @nMachineID) OR (@bByID <> 1 AND iA_Metric = @lA AND iB_Metric = @lB AND iC_Metric = @lC)) DECLARE @id INT SET @id = ( SELECT pkID FROM Machines WHERE fkContract = @fkContract AND ((@bByID = 1 AND pkID = @nMachineID) OR (@bByID <> 1 AND iA_Metric = @lA AND iB_Metric = @lB AND iC_Metric = @lC))) COMMIT TRAN SPFindContractMachine RETURN @id END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN SPFindContractMachine END CATCH 

I also changed these CASE statements to ORed clauses just because they were easier to read. If I recall my SQL theory, ORing can make this query a little slower.

Answer 4

I wonder if UPDLOCK will add a hint to previous SELECT (s), fix it; he should avoid deadlock scenarios in order to prevent another recession that is blocking the reading of the data you are about to change.