Transactions allow you to support multiple access safely.
You need to use transactions when the order of processing is important and there are several users (or processes) accessing the data at the same time.
Examples of when you need to do this include customers booking seats on an airplane. You might have:
- Two customers Alice and Bob both spot there is one seat left on a flight
- They both attempt to book that seat at (roughly) the same time.
- Alice's attempt is received first but before it is completed Bob's attempt is started.
- Alice will get the seat and her credit card will be charged.
- Bob will not get the seat and his credit card will not be charged.
- Two sessions, with and without transactions
- The second session cannot see changes until they have been committed by the first session (do do otherwise would be a "dirty read").
- We create the bank of wealth - it has two customers, they both have £100
+--------+--------+ | cust | amount | +--------+--------+ | andrew | 100 | | brian | 100 | +--------+--------+
DROP TABLE wealth; CREATE TABLE wealth( cust VARCHAR(10) PRIMARY KEY, amount INT ); INSERT INTO wealth VALUES ('andrew',100); INSERT INTO wealth VALUES ('brian', 100);
- When a session starts a transaction it has a consistent copy of the database that is isolated from activity in other sessions.
- If we are at "REPEATABLE READ" level then even committed transactions (from a different session) are not visible.
Pink session White Session BEGIN; SELECT amount FROM wealth WHERE cust='andrew'; --> 100 BEGIN; UPDATE wealth SET amount=200 WHERE cust = 'andrew'; SELECT amount FROM wealth WHERE cust='andrew'; --> 100 COMMIT; SELECT amount FROM wealth WHERE cust='andrew'; --> 100 COMMIT; SELECT amount FROM wealth WHERE cust='andrew'; --> 200