Atomicity

1. Atomicity in Transactions

Transactions in a database will consist of having multiple SQL statements that are executed together. Atomicity is important as it ensures that each transaction is treated as a single statement. Atomicity ensures that a transaction either is executed and committed or fails and rolls back the changes. Basically, if any of the SQL statements in a transaction fails, the entire transaction fails and the attempted changes in the transaction are reverted. If all of the statements in a transaction are executed successfully, then the transaction is successful and commit.

This approach prevents the database from making updates that may only be partially completed. The database will do one of two operations to ensure atomicity. It will either:

1. Commit – If the transaction is successful, the changes are applied and saved to the database.
2. Abort – If a transaction has any issues, the transaction is aborted, and the changes are rolled back so that they are not reflected in the database.

This includes all insert, update and delete statements in a transaction.

2. Transaction Example

Jennifer would like to make a payment to Randall for $100 through an account transfer. This transaction would be a balance transfer between two accounts at two different branches at the same bank. Let us take a look at what the transaction would look like:

1. Jennifer’s (10) account would be deducted by $100.
2. The banking location that Jennifer has her account would have their location’s account deducted by $100.
3. The banking location that Randall(50) has his account would be increased by $100.
4. Randall’s account would be increased by $100.

In looking at the transaction for the code, it would look something like this in PostgreSQL:

BEGIN;

UPDATE customer_account 
SET balance = balance – 100 
WHERE account_id = 10;

UPDATE branch_account 
SET balance = balance – 100 
WHERE branch_id = (SELECT branch_id FROM customer_account where account_id = 10);

UPDATE branch_account
SET balance = balance + 100 
WHERE branch_id = (SELECT branch_id FROM customer_account where account_id = 50);<br>

UPDATE customer_account 
SET balance = balance +100 
WHERE account_id = 50;

COMMIT;

With the atomicity property, if there was an error at any point in the 4 statements, then the entire transaction would be rolled back. For example, let us say that perhaps Randall’s account had a freeze on it not allowing any changes. The first three statements would execute but on the fourth UPDATE statement, an error would be returned. Regardless of what the error was, the first three SQL statements would be reverted back to what it was before the transaction started. Otherwise, Jennifer’s account would be deducted by $100, the bank branch that has Jennifer’s account would have their balance deducted by $100, Randall’s bank branch would have $100 added but Randall’s account would have the same original balance. That certainly would not be acceptable as to Randall, Jennifer had not made the transfer.