事务并发导致的问题是数据库需要重点解决的问题,关于事务处理的技术都已经非常成熟了,四种隔离级别再加上一个快照是所有数据库通行的解决方案,各种数据 库只是在细节上略有不同而已。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: innodb
Create Table: CREATE TABLE `innodb` (
`name` char(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 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分别加上共享锁和排他锁。
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: innodb
Create Table: CREATE TABLE `innodb` (
`name` char(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 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分别加上共享锁和排他锁。