SQL Change Automation 3

Custom locking in SQL Change Automation projects

SQL Change Automation by default uses READ COMMITTED transactions for all migrations, which may impact server performance by taking strong locks.
In a zero down-time 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 


Didn't find what you were looking for?