Skip to main content

Lock-free Backups

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.

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.

However ALTER 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 workspace operations lock. The backup takes the workspace operations lock for the entirety of the backup.

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:

Commands Blocked by the BACKUP Process

  • ALTER TABLE (rowstore and columnstore)

  • ALTER DATABASE

  • CREATE INDEX

  • OPTIMIZE ROWSTORE TABLE

  • TRUNCATE TABLE (rowstore and columnstore)

  • DROP INDEX (rowstore and columnstore)

  • DROP DATABASE

  • SNAPSHOT DATABASE

Commands Not Blocked by the BACKUP Process

  • CREATE TABLE (rowstore and columnstore)

  • DROP TABLE (rowstore and columnstore)

  • DELETE TABLE

  • INSERT TABLE

  • CREATE VIEW

  • DROP VIEW

  • ANALYZE TABLE (rowstore and columnstore)

  • OPTIMIZE COLUMNSTORE TABLE

  • SHOW TABLE STATUS

  • SHOW DATABASE STATUS