What’s ACID in databases? I was asked this in an interview a long time ago and I couldn’t remember the exact meaning. For a (supposedly) senior DBA, that’s bad. I could, however, tell what it means and what it enforces in the database, I couldn’t remember the actual words.

In building a presentation recently, I needed this, so I looked it up on Wikipedia. Isn’t everything on Wikipedia? And isn’t it true?

In this case, they do a good job of explaining the various terms. ACID stands for

• Atomicity
• Consistency
• Isolation
• Durable

I won’t repeat their explanations, but try to give my own take on this.

Atomicity - If you have a transaction that makes some change, it has to all succeed or all fail. No partial transactions. That doesn’t mean that each statement succeeds, but everything wrapped in a transaction, whether 1 or 100 statements, all get completed or all rolled back.

You can appear to muck with this by nesting transactions, but not really. SQL Server follows this rule of enforcing transactions as a complete unit or work. Either all committed, or all rolled back.
 
Consistency - This means that the database essentially enforces consistent change to the database from transactions, and that the database is logically consistent at all times. So references are enforced, cascades take place as part of transactions, etc. It’s a strange concept, but it really means that the database enforces all rules defined.
Note that one funny thing here is the internal sysreferences aren’t always consistent. That one bugs me, though my employer, Red Gate Software, has a tool that finds these: SQL Dependency Tracker

Isolation - I always get this one wrong, thinking it’s integrity, but really that’s the consistency piece. Isolation means  you cannot access data that is changed but still in an uncommitted transaction state. This essentially ensures that you get a consistent, accurate view of data. SQL Server allows you to bypass this with dirty reads, and lots of people do this to improve performance, but I think it’s a bad idea in general.

Durability - If you have any type of failure, usually hardware, this ensures that all committed transactions can be restored. Or that uncommitted transactions are rolled back.

SQL Server enforces this with its roll back/roll forward process when a database is started. This uses the write-ahead log to ensure that the database is in a durable state when users access the data.