Whats ACID?

I am posting about technical concepts that i learn, by the act of explaining the concepts in simple terms i am able to retain information much better and it helps me make sure i understand the concepts in a deeper way, and maybe help someone in the process, that would be awesome :-)

ACID in the context of databases stands for Atomicity, Consistency, Isolation, and Durability

1- Atomicity:

It means that transactions are atomic in the sense that either all statements in an SQL transactions are executed successfully or all of them are rolled back, a simple example would be if two bank accounts in a database and one of them is transfering an amount to the other, then the SQL transaction has at least two queries one is debiting from the first account and another crediting the other (subract the transferred amount from the first account balance and adding the same amount to the other), obviously if the first query debiting the amount succeeded but the other query failed and we didn’t rollback the first query we endup with disaster, i.e money disappears for the system, so in this case the database engine should make sure either both queries gets executed successfully or neither are executed

2- Consistency:

Continuing with the example of bank transfers, consistency means that if the database has two accounts, the total balance of all accounts has to be the same before a transfer operation and after the transfer operation, if not then we have a data inconsistency, i.e money just disappeared into thin air, so as you can see atomicity is very much coupled with consistency, as a matter of fact all the ACID concepts are interlinked and inform each other

Note: Consistency can mean also other meaning specially regarding distributed systems, for example when we have multiple duplicated databases that both can accept write operations, in this case the whole system has to also maintain internal consistency, even if eventually not immediately (i.e eventual consistency), but this is a much bigger topic for this post

3- Isolation:

Isolation is one of the most crucial and tricky concepts, and its related to how concurrent transactions are isolated from each other by the database engine, if you think about it, there’re many ways to isolate transactions, one way is to not isolate them at all :-), thats called “dirty read” isolation level, it means all transactions see each others’ changes even if they’re not commited and rolled back later, and believe it or not sometimes this is useful

another way to isolate transactions is to only allow transactions to see changes after they have been commited, this level of isolation is called “read committed”

another level of isolation is that not only does transactions see only committed changes, but if a transaction T1 read a row r1 during its execution and then another transaction T2 modified the same row r1 and committed, then T1 tried to read the same row again it will not see the changes from T2 even though it was committed, this is of course can be very useful for example if T1 is preparing a report of the past months’ payroll and during preparing the report one of rows changed because someone got promoted this month, which shouldn’t affect last months report, its maybe not the best example but the idea is to keep data consistent for the life time of the transaction, this level is called “repeatable reads”

and the highest level of isolation is “Serializable” which is the worst kind of performance, because all transactions make sure reads and writes are serialized in a queue, with one read/write at any given moment in time, it can be per row for example to make performance more bearable

4- Durability

Lastly Durability, this concept is the easiest one to explain, it basically means that data has to be persisted (durable) across machine restarts or failures, and i know you’re thinking “Duh!”, but actually some databases sacrifice some durability guarantees for the sake of scalability and performance, e.g Redis is an in memory key-value store and every now and then the in-memory data is snapshotted and persisted to disk, but of course if the server crashes in the meantime data will be lost, which is okay for a key-value store which is used many times as just a cache, but this is completely unacceptable for relational databases like postgreSQL and MySQL, which they pride themselves of much stricter implementation of ACID principals

Conclusion

ACID principals are not just theoretical concepts that only lives in books, but it affects real life database transactions and real life scenarios everyday, specially concepts like isolation levels and picking the isolation level that suites the type of data you manage, and picking the right amount of consistency you want to manage, because keeping data consistent 100% all the time is expensive and maybe if your database keeps the number of likes on a social media platform, then you can afford eventual consistency and can live with a little bit of inconsistency for a bit of time.