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