MySQL事务隔离级别实验

快速入门

命令

1. 连接数据库

mysql -uroot -p
mysql> use testdb

2. 准备数据

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

3. 查看当前数据库隔离级别

SELECT @@global.tx_isolation;

set global transaction isolation level read committed;

set global transaction isolation level repeatable read;

4. 会话1

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;

5. 会话二

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

6. 会话三（查看锁等待）

SELECT   waiting_trx_id,   waiting_pid,   waiting_query,   blocking_trx_id,   blocking_pid,   blocking_query FROM sys.innodb_lock_waits;

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

7. 会话1

mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;

mysql> SELECT * FROM child WHERE id > 100;

8. 会话3

mysql> SELECT trx_id, trx_state, trx_started, trx_wait_started, trx_mysql_thread_id, trx_rows_locked,  trx_isolation_level FROM information_schema.innodb_trx;

mysql> SELECT * FROM information_schema.innodb_locks;

mysql> SELECT * FROM information_schema.innodb_lock_waits;

mysql> SHOW ENGINE INNODB STATUS;

参考资料

1. Talk about MVCC and Next-key Locks
2. InnoDB Locking
3. Using InnoDB Transaction and Locking Information