MySQL事务处理与并发控制( 七 ) _MySQL

5.2 可重复读隔离级别不存在幻读， mysql通过间隙锁解决了幻读问题， sql标准在这个隔离级别下允许幻读。

文章插图
可重复读
client2的事务在更新的时候会加锁， client1的不加锁：
select * from data_locks;+--------+-------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |+--------+-------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+| INNODB | 18262831:7872 | 18262831 | 50 | 24 | test | bluesea | NULL | NULL | NULL | 139693811345432 | TABLE | IX | GRANTED | NULL || INNODB | 18262831:6815:4:2 | 18262831 | 50 | 25 | test | bluesea | NULL | NULL | PRIMARY | 139693811342392 | RECORD | X | GRANTED | 1 |+--------+-------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+2 rows in set (0.00 sec)5.3 已提交读隔离级别存在幻读，不可重复读

文章插图
已提交读
5.4 未提交读隔离级别存在脏读

文章插图
未提交读
5.5 写偏序再讨论写偏序一般遵循如下模式：

1 一个 SELECT 查询找出符合条件的行，并检查是否符合一些要求。（例如：至少有两名医生在值班）。
2 按照第一个查询的结果，应用代码决定是否继续。（可能会继续操作，也可能中止并报错）
3 如果应用决定继续操作，就执行写入（插入、更新或删除），并提交事务。

这个写入的效果改变了步骤2 中的先决条件。换句话说，如果在提交写入后，重复执行一次步骤1 的SELECT查询，将会得到不同的结果。因为写入改变了符合搜索条件的行集（现在少了一个医生值班）
MySQL的可重复读避免不了写偏序，为了避免写偏序，需要使用可串行化隔离级别或者在可重复读隔离级别下使用(select ... for update)：
BEGIN TRANSACTION;SELECT count(*) FROM doctors WHERE on_call = TRUE AND shift_id = 1234 FOR UPDATE;UPDATE doctors SET on_call = FALSE WHERE name = 'Alice' AND shift_id = 1234;COMMIT;5.5.1 写偏序异常举例首先构造数据：
我们这里举出的例子还是接着之前的医生值班的例子。
CREATE TABLE `docts` ( `id` int(11) NOT NULL, `name` varchar(16) DEFAULT NULL, `status` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; insert into docts values(1, 'alice', 1); insert into docts values(2, 'bob', 1); insert into docts values(3, 'test', 0);mysql> select * from docts;+----+-------+--------+| id | name | status |+----+-------+--------+| 1 | alice | 1 || 2 | bob | 1 || 3 | test | 0 |+----+-------+--------+3 rows in set (0.00 sec)select @@transaction_isolation,@@global.transaction_isolation;+-------------------------+--------------------------------+| @@transaction_isolation | @@global.transaction_isolation |+-------------------------+--------------------------------+| REPEATABLE-READ | REPEATABLE-READ |+-------------------------+--------------------------------+status == 1表示在值班， 0表示未值班
接下来看看在可重复读下写偏序的发生：

文章插图
写偏序
set @v1=(select count(*) from docts where `status`=1 );的加锁情况mysql> select THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, LOCK_MODE,LOCK_STATUS from data_locks;+-----------+---------------+-------------+-----------+-------------+| THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | LOCK_MODE | LOCK_STATUS |+-----------+---------------+-------------+-----------+-------------+| 49 | test | docts | IS | GRANTED || 49 | test | docts | S | GRANTED || 49 | test | docts | S | GRANTED || 49 | test | docts | S | GRANTED || 49 | test | docts | S | GRANTED |+-----------+---------------+-------------+-----------+-------------+5 rows in set (0.00 sec)5.5.2 写偏序异常解决