ACID compliance
Contents
Overview
ACID compliance is a principle in the SQL world. It describes how a transaction---a single operation---should behave.
- Atomic. The transaction should happen all at once or not at all.
- Consistent. The transaction should never violate any of the consistency rules of the database.
- Isolated. Other concurrent transactions shouldn't affect this transaction at all.
- Durable. The transaction, once committed, should never, ever, ever be rolled back, not even in the worst imaginable crisis.
Implementation
There are really two ways of getting to ACID compliance.
The Oracle Way (WRONG!)
The Oracle Way, in short, is terribly wrong.
What you do is keep a rollback segment of your database.
That segment will hold all of the data that the ongoing transactions have changed.
It also holds data that has been committed but should not be put into the database because ongoing transactions have seen the old version.
Two problems with this system are that long-running transactions will cause the rollback segment to fill up. Either the offending transaction is making a lot of changes, or there are a lot of other transactions that have been committed that made a lot of changes to data that the long-running transaction has seen.
This is a clunky solution, one that bites Oracle users even today. It is the wrong way to go. Don't do it.
The PostgreSQL Way (Better)
PostgreSQL keeps all the data, even data from past or future transactions, in the tables themselves. It marks each row as to which transactions it is valid for. That way, two different transactions can see two different views of the same table.
The beauty of this solution is that a commit merely has to update the transaction window of the rows, rather than actually move data from one disk to the other.
The drawback is that you need a cleanup process to actually free up the space of old rows, or mark them as free. It used to be, a very long time ago, that this was an expensive process requiring the database to all but shutdown. However, for a long time, PostgreSQL's vacuum process can be run at the same time the database is running. There is even an app written and publically available that will run the vacuum process as needed.
