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:
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:
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.
Source: Authored by Vincent Tran