Transaction handling
Published 21 August 2018
By default, Pre & Post-Deployment scripts do not contain any transaction handling and are run outside of any transactions.
A simplified example of the generated deployment script looks like:
Simplified deployment script including Pre & Post-Deployment scripts
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
-- The Pre-Deployment script gets inserted here
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[myStoredProcedure]'
GO
CREATE PROC [dbo].[myStoredProcedure] AS SELECT 1
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
-- The Post-Deployment script gets inserted here
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
PRINT 'The database update failed'
END
GOAdding transaction handling to Pre & Post-Deployment scripts
You can write any SQL in Pre & Post-Deployment scripts, including your own transaction handling. For example:
BEGIN TRANSACTION GO IF @@ERROR <> 0 SET NOEXEC ON GO ALTER DATABASE CURRENT SET RECOVERY SIMPLE GO IF @@ERROR <> 0 SET NOEXEC ON GO COMMIT TRANSACTION GO IF @@ERROR <> 0 SET NOEXEC ON GO
If written this way, the deployment is not atomic - the transaction for the Pre-Deployment script may be committed whilst the main transaction is rolled back. If an atomic deployment is required, you can BEGIN a transaction in the Pre-Deployment script and COMMIT it in the Post-Deployment script. As a consequence, the main transaction will be nested within this outer transaction.