DP-900: how to master ACID transactions in Azure SQL Database

In this guide I will teach the skill of transactions and ACID properties in relational databases on Azure — a topic frequently covered in DP-900 and essential in practice to ensure data integrity. You will see what each ACID property is, how to execute transactions in Azure SQL Database and a practical example in T-SQL.

What you need to know

ACID is a set of properties that describe the behavior of transactions in relational systems:

• Atomicity: a transaction is "all or nothing" — either all changes are applied, or none are.
• Consistency: the transaction brings the database from one consistent state to another consistent state (integrity rules are maintained).
• Isolation: concurrent transactions do not interfere uncontrollably with each other; the result is as if they were executed serially, depending on the isolation level.
• Durability: once committed (COMMIT), the change remains even if there is a system failure.

On Azure, services like Azure SQL Database (PaaS) and SQL Server on VMs support ACID transactions. The way you manage transactions in T-SQL is the same; the Azure service handles availability, durability and recovery.

How it works

In practice, you control transactions with T-SQL commands: BEGIN TRAN, COMMIT and ROLLBACK. Use TRY...CATCH to handle errors and ensure ROLLBACK in case of failure. Typical example: transferring balance between two accounts — an operation that must be atomic.

-- Exemplo simplificado: transferir 100 da conta A para a conta B
BEGIN TRANSACTION;

BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 'A';
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 'B';

    -- Verifica integridade simples
    IF (SELECT Balance FROM Accounts WHERE AccountId = 'A') < 0
    BEGIN
        THROW 50000, 'Saldo insuficiente', 1;
    END

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    -- Regista o erro (ex.: RAISERROR/PRINT/INSERT numa tabela de logs)
    DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
    PRINT @ErrMsg;
END CATCH;

This pattern ensures atomicity (either the full transfer or nothing), consistency (balance check), isolation (other transactions will not see intermediate states, depending on the isolation level) and durability (COMMIT makes the operation persistent).

In practice — isolation levels

Isolation controls phenomena like dirty reads, non-repeatable reads and phantom reads. The common levels in SQL Server / Azure SQL Database are:

• READ UNCOMMITTED — allows dirty reads (faster, less safe).
• READ COMMITTED — default: prevents dirty reads.
• REPEATABLE READ — prevents non-repeatable reads.
• SERIALIZABLE — highest isolation; prevents phantom reads; can reduce concurrency.
• SNAPSHOT — version-based isolation; avoids many locks and prevents dirty/non-repeatable/phantom reads, but consumes tempdb/resources.

Choosing the correct level is a trade-off between integrity and performance. In Azure SQL Database, SNAPSHOT can be useful for read-heavy scenarios without blocking.

Common mistakes

• Not using ROLLBACK in TRY...CATCH blocks — can leave the transaction open and cause locks and deadlocks.
• Choosing an isolation level that is too permissive (READ UNCOMMITTED) for financial operations — risks inconsistent reads.
• Keeping transactions long — the longer a transaction remains open, the higher the probability of locks and conflicts with other transactions. Keep transactions short and atomic.

How to practice

To practice this skill, use the free Azure environment (or a local instance of SQL Server) and perform exercises creating tables, simulating transfers and testing with different isolation levels. Microsoft provides an OFFICIAL free Practice Assessment for DP-900 and a study guide on Microsoft Learn — both free and recommended to guide your review. Use them to check your knowledge and identify areas where you need more practice.

In summary

• ACID defines Atomicity, Consistency, Isolation and Durability — they are essential for transaction reliability.
• Control transactions with BEGIN TRAN / COMMIT / ROLLBACK and use TRY...CATCH to handle errors.
• Choose the isolation level appropriate to the balance between integrity and performance (READ COMMITTED, SNAPSHOT, SERIALIZABLE, ...).
• Practice on Azure SQL Database and use the official Microsoft Practice Assessment and study guide to evaluate progress.