Apology for being late in getting back. You were quite correct. Getting rid of the HOLDLOCK solve the issue, however, teh DEADLOCK took other SPs as preferred partner. 🙂

I did suggest to try with READ_COMMITTED_SNAPSHOT isolation level as the default isolation behavior, but the technical architects are a bit jittery as it will make the tempdb to take the toll. We have implemented the TRY..CATCH method for troubleshooting. Please advise if this is a correct approach.

Regards.