SQLBits 2024
ACID in SQL Server: what it is, how it works, and how to live more adventurously.
A primer on ACID compliance in SQL Server, though mostly on Isolation and Durability, since those are the ones you have some control over. Presented at Data Saturday Gothenburg and Oslo 2023.
You’re a developer, data engineer, or DBA working with SQL Server. You remember reading about ACID compliance in some computer science class a couple of decades ago, and maybe you just assumed that every transaction you run in SQL Server is fully ACID: Atomic, Consistent, Isolated and Durable.
That’s what the packaging says, and it’s mostly correct.
ACID has long been a core concept of transactional databases, but there are a number of ways we can manipulate or even disable some of these four properties in SQL Server. Sometimes intentionally, and sometimes perhaps unexpectedly.
In this session, we’ll look at the real-world effects of the settings and hints in SQL Server that affect ACID compliance: when to use them to improve your database workload, and when to avoid them. We’ll compare isolation levels, delayed durability, and distributed data patterns.
You’ll not only walk away with a better understanding of the underlying transaction logic, but you’ll know which settings can cause data loss, and which ones can actually improve application performance.
That’s what the packaging says, and it’s mostly correct.
ACID has long been a core concept of transactional databases, but there are a number of ways we can manipulate or even disable some of these four properties in SQL Server. Sometimes intentionally, and sometimes perhaps unexpectedly.
In this session, we’ll look at the real-world effects of the settings and hints in SQL Server that affect ACID compliance: when to use them to improve your database workload, and when to avoid them. We’ll compare isolation levels, delayed durability, and distributed data patterns.
You’ll not only walk away with a better understanding of the underlying transaction logic, but you’ll know which settings can cause data loss, and which ones can actually improve application performance.