553   MySQL

一,测试准备工作:

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

Your email address will not be published. Required fields are marked *