Difference between revisions of "Transactions"

From SQLZOO
Jump to: navigation, search
(REPEATABLE READ)
(One transaction must die)
Line 116: Line 116:
  
 
{{#ev:youtube|https://youtu.be/o1n5tQFqr1w}}
 
{{#ev:youtube|https://youtu.be/o1n5tQFqr1w}}
 +
 +
==Update Madness==
 +
We will try a naive update to see how badly things can go wrong. We will then try to fix it so that the errors are more manageable.
 +
 +
We create the wealth table and add two rows:
 +
<source lang=sql>
 +
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);
 +
</source>
 +
We create a php program <code>trnt.php</code> to transfer money from andrew to brian or the other way around.
 +
<source lang=php>
 +
<?php
 +
$payer = $argv[1];
 +
$payee = $argv[2];
 +
$value = $argv[3];
 +
$dbh = new PDO('mysql:host=localhost;dbname=40000036','40000036','juryLTdm');
 +
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
 +
$sth = $dbh->prepare("SELECT amount FROM wealth WHERE cust=?");
 +
$sth->execute(array($payer));
 +
$a = $sth->fetchAll()[0][0];
 +
$sth->execute(array($payee));
 +
$b = $sth->fetchAll()[0][0];
 +
$sth = $dbh->prepare("UPDATE wealth SET amount=? WHERE cust=?");
 +
$sth->execute(array($a-$value,$payer))
 +
$sth->execute(array($b+$value,$payee))
 +
echo "$payer has ".($a-$value).", $payee has ".($b+$value)."\n";
 +
</source>
 +
This can be run from the command line - you give three parameters - payer, payee and amount.
 +
 +
This command gives one pound from andrew to brian:
 +
<source lang=bash>
 +
php trnt.php andrew brian 1
 +
</source>

Revision as of 18:12, 1 November 2016

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.

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                               


Transaction left hanging

  • Two transactions attempt to increase andrew's wealth
  • One attempts to add £10 the other attempts to add £20
  • After both transactions succeed andrew will be £30 richer.
Pink session                   White Session
BEGIN;
                               BEGIN;
UPDATE wealth
   SET amount=amount+10
 WHERE cust = 'andrew';
                               UPDATE wealth
                                  SET amount=amount+20
                                WHERE cust = 'andrew';
                               --This session hangs; its fate rests on the other session
                               --If the other session does rollback andrew will have £120
                               --If the other session does commits  andrew will have £130
COMMIT;
                               SELECT * FROM wealth WHERE cust='andrew';
                               +--------+--------+
                               | cust   | amount |
                               +--------+--------+
                               | andrew |    130 |
                               +--------+--------+


One transaction must die

  • We try the same pair of transactions as above but this time we do a read first.
Pink session                   White Session
BEGIN;
                               BEGIN;
                               SELECT * FROM wealth WHERE cust='andrew';
                               +--------+--------+
                               | cust   | amount |
                               +--------+--------+
                               | andrew |    130 |
                               +--------+--------+
UPDATE wealth
   SET amount=amount+10
 WHERE cust = 'andrew';
--Hangs
                               UPDATE wealth
                                  SET amount=amount+20
                                WHERE cust = 'andrew';
--Fails                        --Succeeds
                               COMMIT;
                               SELECT * FROM wealth WHERE cust='andrew';
                               +--------+--------+
                               | cust   | amount |
                               +--------+--------+
                               | andrew |    150 |
                               +--------+--------+


Update Madness

We will try a naive update to see how badly things can go wrong. We will then try to fix it so that the errors are more manageable.

We create the wealth table and add two rows:

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

We create a php program trnt.php to transfer money from andrew to brian or the other way around.

<?php
$payer = $argv[1];
$payee = $argv[2];
$value = $argv[3];
$dbh = new PDO('mysql:host=localhost;dbname=40000036','40000036','juryLTdm');
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
$sth = $dbh->prepare("SELECT amount FROM wealth WHERE cust=?");
$sth->execute(array($payer));
$a = $sth->fetchAll()[0][0];
$sth->execute(array($payee));
$b = $sth->fetchAll()[0][0];
$sth = $dbh->prepare("UPDATE wealth SET amount=? WHERE cust=?");
$sth->execute(array($a-$value,$payer))
$sth->execute(array($b+$value,$payee))
echo "$payer has ".($a-$value).", $payee has ".($b+$value)."\n";

This can be run from the command line - you give three parameters - payer, payee and amount.

This command gives one pound from andrew to brian:

php trnt.php andrew brian 1