存储过程和transaction

 

https://stackoverflow.com/questions/11531352/how-to-rollback-a-transaction-in-a-stored-procedure

BEGIN TRANSACTION;

BEGIN TRY
    -- Some code
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH

    ROLLBACK TRANSACTION;
END CATCH;

 

 

执行的存储过程需要transaction的话，在调用的时候传入

BEGIN TRANSACTION
DECLARE @usedrecords XML;
SET @usedrecords = N'<Record ID="388" />';
EXEC dbo.pd_trn_Transaction @UsedRecords

 

如果有事务锁定了数据库，那么可以直接rollback

ROLLBACK TRAN

 

 

在执行存储过程的的时候，错误提示

You attempted to acquire a transactional application lock without an active transaction.

 DECLARE @res INT;
        EXEC @res = sp_getapplock @Resource = 'TransactionCheckLock' ,
                                  @LockMode = 'Exclusive' ,
                                  @LockOwner = 'Transaction' ,
                                  @LockTimeout = 10000 ,
                                  @DbPrincipal = 'public';
        IF @res NOT IN ( 0, 1 )
            BEGIN
                RETURN -2;
            END;

存储过程内部，要求TransactionCheckLock，所以在外部调用的时候，必须加上begin transaction

 

查询没有处理的Transaction

https://stackoverflow.com/questions/3978227/how-to-kill-or-rollback-active-transaction

SELECT * FROM sys.dm_tran_session_transactions 

You can't kill/rollback a transaction from another session without killing the owner session.

I think, allowing to kill/rollback a transaction from another user's session means many design and security rule violations because it requires entering another user session (in the context of the current sql server engine design). That's probably why it is not implemented.

关于如何kill session

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2017

 

转载于:https://www.cnblogs.com/chucklu/p/7347524.html