澳门新葡萄京网站 > 新葡萄京 计算机网络 > mysql中不同事务隔离级别下数据的显示效果

mysql中不同事务隔离级别下数据的显示效果
2019-12-10 16:11

   事务是一组原子性的SQL查询语句,也可以被看做一个工作单元。如果数据库引擎能够成功地对数据库应用所有的查询语句,它就会执行所有查询,如果任何一条查询语句因为崩溃或其他原因而无法执行,那么所有的语句就都不会执行。也就是说,事务内的语句要么全部执行,要么一句也不执行。

当前在开发ERP系统,使用到的数据库为Mysql。下面介绍下如何开启事务,以及事务隔离的机制 :

  事务的特性:acid,也称为事务的四个测试(原子性,一致性,隔离性,持久性)

  1. 检查当前数据库使用的存储引擎。

  automicity:原子性,事务所引起的数据库操作,要么都完成,要么都不执行

show engines;

  consisitency:一致性,事务执行前的总和和事务执行后的总和是不变的

澳门新葡萄京官网注册 1

澳门新葡萄京官网网址,  isolation:隔离性, 某个事务的结果只有在完成之后才对其他事务可见

  1. 修改前my.ini中的文件如下:

  durability:持久性,一旦事务成功完成,系统必须保证任何故障都不会引起事务表现出不一致性

澳门新葡萄京官网注册 2

  事务的状态:

  1. 修改my.ini的文件如下。

  活动

     3.1 修改了默认的存储引擎

  部分提交

     3.2 增加了mysql数据库的事务隔离级别, 如果不添加默认是REPEATABLE-READ.

  失败

澳门新葡萄京官网注册 3

  中止

 

  提交

4. 只需要重启mysql的服务即可。

  事务在某一时刻,一定处于上边五种状态中的一种,事务各状态之间的转换如下所示:

    net stop mysql 

澳门新葡萄京官网注册 4

    net start mysql

  事务并发导致的问题

    澳门新葡萄京官网注册 5

  脏读(Drity Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据就会是不正确的。

  1. 再次查询show engines,显示如下

  不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新了原有的数据。

澳门新葡萄京官网注册 6

  幻读(Phantom Read):在一个事务的两次查询中数据不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。

  1. 事务隔离机制的说明,以下引用自别人的文章

  并发控制


  多版本并发控制: Multiversion concurrency control,MVCC

 

  每个用户操作数据时都是源数据的时间快照,当用户操作完成后,依据各快照的时间点在合并到源数据中

  • 未提交读(READ UNCOMMITTED)。另一个事务修改了数据,但尚未提交,而本事务中的SELECT会读到这些未被提交的数据(脏读)。
  • 提交读(READ COMMITTED)。本事务读取到的是最新的数据(其他事务提交后的)。问题是,在同一个事务里,前后两次相同的SELECT会读到不同的结果(不重复读)。如两个会话a和b,由于是read committed所以只有当事务提交后才能被别的事务可见,当a执行查询后b执行插入,b执行commit提交事务,这时a再次查询结果确实不一样的,a的两次查询同属于一个事务,即为不可重复读。
  • 可重复读(REPEATABLE READ)。在同一个事务里,SELECT的结果是事务开始时时间点的状态,因此,同样的SELECT操作读到的结果会是一致的。直到提交事务后再查询才可以看到其他事务在本事务执行期间锁进行的更改操作。在MySQL中InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC)解决了幻读的问题。
  • 串行化(SERIALIZABLE)。读操作会隐式获取共享锁,可以保证不同事务间的互斥。
  • SQL 事务隔离级别 

  锁:要想实现并发控制,最简单的实现机制就是锁(MVCC采用的不是锁机制)。

 澳门新葡萄京官网注册 7

  读锁:共享锁,由读表操作加上的锁,加锁后其他用户只能获取该表或行的共享锁,不能获取排它锁,也就是说只能读不能写

 

  写锁:独占锁,由写表操作加上的锁,加锁后其他用户不能获取该表或行的任何锁

 

  锁粒度:从大到小,MySQL服务器仅支持表级锁,行锁需要存储引擎完成。

 

  表锁:锁定某个表

在MySQL中默认事务隔离级别是可重复读(Repeatable read).可通过SQL语句查询:

  页锁:锁定某个页

查看InnoDB系统级别的事务隔离级别:mysql> SELECT @@global.tx_isolation;

  行锁:锁定某行

 

澳门新葡萄京官网首页,  粒度越精细,并发性越好。即行锁的并发性最好,但需要存储引擎的支持。

在MySQL中默认事务隔离级别是可重复读(Repeatable read).可通过SQL语句查询:

  事务的四种隔离级别

查看InnoDB系统级别的事务隔离级别:

  读未提交(read uncommitted): 允许脏读,也就是可能读取到其他会话中未提交事务修改的数据

 

  读提交(read committed): 只能读取到已经提交的数据。oracle等多数数据库默认都是该级别

    mysql> SELECT @@global.tx_isolation;

  可重读(repeatable read): 在同一个事务内的查询都是事务开始时刻一致的,innodb的默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读

 

  可串行(serializable): 完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

结果:

  在MySQL中,在并发控制情况下,不同隔离级别分别有可能产生问题如下所示:

+-----------------------+
澳门新葡萄京官网注册,| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

