Welcome 微信登录

首页 / 数据库 / MySQL / MySQL事务隔离级别

事务并发导致的问题是数据库需要重点解决的问题，关于事务处理的技术都已经非常成熟了，四种隔离级别再加上一个快照是所有数据库通行的解决方案，各种数据 库只是在细节上略有不同而已。MySQL支持多种存储引擎，每种存储引擎各有特点，MyISAM速度较快，但是其不支持事务处理，并发时能控制的粒度太 粗。InnoDB是一个非常好的存储引擎，它已经被Oracle收购了，Oracle这几年实在疯狂，InnoDB和Berkeley DB都被其纳入囊中，这次MySQL都未能幸免。Linux版的MySQL默认使用的存储引擎是MyISAM，而Windows版则使用InnoDB。下 面的讨论都是使用InnoDB的表。MySQL默认的隔离级别是可重复读。
mysql> show variables like "%iso%";+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| tx_isolation  | REPEATABLE-READ | +---------------+-----------------+1 row in set （0.00 sec）        脏读，不可重复读，幻读是事务并发导致的几个主要的问题，可重复读是用来解决不可重复读的问题，但是MySQL的可重复读级别似乎有点神奇，它可以避免 幻读的。如果使用快照技术的话，可以一次解决上面所有的问题，不知道InnoDB是怎样做的。       首先测试可重读隔离级别的行为。1、创建如下所示的表，注意要使用InnoDB引擎：mysql> show create table innodb G*************************** 1. row ***************************       Table: innodbCreate Table: CREATE TABLE `innodb` （  `name` char（20） DEFAULT NULL,  `age` int（11） DEFAULT NULL） ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set （0.00 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）3、插入一条要用的测试记录mysql> insert into innodb values（"fzj", 25）;Query OK, 1 row affected （0.00 sec）mysql> select * from innodb;+------+------+| name | age  |+------+------+| fzj  |   25 | +------+------+1 row in set （0.00 sec）4、开始测试，两个客户端分别用红色和蓝色区别，给它们取个名字分别为A和B吧。1>解决不可重复读问题mysql> start transaction;Query OK, 0 rows affected （0.05 sec）
mysql> update innodb set age=age+5 where name="fzj";Query OK, 1 row affected （0.02 sec）Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from innodb;+------+------+| name | age  |+------+------+| fzj  |   30 | +------+------+1 row in set （0.00 sec）mysql> start transaction;Query OK, 0 rows affected （0.00 sec）
mysql> select * from innodb where name="fzj";+------+------+| name | age  |+------+------+| fzj  |   25 | +------+------+1 row in set （0.00 sec）此处可见，B事务避免了脏读。mysql> commit    -> ;Query OK, 0 rows affected （0.04 sec）
mysql> select * from innodb where name="fzj";+------+------+| name | age  |+------+------+| fzj  |   25 | +------+------+1 row in set （0.00 sec）即便是A事务提交了，B事务查询的结果仍然不受干扰，这是可重复读隔离级别的典型特性。可是如果 B事务也要更新同一条记录怎么办？基于这个数字会导致A事务更新的丢失。mysql> update innodb set age=age+5 where name="fzj";Query OK, 1 row affected （0.00 sec）Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from innodb where name="fzj";+------+------+| name | age  |+------+------+| fzj  |   35 | +------+------+1 row in set （0.00 sec）可见A事务的更新并没有丢失。如果在A事务未提交之前，B事务更新同一条记录会导致B事务被阻塞，update需要对相应行加上行锁，这种排他 锁是独占性的。2>解决幻读问题mysql> start transaction;Query OK, 0 rows affected （0.00 sec）
mysql> insert into innodb values（"ecy", 25）;Query OK, 1 row affected （0.00 sec）
mysql> start transaction;Query OK, 0 rows affected （0.04 sec）
mysql> select count（*） from innodb;+----------+| count（*） |+----------+|        1 | +----------+1 row in set （0.02 sec）mysql> commit    -> ;Query OK, 0 rows affected （0.05 sec）
mysql> select count（*） from innodb;+----------+| count（*） |+----------+|        1 | +----------+1 row in set （0.00 sec）可见A事务成功地插入了一条记录，因为 它没有同B事务竞争锁，所以A事务插入记录和B事务更新记录可以同时进行，可是A事务提交之后并没有导致B事务幻读，MySQL的可重复读隔离级别确实能 避免幻读。
3>serializable隔离级别serializable隔离级别是最高的隔离级别，一个事务中的查询语句会给相应的行加上排他锁，直到事务结束。mysql> set session transaction isolation level serializable;Query OK, 0 rows affected （0.00 sec）mysql> start transaction;Query OK, 0 rows affected （0.00 sec）mysql> update innodb set age=age+6 where name="fzj";Query OK, 1 row affected （0.00 sec）Rows matched: 1  Changed: 1  Warnings: 0
mysql> set session transaction isolation level serializable;Query OK, 0 rows affected （0.00 sec）mysql> start transaction;Query OK, 0 rows affected （0.00 sec）mysql> select * from innodb where name="fzj";B事务此时会被阻塞，因为A事务要跟新"fzj"这一行，因此给这行加上了排它锁，B事务再将给 其加上共享锁将会失败。使用A事务commit之后，B事务才会往下执行（我发现让我吃惊的行为，在B事务中即使查询name="ecy"这行，B事务也 会被阻塞，难道使用了表锁定？？）。比较REPEATABLE-READ隔离级别可以发现，在可重复读隔离级别不会给查询语句加上共享锁，如果需要的话可 以显示地使用select ... lock in share mode和select ... for update分别加上共享锁和排他锁。配置Ubuntu Linux默认是不区分MySQL表名的大小写的操作访问Linux下MySQL服务器数据库相关资讯      mysql 
  • 数据库服务器 MySQL  （08/15/2013 06:50:23）
  • MySQL 5.6 GA 及逃亡潮  （02/08/2013 14:36:35）
  • MySQL 5.5.22、5.1.62、5.0.96全线  （03/22/2012 19:03:49）
  • MySQL Administrator连接VMWare下  （05/24/2013 09:20:58）
  • MySQL 5.1.68 发布  （02/05/2013 08:37:47）
  • CentOS 5.2+MySQL+Heartbeat双机互  （01/29/2012 11:16:55）
本文评论 查看全部评论 （0）
表情： 姓名： 字数