MySQL中默认使用REPEATABLE-READ的事务隔离级别,可以避免脏读,不可重复读但是仍然会出现幻读现象。确切的说,MySQL的InnoDB可以在一定程度上防止幻读,但是不能完全避免。
Oracle默认使用READ COMMITTED事务隔离级别,
By default, InnoDB operates in REPEATABLE READ transaction isolation
level and with the innodb_locks_unsafe_for_binlog system variable
disabled. In this case, InnoDB uses next-key locks for searches and
index scans, which prevents phantom rows (see Section 13.6.8.5,
“Avoiding the Phantom Problem Using Next-Key Locking”).
To prevent phantoms, InnoDB uses an algorithm called next-key
locking that combines index-row locking with gap locking.
You can use next-key locking to implement a uniqueness check in your
application: If you read your data in share mode and do not see a
duplicate for a row you are going to insert, then you can safely
insert your row and know that the next-key lock set on the successor
of your row during the read prevents anyone meanwhile inserting a
duplicate for your row. Thus, the next-key locking enables you to
“lock” the nonexistence of something in your table.
MySQL manual里对可重复读里的锁的详细解释:
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE
MODE),UPDATE, and DELETE statements, locking depends on whether the
statement uses a unique index with a unique search condition, or a
range-type search condition. For a unique index with a unique search
condition, InnoDB locks only the index record found, not the gap
before it. For other search conditions, InnoDB locks the index range
scanned, using gap locks or next-key (gap plus index-record) locks
to block insertions by other sessions into the gaps covered by the
range.
第一个测试示例如下(不手动加锁)
① 事务A查询表中id为9的数据
没有没有还是没有!
② 事务B向表中插入id为9的数据,暂不提交
start TRANSACTION;
insert into t_user(id,name,age)VALUES(9,'jane1',18);
SELECT * from t_user;
没提交的数据在日志中,没有持久化到数据库!!
③ 事务A去尝试更新id为9的数据
mysql> update t_user set name='jane00'where id=9;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
锁等待超时,什么鬼,谁加锁了(事务B的insert 添加了排它锁)?
④ 事务B将事务提交,此时数据持久化到数据库
⑤ 事务A再次尝试更新id为9的数据
mysql> update t_user set name='jane00'where id=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
发生了什么?怎么成功了,不是没有该条数据么?让我再次查一下看看:
我擦我擦,见鬼了,凭空出现了数据!!!
update 时使用了当前读(读取最新数据),再次查询的时候会查询最新数据。
第二个测试实例如下(手动加共享锁)
① 事务A查询id为10的数据并使用共享锁
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user where id =10 lock in share mode;
Empty set (0.00 sec)
② 事务B尝试插入id为10的数据
start TRANSACTION;
SELECT * from t_user;
insert into t_user(id,name,age)VALUES(10,'jane1',18);
被阻塞了,等待然后到来的是锁等待超时(事务A已经加了行级锁中的共享锁,事务B只能读,不能写):
Err] 1205 - Lock wait timeout exceeded; try restarting transaction
③ 事务A尝试更新id为10的数据
mysql> update t_user set name='janei' where id =10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
# 很显然 空语句,那就提交吧。
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
④ 事务B再次插入数据并提交
insert into t_user(id,name,age)VALUES(10,'jane1',18);
SELECT * from t_user;
COMMIT;
此时数据表中有了id为10的数据:
唔,使用共享锁好像可以了,就是等待超时会抛异常。
第三个测试示例如下(使用排它锁/独占锁/互斥锁)
① 事务A查询当前表
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user for update;
# 没有使用索引,将会加表锁
唔,很好,没有id为11的数据。
② 事务B尝试插入id为11的数据
插入前先使用排它锁查一下吧
start TRANSACTION;
select * from t_user for update;
# 直接爆异常--表被事务加表锁了,不能再加其他锁
[SQL]select * from t_user for update;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
事务A不提交,事务B是没法执行的。使用共享锁和排它锁貌似挺好用的,应该没有其他问题了吧?
第四个测试示例如下(同样使用for update)
此时数据表数据如下:
① 事务A对max(id)进行加锁
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select max(id) from t_user for update;
+---------+
| max(id) |
+---------+
| 10 |
+---------+
1 row in set (0.00 sec)
# [10,+∞)范围内的id都被加了间隙锁+X锁。
② 事务B尝试插入id 4 5 和20的 数据并提交事务。
start TRANSACTION;
insert into t_user(id,name,age)VALUES(4,'jane1',18);
insert into t_user(id,name,age)VALUES(5,'jane1',18);
insert into t_user(id,name,age)VALUES(20,'jane1',18);
COMMIT;
id为4和5数据插入正常,id为20数据插入失败。
[SQL]
insert into t_user(id,name,age)VALUES(20,'jane1',18);
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
③ 事务A尝试更新id为4的记录并查询表记录数
mysql> update t_user set name='januie'where id=4;
Query OK, 1 row affected (42.56 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t_user ;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小明 | 18 |
| 2 | janus | 18 |
| 3 | 明天 | 18 |
| 4 | januie | 18 |
| 5 | jane1 | 18 |
| 8 | jane1 | 18 |
| 9 | jane00 | 18 |
| 10 | jane1 | 18 |
+----+--------+------+
8 rows in set (0.00 sec)
什么情况?事务A已经使用了for update,怎么事务B还能插进去?为什么插入 id 为4和 5 正常,插入id为20失败?
事务A不光update成功了,而且查询记录还多出来两条!!!
第五个测试示例(不加锁,事务A只做普通查询)
① 事务A查询表记录
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小明 | 18 |
| 2 | janus | 18 |
| 3 | 明天 | 18 |
| 4 | januie | 18 |
| 5 | jane1 | 18 |
| 8 | jane1 | 18 |
| 9 | jane00 | 18 |
| 10 | januie | 18 |
+----+--------+------+
8 rows in set (0.00 sec)
8条记录。
② 事务B插入id为6的记录并提交
start TRANSACTION;
insert into t_user(id,name,age)VALUES(6,'jane1',18);
SELECT * from t_user;
COMMIT;
此时数据库实际有9条数据。
③ 事务A再次查询数据表记录
mysql> select * from t_user;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小明 | 18 |
| 2 | janus | 18 |
| 3 | 明天 | 18 |
| 4 | januie | 18 |
| 5 | jane1 | 18 |
| 8 | jane1 | 18 |
| 9 | jane00 | 18 |
| 10 | januie | 18 |
+----+--------+------+
8 rows in set (0.00 sec)
嗯,很好的保证了可重复读,还是8条数据。
④ 事务A提交事务后再次查询表记录
mysql> select * from t_user;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 小明 | 18 |
| 2 | janus | 18 |
| 3 | 明天 | 18 |
| 4 | januie | 18 |
| 5 | jane1 | 18 |
| 6 | jane1 | 18 |
| 8 | jane1 | 18 |
| 9 | jane00 | 18 |
| 10 | januie | 18 |
+----+--------+------+
9 rows in set (0.00 sec)
(ÒωÓױ),数据库有9条啊,事务A刚才看的不是最新数据,是历史数据!!!