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