Optimized locking

In the Ultra Transcenders books

DP-800

Each book explains Optimized locking in context, with comparison tables and the common traps.

Terms in this definition

• TID locking

  One element of optimized locking: each changed row is tagged with its transaction ID and its row and page locks are let go straight after the change. The transaction then keeps just a single lock, on that TID, until it ends.

• Lock after qualification

  Part of the optimized locking feature. Rows are first tested against a DML statement's filter using their newest committed version, without locking, and only rows that pass are locked. Read committed snapshot isolation must be on.

• ADR

  A recovery design in the Database Engine. Changes are versioned inside the user database, in what's called a persistent version store, which makes rolling back almost immediate and recovery time constant. Azure SQL Database, SQL database in Fabric and Managed Instance have it permanently enabled.

• FIRST

  A DAX function available only inside visual calculations. It fetches the value at the start of one axis of the visual's matrix, which makes it handy for comparing each point with the first; its opposite is LAST.

• Azure SQL Database

  Platform-as-a-service database offered as a single database or in an elastic pool, sized up to 4 TB or 128 TB on Hyperscale. SQL Agent, CLR and queries across databases aren't available.

• Serverless

  Compute tier for single Azure SQL databases that scales automatically, pauses when idle and charges by the second. It is offered in General Purpose and Hyperscale, not Business Critical, and reserved capacity does not apply.

• Schema

  The middle part of a Unity Catalog name (catalog.schema.table), grouping tables, views, volumes, functions and models inside a catalog. A grant on it covers everything in it now and later, and nothing inside can be reached without USE SCHEMA.