Difference between revisions of "Transactions"

From SQLZOO
Jump to navigation Jump to search
Line 1: Line 1:
 
==BEGIN==
 
==BEGIN==
 
*Two sessions, with and without transactions
 
*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
 
*We create the bank of wealth - it has two customers, they both have £100
 
  +--------+--------+
 
  +--------+--------+
Line 20: Line 21:
 
</div>
 
</div>
  
{{#ev:youtube|GwGKUMvZBTM}}
+
{{#ev:youtube|https://youtu.be/cy5kbSw0D_4}}
 +
 
 +
 
 +
==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                             
 +
 
 +
 
 +
{{#ev:youtube|https://youtu.be/xQ7l_H_IDzc}}

Revision as of 16:51, 31 October 2016

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