-- Examples related to lecture on October 26, 2012
-- Rasmus Pagh

create database cc;
connect cc;
create table primes(p int) engine=innoDB;
--- Concurrency control only works with innoDB (default engine).


--- Disable commit after each statement (in each connection):
SET autocommit=0;


--- READ COMMITTED
--- Transaction 1:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Default isolation level is REPEATABLE READ
SELECT * FROM primes;
INSERT INTO primes VALUES (43)
SELECT * FROM primes;

--- Transaction 2:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM primes;
INSERT INTO primes VALUES (41)
SELECT * FROM primes;

--- Different sequences of database states observed!


--- Transaction 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2nd. SELECT * from primes;
4th. SELECT * from primes;
6th. commit
     SELECT * from primes;

--- Transactions 2,3:
1st. INSERT INTO primes VALUES (101);
3rd. commit;
5th. INSERT INTO primes VALUES (103);
     commit;


--- SELECT FOR UPDATE

T2: mysql> select * from primes;
+------+
| p    |
+------+
|    2 |
|    3 |
|    5 |
+------+
3 rows in set (0.00 sec)

T1: mysql> select * from primes where p=3 for update;
+------+
| p    |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

T2: mysql> update primes set p=7 where p=3;
... (waiting for lock)

T1: mysql> update primes set p=11 where p=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

T1: mysql> commit;
Query OK, 0 rows affected (0.01 sec)

T2:
... (lock received)
Query OK, 0 rows affected (44.95 sec)
Rows matched: 0  Changed: 0  Warnings: 0

T2: mysql> select * from primes;
+------+
| p    |
+------+
|    2 |
|   11 |
|    5 |
+------+
3 rows in set (0.00 sec)


--- SERIALIZABLE

--- Locking and deadlocking I

--- Transaction 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1st. INSERT INTO primes VALUES (47)
3rd. SELECT * FROM primes

--- Transaction 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2nd. INSERT INTO primes VALUES (49)
4th. SELECT * FROM primes


--- Locking and deadlocking II

alter table primes add constraint primary key(p);

--- Transaction 1:
1st. INSERT INTO primes VALUES (7)
3rd. INSERT INTO primes VALUES (11)

--- Transaction 2:
2nd. INSERT INTO primes VALUES (11)
4th. INSERT INTO primes VALUES (7)


--- Some storage engines just ignore transactions and concurrency control:

create table prim (p int) engine=myISAM;
insert into prim values (2);
rollback;
select * from prim;


--- DBMSs try to maintain the illusion that single SQL statements are atomic.
--- This sometimes fails:

mysql> select * from primes;
+----+
| p  |
+----+
| 11 |
| 13 |
+----+
2 rows in set (0.00 sec)

mysql> update primes set p=p+2;
ERROR 1062 (23000): Duplicate entry '13' for key 'PRIMARY'

-- Bug or feature?


--- Some DBMSs are not truly ACID. Try this in Oracle:

T1: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
T2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
T1: INSERT INTO primes VALUES (19)
T2: INSERT INTO primes VALUES (23)
T1: INSERT INTO primes (SELECT COUNT(*) FROM primes)
T2: INSERT INTO primes (SELECT COUNT(*) FROM primes)
T1: commit;
T2: commit;

--- What serial schedule does the result correspond to?