Atomicity in ACID

Atomicity is the first principle of the ACID property. It is so important to know about that when you are designing database.

It states that all queries in a transaction must succeed; if one or more queries fail, then all the queries that succeed will be rolled back to the previous. Even if the database crashed, then all the queries that succeed will be rolled back to the previous

Let’s say for example, we have an accounts table.

idcustomerbalance
827823lahin4000
827824sadek2000
827825rasel3000

So start the transection,

  • SELECT * FROM accounts WHERE customer=lahin

It will return the full row of lahin. Another query of the same transection,

  • UPDATE accounts SET balance = balance – 100 WHERE customer=lahin

When processing the UPDATE query our database crashed or any disaster happened. But we have some more queries that need to be processed.

So when we restart our system,

idcustomerbalance
827823lahin3900
827824sadek2000
827825rasel3000

We can see the balance of lahin is 3900. This can create a very big issue. After our database crashed no other process took place, so when we restart our system where is 100? We don’t have any record for 100.

A few queries were also left unprocessed because of the disaster.

Atomicity ensures that once a disaster happens, all queries for a transection will be rolled back. Then the table will look like this,

idcustomerbalance
827823lahin4000
827824sadek2000
827825rasel3000

After the disaster lahin’s balance rolled back.

Relational Databases support Atomicity very well.

Leave a Comment

Your email address will not be published. Required fields are marked *