# What is ACID?
ACID stands for atomicity, consistency, isolation, and durability.
Earlier on in the course we learned that database operations occur in transactions. These ACID properties are part of every SQL database system.
We've learned that when working with psycopg2, using the context manager (with connection.cursor() as cursor) puts the statements inside the context manager in a transaction.
If you don't use psycopg2, for example if you want to use just plain SQL, you can precede a set of statements with BEGIN TRANSACTION and follow them with END TRANSACTION.
ACID is not something you have to implement yourself. It's a description of core concepts that SQL follows.
# Atomicity
Atomicity means that transactions are considered to be an indivisible series of operations. Every operation inside a transaction must happen, or none do: there's no possibility of a partial transaction occurring. If any operation within a transaction fails, all other operations are cancelled.
This ensures that you can group together operations, and you'll be sure that all of them will run. This is especially useful if the two transactions depend on each other, but you can group transactions that don't depend on each other and you just want them to happen at the same time.
For example, let's say you try to UPDATE and INSERT in the same transaction (two operations).
If the UPDATE fails, the INSERT will not happen. Similarly, if the INSERT fails, the UPDATE will not happen.
# Consistency
Consistency in SQL means that the rules and constraints of the database are followed at all times.
For example, if you had a foreign key constraint where options.poll_id is a foreign key to polls.id, it would mean you cannot delete a poll that has an id that is being used in the options table.
If you did that, the data in the database would no longer be valid: the option would be referencing a nonexistent poll.
Many other tutorials and guides provide confusing explanations about atomicity and consistency that often conflates the two terms. Be careful!
Atomicity is about the operations in each transaction all happening. Consistency is about the constraints being applied at all times[1].
TIP
PostgreSQL has many constraints other than foreign key. For example, UNIQUE constraints make sure that data in a column must be unique. NOT NULL makes sure data has a value.
In addition, things like the column data type (INTEGER or TEXT) are also constraints that must be followed for consistency. We cannot put a string into an INTEGER column in PostgreSQL.
# Isolation
Isolation means that data is only visible in the database once the transaction has ended (that's when the data is committed, or saved, to the disk).
So for example if you have a long transaction making 10 different INSERT queries, another separate transaction won't be able to use the result of any of those queries until the long transaction has ended.
# Durability
Durability means that the data is saved to a permanent storage once a transaction is saved or committed. Even if there is a power outage after a transaction has ended, the data will not go missing.
This is in contrast to some other databases that operate either partially or fully "in memory".
Operating in memory is much faster, since RAM has faster read and write speeds than hard drives, but the data gets lost when the device is turned off. We normally want durability for data that we want to keep and reuse, and we're not so worried about durability for data that we don't need to store.
Some examples of data you may not need to store are things like:
- Logged in status of users (if this data is lost, users need to log in again, but it's not a massive deal).
- Cache data (if this data is lost, temporarily accessing it from the source may be slower, but it's not a big deal).
# Wrapping Up
That's about it for ACID in SQL databases. Normally what happens in this topic is that you know the words and what they mean, but things don't really "click" until you have a bit more experience.
Keep learning, keep using the databases, and things will become clearer over time!