mysql事务隔离级别

最新推荐文章于 2024-08-23 10:27:05 发布

j_now

最新推荐文章于 2024-08-23 10:27:05 发布

阅读量390

点赞数

分类专栏： db-mysql 文章标签： mysql variables warnings query insert 工作

本文链接：https://blog.csdn.net/j_now/article/details/6272458

版权

db-mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

http://xm-king.javaeye.com/blog/770721

他写的很好

这里再看看能够说明innodb repetable-read时避免幻读的例子：

client 1:

mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.41-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
+---+---------+
4 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

client 2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

client 3:

注意 client3的操作都没有启动事务、i.e. 自动提交

---------------------------------------------------------------- 准备工作结束：

client 1:

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
+---+---------+
4 rows in set (0.00 sec)

client 3:

4 rows in set (0.00 sec)

mysql> insert into x1 (s) values ('five');
Query OK, 1 row affected (0.05 sec)

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
| 8 | five    |
+---+---------+
5 rows in set (0.00 sec)

client 1:

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
+---+---------+
4 rows in set (0.00 sec)

------------------------------------- innodb 在repetable-read级别不会出现幻读

client 2:

+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
| 8 | five    |
+---+---------+
5 rows in set (0.00 sec)

------------------------------------- 由于client2从来没有select x1，此处虽然出现 five也不能说出现幻读

client 3:

mysql> insert into x1 (s) values ('size');
Query OK, 1 row affected (0.10 sec)

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
| 8 | five    |
| 9 | size    |
+---+---------+
6 rows in set (0.00 sec)

client 1:

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
+---+---------+
4 rows in set (0.00 sec)

client 2:

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
| 8 | five    |
+---+---------+
5 rows in set (0.00 sec)

------------------------------------- innodb在repetable-read级别不会出现幻读

client 1:

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
| 8 | five    |
| 9 | size    |
+---+---------+
6 rows in set (0.00 sec)

client 2:

///

这里再看看能够说明innodb repetable-read时避免不可重复读的例子：

client 1:

mysql> show variables like '%isola%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
| 8 | five    |
| 9 | size    |
+---+---------+
6 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

client 2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

---------------------------------------------------------------- 准备工作结束：

client 1:

mysql> update x1 set s='four1' where i=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four1   |
| 8 | five    |
| 9 | size    |
+---+---------+
6 rows in set (0.00 sec)

client 2:

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
| 8 | five    |
| 9 | size    |
+---+---------+
6 rows in set (0.00 sec)

------------------------------------- innodb在repetable-read是不会出现脏读

client 1:

mysql> commit;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four1   |
| 8 | five    |
| 9 | size    |
+---+---------+
6 rows in set (0.00 sec)

client 2:

mysql> select * from x1;
+---+---------+
| i | s       |
+---+---------+
| 1 | one     |
| 2 | two     |
| 3 | triple1 |
| 4 | four    |
| 8 | five    |
| 9 | size    |
+---+---------+
6 rows in set (0.01 sec)

------------------------------------- innodb在repetable-read是不会出现不可重复读

that's all, thank u