Transactions

BEGIN

• 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);

Click to load content

REPEATABLE READ

• 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                               

Click to load content