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 15: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