Online College Courses for Credit

+
Atomicity

Atomicity

Rating:
(0)
Author: Sophia Tutorial
Description:

Recall how the atomicity property requires all SQL statements in a transaction must be completed, otherwise, the entire transaction needs to be aborted as a single logical unit.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores the atomicity property in a transaction and how it affects the database.

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. 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);

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.


summary
The atomicity property ensures that either all SQL statements are executed or none of them are executed in a transaction.