que tal kaquijose, agrego la opinion que solicitaste.
Te comento que coincido con el compañero iislas.
El problema que te puede estar pasando es que al ejecutar las operaciones de insert, update y delete desde la aplicacion (aun cuando son sps) te esta generando bloqueos, que impiden que se terminen con exito los comandos, ya me ha tocado optimizar aplicaciones con los sintomas que describes.
Lo mejor seria manejar las transacciones desde SQL (con las sentencias que te comentaron), pero aun asi debes revizar tu proceso ya que tal vez no sea lo mejor el insertar en temporales, actualizar y luego de ahi pasarlos a las tablas operativas.
Adicionalmente debes checar la forma en que se realizan las consultas, aplicar "nolock" a los selects en los casos que sea posible (tambien es otro tema que debes investigar), verificar uso de indices, evitar (si es el caso) el uso de cursores, etc....
En fin, hay que tomarse un tiempo, porque creo que no será una tarea de solo arreglar una linea de codigo para que funcione correctamente siempre. Mas aun si tu sistema es accedido por multiples usuarios que actualizan dichas tablas al mismo tiempo.
Saludos!