Transactions

1. Introduction

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 in the transaction have executed successfully. 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.

2. Examples

Let us take a look at a scenario in which a transaction would be necessary. 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 being transferred from James to the store, and the second is the computer being deducted from the store inventory and transferred to James. The basic SQL statements would look something like the following:

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;

As you can see, there are multiple SQL statements that are needed to accomplish this operation. Both James and the store would want to be assured that either all of these statements occur, or none of them do. It would not be acceptable if James’s account was deducted by $500, the inventory for the store had the computer removed, and then there was a system failure. This would mean that James would not get the order and the store would not get the $500 that James had paid. We need to ensure that if anything goes wrong at any point within the entire operation, that none of the statements that had been executed so far would take effect. This is where the use of a transaction is valuable.

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;

A transaction can contain a single statement or a dozen SQL statements. Note that each SQL statement needs 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.