-- Examples related to lecture on November 1, 2011

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;


--- 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?