mysql 如何解决幻像问题（Phantom Problem）

mysql 如何解决幻像问题（Phantom Problem）

在默认的事务隔离级别下，即REPEATABLEREAD下，InnoDB存储引擎采用Next-KeyLocking机制来避免PhantomProblem（幻像问题）。

这点可能不同于与其他的数据库，如Oracle数据库，因为其可能需要在SERIALIZABLE的事务隔离级别下才能解决PhantomProblem。

PhantomProblem是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

下面将演示这个例子，

表t create table (id ) 表t由1、2、5这三个值组成，

 CREATE TABLE `t` (`id` int(11) NOT NULL AUTO_INCREMENT,`a` int,PRIMARY KEY (`id`),key(a)
) ;
insert into t(a) values(1),(2),(5);

若这时事务T1执行如下的SQL语句：

SELECT* FROM t WHERE a＞2 FOR UPDATE;

注意这时事务T1并没有进行提交操作，上述应该返回5这个结果。

若与此同时，另一个事务T2插入了4这个值，并且数据库允许该操作，那么事务T1再次执行上述SQL语句会得到结果4和5。这与第一次得到的结果不同，违反了事务的隔离性，即当前事务性，即当前事务能够看到其他事务的结果。

时间	会话A	会话B
1	set sessi tx_isolation='read-committed';
2	begin;
3	select * from t where a>2 for update; 结果为a:5
4		begin;
5		insert into t(a) select 4;
6		commit
7	select * from t where a>2 for update; 结果为 a:4 和 a:5

InnoDB存储引擎采用Next-KeyLocking的算法避免PhantomProblem。对于上述的SQL语句

SELECT * FROM t WHERE a＞2 FOR UPDATE，

其锁住的不是5这单个值，而是对（2，+∞）这个范围加了X锁。因此任何对于这个范围的插入都是不被允许的，从而避免PhantomProblem。InnoDB存储引擎默认的事务隔离级别REPEATABLE READ，在该隔离级别下，其采用Next-KeyLocking的方式来加锁。而在事务隔离级别READCOMMITTED下，其仅采用RecordLock，因此在上述的示例中，会话A需要将事务的隔离级别设置为READCOMMITTED。此外，用户可以通过InnoDB存储引擎的Next-KeyLocking机制在应用层面实现唯一性的检查。例如：

SELECT* FROM table
WHERE col= xxx LOCK IN SHARE MODE；
#如果没有返回值 ，则证明该值并不存在，是可以插入的
INSERT INTO table VALUES(...);

如果用户通过索引查询一个值，并对该行加上一个SLock，那么即使查询的值不在，其锁定的也是一个范围，因此若没有返回任何行，那么新插入的值一定是唯一的。也许有读者会有疑问，如果在进行第一步SELECT…LOCKINSHAREMODE操作时，有多个事务并发操作，那么这种唯一性检查机制是否存在问题。其实并不会，因为这时会导致死锁，只有一个事务的插入操作会成功，而其余的事务会抛出死锁的错误。

MYSQL系列书籍

高可用mysql: =2651 (访问密码: 2651)

MySQL王者晋级之路.pdf: =2651 (访问密码: 2651)

MySQL技术内幕InnoDB存储引擎第2版.pdf: =2651 (访问密码: 2651)

MySQL技术内幕 第4版.pdf: =2651 (访问密码: 2651)

MySQL管理之道，性能调优，高可用与监控（第二版）.pdf: =2651 (访问密码: 2651)

深入浅出MySQL数据库开发、优化与管理维护第2版.pdf: =2651 (访问密码: 2651)

高性能MySQL.第3版.Baron Schwartz.pdf: =2651 (访问密码: 2651)

MYSQL内核：INNODB存储引擎 卷1.pdf: =2651 (访问密码: 2651)

MySQL技术内幕InnoDB存储引擎第2版.pdf: =2651 (访问密码: 2651)

MySQLDBA修炼之道.pdf: =2651 (访问密码: 2651)

MySQL5.7从入门到精通.pdf: =2651 (访问密码: 2651)

高可用mysql.pdf: =2651 (访问密码: 2651)

HIVE电子书

Practical Hive.pdf: =2651 (访问密码: 2651)

Hive-Succinctly.pdf: =2651 (访问密码: 2651)

Apache Hive Essentials.pdf: =2651 (访问密码: 2651)

Apache Hive Cookbook.pdf: =2651 (访问密码: 2651)

hadoop电子书

Practical Hadoop Migration.pdf: =2651 (访问密码: 2651)

Hadoop实战-陆嘉恒(高清完整版).pdf: =2651 (访问密码: 2651)

Hadoop & Spark大数据开发实战.pdf: =2651 (访问密码: 2651)

Expert Hadoop Administration.pdf: =2651 (访问密码: 2651)

Big Data Forensics - Learning Hadoop Investigations.pdf: =2651 (访问密码: 2651)

python电子书

python学习手册.pdf: =2651 (访问密码: 2651)

Python基础教程-第3版.pdf: =2651 (访问密码: 2651)

Python编程：从入门到实践.pdf: =2651 (访问密码: 2651)

Python Projects for Beginners.pdf: =2651 (访问密码: 2651)

kafka电子书

Learning Apache Kafka, 2nd Edition.pdf: =2651 (访问密码: 2651)

Kafka权威指南.pdf: =2651 (访问密码: 2651)

Kafka in Action.pdf: =2651 (访问密码: 2651)

Apache Kafka实战.pdf: =2651 (访问密码: 2651)

Apache Kafka Cookbook.pdf: =2651 (访问密码: 2651)

spark电子书

Spark最佳实践.pdf: =2651 (访问密码: 2651)

数据算法--Hadoop-Spark大数据处理技巧.pdf: =2651 (访问密码: 2651)

Spark大数据分析实战.pdf: =2651 (访问密码: 2651)

Spark 2.0 for Beginners.pdf: =2651 (访问密码: 2651)

Pro Spark Streaming.pdf: =2651 (访问密码: 2651)

Spark in Action.pdf: =2651 (访问密码: 2651)

Learn PySpark.pdf: =2651 (访问密码: 2651)

Fast Data Processing with Spark.pdf: =2651 (访问密码: 2651)

Fast Data Processing with Spark, 2nd Edition.pdf: =2651 (访问密码: 2651)

OReilly.Learning.Spark.2015.1.pdf: =2651 (访问密码: 2651)

High Performance Spark.pdf: =2651 (访问密码: 2651)

Machine Learning with PySpark.pdf: =2651 (访问密码: 2651)

Spark for Python Developers.pdf: =2651 (访问密码: 2651)

Spark Cookbook.pdf: =2651 (访问密码: 2651)

Big Data Analytics with Spark.pdf: =2651 (访问密码: 2651)

PySpark SQL Recipes.pdf: =2651 (访问密码: 2651)

Advanced Analytics with Spark Patterns for Learning from Data at Scale .pdf: =2651 (访问密码: 2651)

OReilly.Advanced.Analytics.with.Spark.Patterns.for.Learning.from.Data.at.Scale.pdf: =2651 (访问密码: 2651)

Big Data Analytics Beyond Hadoop_ Real-Time Applications with Storm, Spark, and More Hadoop Alternatives.pdf: =2651 (访问密码: 2651)