Lock-free Backups

Lock-free backups do not block INSERT, UPDATE, and DELETE operations at any point during a backup. They do not need to lock and wait for write queries. Lock-free backups are enabled by default

However, ALTER DATABASE and SNAPSHOT commands will not run while there is a backup job active on the relevant database. There is no difference between lock-free backup and the original locking backup with respect to the cluster operations lock. The backup takes the cluster operations lock for the entirety of the backup.

Lock-free backups are enabled when the lockfree_backup engine variable is set to ON and the disable_update_delete_distributed_transactions engine variable is set to OFF. The default values of lockfree_backup and disable_update_delete_distributed_transactions are ON and OFF, respectively.

Note

Lock-free backups cannot be made using BACKUP DATABASE ... WITH SPLIT PARTITIONS ....

Locking that Occurs if Lock-free Backups are Disabled

If lock-free backups are disabled and BACKUP DATABASE is run, the following applies before the backup starts:

  • The aggregator briefly blocks new queries from running across the cluster.

  • If there is a long-running write query executing, BACKUP DATABASE waits for the query to complete.

Once BACKUP DATABASE is running, queries against the cluster can proceed normally.

Commands Blocked by the BACKUP Process

  • ALTER DATABASE

  • DROP DATABASE

  • SNAPSHOT DATABASE

Commands Not Blocked by the BACKUP Process

  • CREATE TABLE (columnstore and rowstore)

  • ALTER TABLE (columnstore and rowstore)

  • DROP TABLE (columnstore and rowstore)

  • DELETE TABLE

  • INSERT TABLE

  • TRUNCATE TABLE (columnstore and rowstore)

  • CREATE INDEX

  • DROP INDEX (columnstore and rowstore)

  • CREATE VIEW

  • DROP VIEW

  • ANALYZE TABLE (columnstore and rowstore)

  • OPTIMIZE COLUMNSTORE TABLE

  • SHOW TABLE STATUS

  • SHOW DATABASE STATUS

  • OPTIMIZE ROWSTORE TABLE

Last modified: February 29, 2024

Was this article helpful?