Transactions
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