Dead lock issue with Update and delete statement on the same table

[kali siddhu]

I am getting dead lock issue due to below transaction. Please could you help on this.

------------------------------------------------------Transaction 1------------------------------------------------------
Transactionname="DELETE"
waitresource="KEY: 6:72057594046054400 (6a6c767d8e74)"
lasttranstarted="2020-07-11T05:48:38.007"
lockMode="U"
isolationlevel="read committed (2)"
status="suspended"

DELETE FROM [Channel] WHERE FileInventoryKey IN (SELECT FileInventoryKey FROM #FileInventoryKeys)

--------------------------------------------------------Transaction 2------------------------------------------------------
Transactionname="UPDATE"
waitresource="KEY: 6:72057594046054400 (93201dea6b78)"
lasttranstarted="2020-07-11T05:48:38.007"
lockMode="U"
isolationlevel="read committed (2)"
status="suspended"

update ch set ChannelKey = c.ChannelKey
from Channel ch
join ChannelVersion c on c.ChannelID = ch.ChannelID
join Channel cc on cc.ChannelKey = c.ChannelKey and (cc.HasIntervalData = 0 and ch.IsRegister = 'true') --register channels where c.ChannelVersionKey in (select ChannelVersionKey from [#UpdatedChannels]

I have two options 1). UPD lock on the update statement 2). SET TRANSACTION ISOLATION LEVEL Snapshot

[David Mujica]

If you are using SQL server and the DELETE operation can be delayed or postponed try this approach … Use an application lock to synchronize two different processes that data is being manipulated and try again later ... Microsoft has sp_getapplock Maybe it would work for you.

[kali siddhu]

These are three solutions which one will be the best one and let me know if i need to change anything in the solution. First solution : EXEC @returnCode = Sp_getapplock @Resource = 'Resource_stg_SqlQueueManager', @LockMode = 'Exclusive', @LockOwner = 'Transaction ', @LockTimeout = 500000 Second solution SET TRANSACTION ISOLATION LEVEL SNAPSHOT Third solution Update with nolock Example : update ch set ChannelKey = c.ChannelKey from CRF_Channel ch with (nolock) join ChannelVersion c on c.ChannelID = ch.ChannelID join Channel cc on cc.ChannelKey = c.ChannelKey and (cc.HasIntervalData = 0 and ch.IsRegister = 'true')