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