一，测试准备工作:
1,新建表格test_isolation,建立唯一索引，并制定引擎为InnoDB
mysql> create table test_isolation (`id` int(10) unsigned not null,`name` char(10) not null,unique key `id`(`id`)) engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> desc test_isolation; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | name | char(10) | NO | | NULL | | +-------+------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
2，关闭自动提交，便于进行进行事物性操作
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec)
二，隔离等级的测试：Read Uncommitted，可以读取未提交的数据
1,设置隔离等级：
mysql> set session tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec) mysql> set global tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.tx_isolation,@@tx_isolation; +-----------------------+------------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+------------------+ | READ-UNCOMMITTED | READ-UNCOMMITTED | +-----------------------+------------------+ 1 row in set (0.00 sec)
2，事物A的操作记录
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_isolation; +----+------+ | id | name | +----+------+ | 1 | one | +----+------+ 1 row in set (0.00 sec) mysql> insert into test_isolation(id,name) values(2,'two'); Query OK, 1 row affected (0.00 sec) mysql> select * from test_isolation; +----+------+ | id | name | +----+------+ | 1 | one | | 2 | two | +----+------+ 2 rows in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:26:13 | +-----------+ 1 row in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.02 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:26:33 | +-----------+ 1 row in set (0.00 sec)
3，事物B的操作记录
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_isolation; +----+------+ | id | name | +----+------+ | 1 | one | | 2 | two | +----+------+ 2 rows in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:26:22 | +-----------+ 1 row in set (0.00 sec) mysql> select * from test_isolation; +----+------+ | id | name | +----+------+ | 1 | one | +----+------+ 1 row in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:26:40 | +-----------+ 1 row in set (0.00 sec) mysql>
4，结论：
事物A插入数据之后未提交，此时事物B读到这些未提交数据，之后事物A回滚了，事物B再去读这些数据时，发现不见了，即之前事物B读到的数据不存在了，是脏数据。
三，隔离等级的测试：Read Committed，只读取已提交的数据
1，设置隔离等级
mysql> set session tx_isolation='read-committed'
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> set session tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
mysql> set global tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| READ-COMMITTED | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)
mysql>
2，事物A操作记录
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_isolation; +----+------+ | id | name | +----+------+ | 1 | one | +----+------+ 1 row in set (0.00 sec) mysql> insert into test_isolation(id,name) values(2,'two'); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:46:21 | +-----------+ 1 row in set (0.00 sec)
3，事物B操作记录
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_isolation; +----+------+ | id | name | +----+------+ | 1 | one | +----+------+ 1 row in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:45:47 | +-----------+ 1 row in set (0.00 sec) mysql> select * from test_isolation; +----+------+ | id | name | +----+------+ | 1 | one | | 2 | two | +----+------+ 2 rows in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:46:26 | +-----------+ 1 row in set (0.00 sec)
4，结论:
事物B在第一次查询只有一条记录，这是事物A执行了插入数据，并且提交了；然后事物B第二次查询时发现多了一条数据，跟第一次查询结果不一样了，即同一事物中，不可以重复查询了。
四，隔离等级的测试：Repeatable，同一事物读取数据从开始之后，到提交之前都不变
1，设置隔离等级
mysql> set session tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec) mysql> set global tx_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.tx_isolation,@@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec)
2，事物A操作记录
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_isolation; +----+------+ | id | name | +----+------+ | 1 | one | | 2 | two | +----+------+ 2 rows in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:52:14 | +-----------+ 1 row in set (0.00 sec) mysql> insert into test_isolation(id,name) values(3,'three'); ERROR 1062 (23000): Duplicate entry '3' for key 'id' mysql> select * from test_isolation; +----+------+ | id | name | +----+------+ | 1 | one | | 2 | two | +----+------+ 2 rows in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:53:37 | +-----------+ 1 row in set (0.00 sec)
3，事物B操作记录
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into test_isolation(id,name) values(3,'three'); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_isolation; +----+-------+ | id | name | +----+-------+ | 1 | one | | 2 | two | | 3 | three | +----+-------+ 3 rows in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 17:53:02 | +-----------+ 1 row in set (0.00 sec) mysql>
4，结论:
事物A查询发现没有id=3的数据，这时事物B执行了插入id=3的数据，并且提交了；然后事物A执行同样id=3的数据插入，发现报错了，提示已经存在id=3的数据了，但是事物A查询却看不到id=3的数据，这是因为同一事物中读取的数据从开始之后，到提交之前都不变，即使被其他事物修改并且提交了，当前事物依然看不到这些新增的数据，
五，隔离级别：串行化Serializable
串行化即每个事物都排队一个一个进行处理，这样就不会出现并发导致的各种问题，但是这种方式会出现很多锁竞争，以及超时现象，效率比较低
Leave a Reply