Transactions

From SQLZOO
Revision as of 15:51, 31 October 2016 by Andr3w (talk | contribs) (→‎BEGIN)
Jump to navigation Jump to search

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


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