Atomicity is one of the four ACID properties that define a database system. While ACID properties are usually referenced in Relational DBMS, they are still an applicable concept for nearly any database system out there. Let’s see what it is…
- All queries in a transaction must succeed. Like an atom (something which can’t be split)
- If a query fails anywhere in a transaction, then all the queries executed before it in the same transaction must rollback
- If the database fails before committing a transaction, then all the successful queries in the transaction should rollback
Let’s take an example to understand it better. Here are our two beautiful tables from our Transactions post
Which means that we need to check the inventory first to see if we have enough iPhones available and if available then make the sale by updating the sales table’s QTY_SOLD column
And the task is also the same. Make a sale of 2 iPhones.
Here’s the transaction we’d proceed with:
|
1 2 3 4 5 6 7 |
Transaction BEGIN SELECT QTY FROM INVENTORY WHERE ITEM_ID = 1; If QTY >= 2: UPDATE INVENTORY SET QTY = QTY - QTY_SOLD WHERE ITEM_ID = 1; ** DATABASE CRASH ** |
Whoops.. We just did an update in our INVENTORY table so that the QTY would become 8 instead of 10 and then our DB crashed, but we didn’t get the chance to update our SALES table.
Now if our DB doesn’t implement atomicity properly, then next time we bring the DB up again we’d still get INVENTORY as 8 only. We just lost 2 iPhones (all thanks to technology). So an ideal database (which has ATOMIC property implemented) should rollback any uncommitted transactions while starting up again after a crash. And rollbacks could take quite some time depending how the database rollback process is implemented… see the questions in our Transactions post.