Когда использовать транзакции в SQL Server

Есть много-много вопросов о том, КАК использовать транзакции. Я хочу знать КОГДА ? При каких обстоятельствах? Какие типы запросов? Может ли вместо этого хватить блоков Try-Catch? И т.д. ...

Я разработал базу данных с ~ 20 таблицами и ~ 20 хранимыми процедурами. В настоящее время ни один из моих SP не использует транзакцию, но повсюду присутствует множество блоков Try-Catch. Причина в том, что каждый раз, когда я пытался заключить их в транзакцию, SP переставал работать, и я получал недостающие данные, и мне было хуже, чем если бы я использовал Trans.

Итак, еще раз ...

1. Когда подходящее время для использования транзакции?
2. В качестве дополнительного вопроса, если я использую их, как я могу использовать их таким образом, чтобы ТОЛЬКО предотвратить другие SP могут получать доступ к одним и тем же данным в одно и то же время, чтобы предотвратить повреждение, вместо того, чтобы приводить к тому, что мои SP вообще не работают?

Вот небольшой образец SP, который я написал для переименования продукта:

CREATE PROCEDURE spRenameProduct
    @pKey int = NULL,
    @pName varchar(50)
AS
BEGIN
    BEGIN TRY
        IF LTRIM(RTRIM(@pName)) = '' SET @pName = NULL
        IF NOT @pKey IS NULL AND NOT @pName IS NULL BEGIN
            declare @pKeyExisting int = (select MIN(ID) from rProduct where Product like @pName and not ID = @pKey)
            IF @pKeyExisting is null BEGIN
                update rProduct set IsValid = 1, Product = @pName where ID = @pKey
            END ELSE BEGIN
                update Request set ProductID = @pKeyExisting where ProductID = @pKey
                update StatusReport set ProductID = @pKeyExisting where ProductID = @pKey
                delete from rProduct where ID = @pKey
            END
        END
    END TRY BEGIN CATCH END CATCH
END

А что, если бы два человека были используя это в одно и то же время? Я действительно не хочу и у меня нет времени (к сожалению) увлекаться фантазиями. ЦЕЛОВАТЬ. лучше всего в этом случае. :)