Online College Courses for Credit

+
Transactions

Transactions

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify a correctly designed transaction block to ensure that a series of INSERT, UPDATE and DELETE statements either all execute or don't execute at all.

(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 concept of transactions in a database to ensure multiple statements execute in an all-or-nothing scenario.

Transactions are a core feature of every database system. The purpose of a transaction is to combine multiple SQL statements together into a scenario that would either execute all of the statements or none of them. Each individual SQL statement within a transaction is not visible to other concurrent transactions in the database as they are not saved to the database unless all of the statements have executed successfully in the transaction. If at any point, there is a failure that occurs in any of the statements within a transaction, none of the steps or statements affect the database at all.

Let us take a look at a scenario in which the transaction would be necessary rather than saving or committing the data after every step. James has gone to an online computer store and purchased a new computer for $500. In this transaction, there are two things to track. The first is the $500 going from James to the store, the second is the computer item being deducted from the inventory and giving it to James’ account. The basic SQL statements may look something like the following (as a basic generic structure):

UPDATE customer_account
SET balance = balance – 500
WHERE account_id = 1000;

UPDATE store_inventory
SET quantity = quantity – 1
WHERE store_id = 5 AND product_name = ‘Computer’;

INSERT INTO customer_order(account_id, product_name,store_id, quantity,cost)
VALUES (1000, ‘Computer’,5, 1, 500);

UPDATE store_account
SET balance = balance + 500
WHERE store_id = 5;

This is very basic of a series of statements and the details of the statements are not important. However, what is important is that there are multiple SQL statements that are needed to accomplish this operation. The store as well as James would want to be assured that all of these statements occur or none of them happen. It would not be acceptable if James’ account was deducted by $500 and the inventory for the store had the item removed and there was a system failure after the second statement. This would mean that James would not get the order in place and the store would not get the $500 that James had paid. We would need to ensure that if anything goes wrong at any point within the entire operation that none of the statements that were executed so far would take effect. This is where the use of a transaction comes into play as a transaction does ensure this. A transaction would be viewed as being atomic as it would either happen completely or not at all.

To set up a transaction, we need to start with the BEGIN command, have our list of commands, and then end with COMMIT; similar to our prior example:

BEGIN;

UPDATE customer_account
SET balance = balance – 500
WHERE account_id = 1000;

UPDATE store_inventory
SET quantity = quantity – 1
WHERE store_id = 5 AND product_name = ‘Computer’;

INSERT INTO customer_order(account_id, product_name,store_id, quantity,cost)
VALUES (1000, ‘Computer’,5, 1, 500);

UPDATE store_account
SET balance = balance + 500
WHERE store_id = 5;

COMMIT;

This could be with a single statement or a dozen SQL statements inside. Each SQL statement does need to end with a semicolon to separate each out individually.

PostgreSQL and other databases treat each SQL statement that is executed as if it were an individual transaction. If we do not include a BEGIN command, then each of the SQL statements (INSERT, UPDATE, DELETE, etc) has an implicit BEGIN command and a COMMIT command if the statement is successful.


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
Transactions in a database to ensure multiple statements execute in an all-or-not scenario.