Custom locking

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:

    • 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
    	DECLARE @attempts int = 1;
    	DECLARE @lockResult int = -1;
    	WHILE @lockResult < 0
    		EXEC @lockResult = sp_getapplock @Resource = @ComponentName, @LockMode = @LockMode, @LockOwner = 'Session', @LockTimeout = 100
    		IF @lockResult < 0
    			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
    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(...)
    CREATE TABLE customers(...)
    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

Didn't find what you were looking for?