Transaction handling
Published 31 July 2019
When deploying your database with SQL Change Automation, you may wonder:
- How are transactions handled?
- If the deployment fails, will all the previous migrations be rolled-back?
- Can I implement my own transaction handling?
The answer to these questions depends on the type of change you are performing. Here is a break-down of transaction handling behavior in SQL Change Automation:
| Sequence | Script Directory | Transaction Used |
|---|---|---|
| 1 | Pre-deployment | No |
| 2 | Migration scripts | Yes |
| 3 | Programmable objects & additional scripts | Yes |
| 4 | Post-deployment | No |
Where Transaction Used = Yes, SQL Change Automation will automatically wrap your migrations in a single BEGIN TRAN / COMMIT TRAN block. If at any point one of these migration fails to deploy, the entire transaction will be rolled-back.
If you wish to use a transaction within your pre/post-deployment scripts, you can perform a BEGIN TRAN yourself, however please ensure that you perform a COMMIT or ROLLBACK at the end of your script to avoid having overlapping transactions. In the case of migration scripts, programmable objects and additional scripts, SQL Change Automation will confirm that TRANCOUNT() = 0 after each script execution.
Disabling automatic transaction handling
Automatic transaction handling can be disabled on an individual basis for a migration script, programmable object, or an additional script.
You might want to disable automatic transaction handling if:
- You have a long-running operation like a BULK INSERT and you want to commit batches of rows at a time
- You want to automate the deployment of a server-level object such as a linked server
- You need to perform an ALTER DATABASE operation on the current database
- You need to add full-text indexes to your database, or perform some other operation which cannot be done within a user transaction
- You want to use an alternative approach to locking
Prior to executing your custom-flagged migration, SQL Change Automation will COMMIT any open transactions. After executing that migration, a new transaction will be opened for any remaining migrations that are pending deployment.
Alternatively the default behavior of automatic transaction handling by default can be inverted by setting the DefaultTransactionHandlingMode property to Custom in the project settings file.
