SQL Change Automation by default uses READ COMMITTED transactions for all migrations, which may affect server performance by taking strong locks.
In a zero downtime deployment we would likely rely on making schema and data changes in a way that requires minimal locking.

Using app locks in SQL Change Automation

Rather than using transactions, you can use SQL Server app locks to prevent change clashes. Here is an example of creating a project in this way: 

1. Create a new SQL Change Automation project 
   

2. Edit the .sqlproj file and add the line:
   

   <DefaultTransactionHandlingMode>Custom</DefaultTransactionHandlingMode> 

   • This means no transactions will be used for the migration scripts unless specified
     

3. Add a pre-deployment script adding procedures to do locking. For example:

   CREATE OR ALTER PROCEDURE dbo.GetLock @ComponentName nvarchar(255), @LockMode nvarchar(255) AS
   BEGIN 
   	DECLARE @attempts int = 1;
   	DECLARE @lockResult int = -1;
   
   	WHILE @lockResult < 0
   	BEGIN 
   		EXEC @lockResult = sp_getapplock @Resource = @ComponentName, @LockMode = @LockMode, @LockOwner = 'Session', @LockTimeout = 100
   		IF @lockResult < 0
   		BEGIN
   			DECLARE @waitCycles bigint = ROUND(RAND() * @attempts, 0)
   			DECLARE @delay char(8) = convert(char(8), DATEADD(second, @waitCycles, '00:00:00'),108)
   
   			WAITFOR DELAY @delay
   			SET @attempts = @attempts * 2
   		END 
   	END 
   END
   GO
   CREATE OR ALTER PROCEDURE dbo.ReleaseLock @ComponentName nvarchar(255) AS
   EXEC sp_releaseapplock @Resource = @ComponentName, @LockOwner = 'Session' 
   

   
   

4. Add a new migration script and use these functions only when necessary
   

   CREATE TABLE accounts(...)
   GO
   CREATE TABLE customers(...)
   GO
   EXEC dbo.GetAppLock @ComponentName = 'customers', @LockMode = 'UPDATE'
   EXEC dbo.GetAppLock @ComponentName = 'customers', @LockMode = 'EXCLUSIVE'
   -- Do something that needs no one else accessing the data
   EXEC dbo.ReleaseLock @ComponentName = 'customers'
   EXEC dbo.ReleaseLock @ComponentName = 'customers' 

   
   

5. Build the project 
   
   

When you inspect the package deployment script in the build output you will notice there is no transaction around your migration. 

Potential limitations 

• All locks must be acquired and released within the same script
  

• We run scripts incrementally so there is no guarantee that two scripts will always run together

• We still wrap the creation of SQL Change Automation’s internal tables and views in a transaction when generating deployment scripts

• When using the programmable objects feature you cannot apply custom locks

• The programmable object scripts only contain the creation scripts