当前位置：首页 -> 基础 -> 数据库编程

《高性能mysql》札记第一波(二)

2015-01-23 22:03:53 · 作者: · 浏览: 17
标签: 《高性能 mysql 札记 第一
ansaction;
Query OK, 0 rows affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set


mysql> update test set value = 11 where id = 1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 11 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set 3 4 mysql> commit;
Query OK, 0 rows affected mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 11 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set


mysql> commit;
Query OK, 0 rows affected 5 mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set


mysql> start transaction;
Query OK, 0 rows affected


mysql> select value from test where id = 1;
+-------+
| value |
+-------+
| 11 |
+-------+
1 row in set 6 7 mysql> start transaction;
Query OK, 0 rows affected


mysql> update test set value = 1 where id = 1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0


mysql> select value from test where id = 1;
+-------+
| value |
+-------+
| 1 |
+-------+
1 row in set mysql> select value from test where id = 1;
+-------+
| value |
+-------+
| 11 |
+-------+
1 row in set 8 9 mysql> commit;
Query OK, 0 rows affected
mysql> select value from test where id = 1;
+-------+
| value |
+-------+
| 1 |
+-------+
1 row in set


mysql> commit;
Query OK, 0 rows affected		 10
从以上步骤可以看出来， 使用READ-COMMITTED的时候，一个事务内部，其查询结果会受到其他事务的update和insert影响。

隔离级别为可重复读，测试insert情况 首先我们先看一下命令行模式下，怎么 修改事务隔离级别（以下是把可重复读改成提交读，注意如果按照我的测试顺序，请注意修改）

?

?

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set

mysql>
貌似隔离级别没有变？ 关掉当前命令行，重新打开一个命令行 
mysql> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set

mysql>

事务隔离级别修改完毕后，我们开始测试
事务1 事务2
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set


mysql> start transaction;
Query OK, 0 rows affected


mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+-------+
3 rows in set		 1
2 mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set


mysql> start transaction;
Query OK, 0 rows affected


mysql> select * from test;
+----+--