• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

DeadLock on 2 insert at the same time

I have 2 processes that caused a Deadlock and I have no idea how to change my code so it won't append again has the code is really simple.

The code is creating a temptable named [##FiltreTableCall_ID @@SPID]
So the 2 processed created their temptable :
[##FiltreTableCall_ID 925] at 2009-02-16 15:19:19.253
[##FiltreTableCall_ID 282] at 2009-02-16 15:19:19.270

Notice that the time of execution is really closed (23 ms), but they both worked fine.
Now I tried to insert the content of these table into my table FiltreTable
It's a simple insert from one table to the other with no join and where clause.
INSERTING into :
[##FiltreTableCall_ID 282] at 2009-02-16 15:19:19.270
[##FiltreTableCall_ID 925] at 2009-02-16 15:19:19.283 <-- this one caused a deadlock

I looked and the inserting into [##FiltreTableCall_ID 282] wasn't finished when the deadlock occured, but why inserting into[##FiltreTableCall_ID 925] just wasn't waited that 282 finished to start is inserting ? the inserting took about 50ms not very long to wait ?

Anyone know how to resolve this ?

Thanks

SELECT * INTO [##FiltreTableCall_ID 925] FROM (SELECT Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA FROM Call C (NOLOCK) INNER JOIN ivProdType S (NOLOCK) ON C.Call_ivProdType_ID = S.ivProdType_ID WHERE Call_ID NOT IN (SELECT Call_ID FROM FiltreTable (NOLOCK)) AND Call_ID = 378650) S
 
SELECT * INTO [##FiltreTableCall_ID 282] FROM (SELECT Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA FROM Call C (NOLOCK) INNER JOIN ivProdType S (NOLOCK) ON C.Call_ivProdType_ID = S.ivProdType_ID WHERE Call_ID NOT IN (SELECT Call_ID FROM FiltreTable (NOLOCK)) AND Call_ID = 373084) S
 
INSERT INTO FiltreTable (Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA) SELECT Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA FROM [##FiltreTableCall_ID 282] (NOLOCK)
 
INSERT INTO FiltreTable (Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA) SELECT Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA FROM [##FiltreTableCall_ID 925] (NOLOCK)

Open in new window

0
powlin
Asked:
powlin
1 Solution
 
BrandonGalderisiCommented:
Are there triggers on FiltreTable?
0
 
powlinAuthor Commented:
Yes there's, sorry for not having posted it.

here is the trigger for insert
ALTER TRIGGER [trgFiltreTableInsert] ON [FiltreTable]
FOR INSERT
AS
BEGIN
	SET NOCOUNT ON
	INSERT INTO ValeurTable (Call_ID,Call_HistoFlag) SELECT Call_ID, Call_HistoFlag FROM inserted (NOLOCK)
END

Open in new window

0
 
tigin44Commented:
use transactions.


SELECT * INTO [##FiltreTableCall_ID 925] FROM (SELECT Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA FROM Call C (NOLOCK) INNER JOIN ivProdType S (NOLOCK) ON C.Call_ivProdType_ID = S.ivProdType_ID WHERE Call_ID NOT IN (SELECT Call_ID FROM FiltreTable (NOLOCK)) AND Call_ID = 378650) S
 
SELECT * INTO [##FiltreTableCall_ID 282] FROM (SELECT Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA FROM Call C (NOLOCK) INNER JOIN ivProdType S (NOLOCK) ON C.Call_ivProdType_ID = S.ivProdType_ID WHERE Call_ID NOT IN (SELECT Call_ID FROM FiltreTable (NOLOCK)) AND Call_ID = 373084) S
 
BEGIN TRANSACTION 
INSERT INTO FiltreTable (Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA) SELECT Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA FROM [##FiltreTableCall_ID 282] (NOLOCK)
IF @@ERROR = 0 
    COMMIT TRANSACTION
ELSE
    ROLLBACK
 
BEGIN TRANSACTION     
INSERT INTO FiltreTable (Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA) SELECT Call_ID, Call_HistoFlag, Call_DateMod, ivProdType_ID, ivProdType_Hierarchy, Call_DateEntered, Call_LST_CALLCAT, Call_LST_CALLPRIO, Call_LST_CALLSTAT, Call_LST_CALLUDF2, Call_LST_CALLUDF3, Call_LST_CALLUDF4, Call_LST_CALLUDF5, Call_LST_CALLUDF6, Call_LST_QUEUE, CUD_LIGNESERVICE_ESCALADE, CUD_PALIER_INFRA FROM [##FiltreTableCall_ID 925] (NOLOCK)
IF @@ERROR = 0 
    COMMIT TRANSACTION
ELSE
    ROLLBACK 

Open in new window

0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now