澳门新葡萄京官网注册 8

 

  上边之所以介绍那么多理论知识,是为了便于理解。在上边的表格中已经列出来了,在不同隔离级别下,数据的显示效果可能出现的问题,现在在linux上安装好mysql,通过我们的实验来一起看一下在不同隔离级别下数据的显示效果吧。

查看InnoDB会话级别的事务隔离级别:

  实验环境:

 

  linux系统:RedHat 5.8

  mysql> SELECT @@tx_isolation;

  linux内核:linux-2.6.18-308.el5

 

  mysql版本:mysql-5.6.10-linux-glibc2.5-i686

结果:
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

  本次实验的所有操作均在虚拟机中完成,通过Xmanager连接虚拟机,然后打开两个会话连接,在两个会话中,同时更改隔离级别,然后查看数据的显示效果。

 

  本次实验中mysql采用源码编译安装的方式安装mysql,你也可以使用rpm包的方式直接安装mysql。具体源码安装的方式及过程,这里不再演示,在前面的博客中,我已经介绍了很多次。如果你采用源码编译安装的方式,不知道如何安装mysql,可参看我以前写的博客,里边都有介绍。采用源码编译安装的方式,在mysql的配置文件中,最好启用每表一个表空间。这里我们直接启用。

修改事务隔离级别:

  因为是实验,这里没有对mysql设置密码,因此,我们直接使用命令进入mysql。命令及显示效果如下:

    mysql> set global transaction isolation level read committed;

  50[[email protected] ~]# mysql -uroot -p #使用该命令进入mysql,因为没有设置密码,在要求输入密码时直接按回车键即可

    Query OK, 0 rows affected (0.00 sec)

  Enter password:

    mysql> set session transaction isolation level read committed;

  Welcome to the MySQL monitor. Commands end with ; or g.

    Query OK, 0 rows affected (0.00 sec)

  Your MySQL connection id is 2

 

  Server version: 5.6.10 MySQL Community Server (GPL)

InnoDB 的可重复读隔离级别和其他数据库的可重复读是有区别的,不会造成幻象读(phantom read),所谓幻象读,就是同一个事务内,多次select,可以读取到其他session insert并已经commit的数据。下面是一个小的测试,证明InnoDB的可重复读隔离级别不会造成幻象读。测试涉及两个session,分别为 session 1和session 2,隔离级别都是repeateable read,关闭autocommit

  Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

 

  Oracle is a registered trademark of Oracle Corporation and/or its

    mysql> select @@tx_isolation;   

  affiliates. Other names may be trademarks of their respective

    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set (0.00 sec)    

  owners.

    mysql> set autocommit=off;

  Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

    Query OK, 0 rows affected (0.00 sec)

  mysql> show variables like '%iso%'; #查看mysql默认的事务隔离级别,默认为可重读。也可以使用select @@tx_isolation命令查看

 

  +-----------------+------------------+

session 1 创建表并插入测试数据

  | Variable_name | Value |

     mysql> create table test(i int) engine=innodb;

  +-----------------+------------------+

    Query OK, 0 rows affected (0.00 sec)

  | tx_isolation | REPEATABLE-READ |

    mysql> insert into test values(1);
    Query OK, 1 row affected (0.00 sec)

  +-----------------+------------------+

 

  1 row in set (0.36 sec)

session 2 查询,没有数据,正常,session1没有提交,不允许脏读
     mysql> select * from test;
     Empty set (0.00 sec)

  mysql> show databases; #查看系统已经存在的数据库

 

  +---------------------+

session 1 提交事务

  | Database |

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

  +---------------------+

 

  | information_schema |

session 2 查询,还是没有数据,没有产生幻象读
                        
    mysql> select * from test;
    Empty set (0.00 sec)
                        
当session2提交事务后才可以看到session1的插入数据;                       
以上试验版本:
                        
    mysql> select version();
    +-------------------------+
    | version()               |
    +-------------------------+
    | 5.0.37-community-nt-log |
    +-------------------------+
    1 row in set (0.00 sec)

  | mysql |

  | performance_schema |

  | test |

  +---------------------+

  4 rows in set (0.00 sec)

  现在导入我们实验所使用的数据库。

  [[email protected]mysql ~]# mysql < jiaowu.sql #导入实验所用的jiaowu数据库

  [[email protected] ~]# mysql -uroot -p

  Enter password:

  Welcome to the MySQL monitor. Commands end with ; or g.

  Your MySQL connection id is 7

  Server version: 5.6.10 MySQL Community Server (GPL)

  Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

  Oracle is a registered trademark of Oracle Corporation and/or its

  affiliates. Other names may be trademarks of their respective

  owners.

  Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

  mysql> show databases; #查看导入的jiaowu数据库是否存在

  +----------------------+

  | Database |

  +----------------------+

  | information_schema |

  | jiaowu |

  | mysql |

  | performance_schema |

  | test |

  +----------------------+

  5 rows in set (0.01 sec)

  我们在mysql命令界面下,没有明确启用事务时,输入的每个命令都是直接提交的,因为mysql中有个变量的值,可实现自动提交。也就是说我们每输入一个语句,都会自动提交,这会产生大量的磁盘IO,降低系统的性能。在我们做实验时,因为我们要明确使用事务,所以,建议关闭自动提交的功能,如果不关闭也没有关系,但是如果你没有明确使用事务,想要做下边的实验,那就需要关闭此功能了。这里,我们明确使用事务,且关闭自动提交功能。假如你关闭了自动提交功能,需明确使用事务,否则你输入的所有语句会被当成一个事务进行处理。命令如下: