Chinaunix首页 | 论坛 | 博客
  • 博客访问: 528530
  • 博文数量: 100
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1172
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(100)

文章存档

2019年(21)

2018年(17)

2017年(38)

2016年(24)

我的朋友

分类: Mysql/postgreSQL

2019-12-17 15:08:05

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址:http://blog.chinaunix.net/uid-31396856-id-5827860.html

事务的特性ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性)。MySQL在事务隔离性的实现由四种事务级别分别为:
READ-UNCOMMITTED:读未提交事务,也成为"脏读",是指一个事务还没提交时,它做的变更就能被别的事务看到;
READ-COMMITTED:读提交事务是指一个事务提交之后,它做的变更才会被其他事务看到。
REPEATABLE-READ:可重复读是指,一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。
SERIALIZABLE:串行化事务,顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行

测试环境如下:


下面通过实验来测试不通事务级别下的V1、V2及V3的值。
实验1:读未提交事务级别
##sessionB
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> #事务B
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:08:23 |
+---------------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> update T set c=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:09:02 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.02 sec)

##sessionA
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> #事务A
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T;
+------+
| c   |
+------+
|  1  |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:08:48 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T;  --V1
+------+
| c    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:09:11 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; --V2
+------+
| c    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.00 sec)


(mysql5.5)root@localhost [test]> select c from T; --V3
+------+
| c    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

总结:有上可知:V1是2,V2是2,V3是2
若隔离级别是“读未提交”, 则 V1 的值就是 2。这时候事务 B 虽然还没有提交,但是结果已经被 A 看到了。因此,V2、V3 也都是 2。

实验2:读提交事务级别
##sessionB
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> #事务B
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:15:02 |
+---------------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)


(mysql5.5)root@localhost [test]> select c from T;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> update T set c=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:15:43 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.01 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:16:26 |
+---------------------+
1 row in set (0.00 sec)

sessionA
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> #事务A
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:14:53 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:15:49 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V1
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T; ##V2
+------+
| c    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T; ##V3
+------+
| c    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
总结:
测试知:V1是1,V2和V3是2.
若隔离级别是“读提交”,则 V1 是 1,V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以, V3 的值也是 2。

实验3:可重复度事务级别
##sessionB
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:24:26 |
+---------------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)


(mysql5.5)root@localhost [test]> select c from T;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> update T set c=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:24:43 |
+---------------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:25:08 |
+---------------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.02 sec)


(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:25:16 |
+---------------------+
1 row in set (0.00 sec)

##SeesionA
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:24:13 |
+---------------------+
1 row in set (0.00 sec) 
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:24:47 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V1
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:25:05 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:25:19 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V2
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V3
+------+
| c    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

由上测试知:V1是1 ,V2是1,V3是2
若隔离级别是“可重复读”,则 V1、V2 是 1,V3 是 2。之所以 V2 还是 1,遵循的就是这个要求:事务在执行期间看到的数据前后必须是一致的。

实验4:串行化事务级别
##sessionA
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> #事务A
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)
(mysql5.5)root@localhost [test]> select c from T;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:30:44 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> 
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:34:20 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T; ##V1
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

sessionB
(mysql5.5)root@localhost [test]> 
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:31:15 |
+---------------------+
1 row in set (0.00 sec)
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(mysql5.5)root@localhost [test]> select c from T;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> update T set c=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

此处表明串行话事务级别,A事务完成B事务执行锁等待超时失败。
只有在A提交后B才可以执行。
调整GLOBAL innodb_lock_wait_timeout=3600在进行测试
sessionB
(mysql5.5)root@localhost [test]>  SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> #事务B
(mysql5.5)root@localhost [test]> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 3600  |
+--------------------------+-------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:47:44 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)


(mysql5.5)root@localhost [test]> select c from T;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> update T set c=2;
Query OK, 1 row affected (16.21 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:48:33 |
+---------------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:48:43 |
+---------------------+
1 row in set (0.00 sec)

(mysql5.5)root@localhost [test]> commit;
Query OK, 0 rows affected (0.02 sec)


##sessionA
(mysql5.5)root@localhost [test]> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> #事务A
(mysql5.5)root@localhost [test]> begin;
Query OK, 0 rows affected (0.00 sec)


(mysql5.5)root@localhost [test]> select c from T;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:47:35 |
+---------------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> 
(mysql5.5)root@localhost [test]> 
(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:48:06 |
+---------------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> select c from T; ##V1
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> select c from T; ##V2
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> commit; --提交事务A
Query OK, 0 rows affected (0.00 sec)


(mysql5.5)root@localhost [test]> select c from T; ##V2
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-12-17 14:48:40 |
+---------------------+
1 row in set (0.00 sec)


(mysql5.5)root@localhost [test]> select c from T; ##V3
+------+
| c    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)


由上测试可知:在串行话事务级别下
提交A事务之前:V1为1
提交A事务后,B事务提交前V2为1
提交B事务后,V3值为2

--The end
阅读(279) | 评论(0) | 转发(0) |
0

上一篇:MySQL支持hash join的使用和优化

下一篇:没有了

给主人留下些什么吧!~~