细聊MySQL的Innodb存储引擎（二）

细聊MySQL的Innodb存储引擎（一）

        上一篇主要和大家探讨了下Innodb的锁机制与隔离机制。本篇来和大家一起研究下在使用Innodb是会出现的问题以及如何解决它们。

Innodb是如何解决幻读问题的

什么是幻读？听起来似乎很高端，但实际上它只是反映了事务中的一种数据不一致的情况。下面看我来描述这样一个场景，通过这个场景，大家就能很清楚的知道幻读到底是什么意思。

打开两个客户端，设为A和B

A客户端

mysql> start transaction; （步骤一）

Query OK, 0 rows affected (0.00 sec)

mysql> select * from b; （步骤二）

+----+------+

| id | name |

+----+------+

| 10 | abd  |

| 99 | NULL |

|  1 | wang |

|  7 | eeee |

|  2 | wei  |

|  3 | ak47 |

|  9 | ffff |

+----+------+

7 rows in set (0.00 sec)

mysql> mysql> select * from b;（步骤六）

+----+------+

| id | name |

+----+------+

| 10 | abd  |

| 99 | NULL |

|  1 | wang |

|  7 | eeee |

|  2 | wei  |

|  3 | ak47 |

|  9 | ffff |

+----+------+

7 rows in set (0.00 sec)

mysql> insert into b (id,name) values (100,‘abc’); （步骤七）

ERROR 1062 (23000): Duplicate entry ‘100‘ for key ‘PRIMARY‘

B客户端

mysql> start transaction; （步骤三）

Query OK, 0 rows affected (0.00 sec)

mysql> insert into b (id,name) values (100,‘abc’); （步骤四）

Query OK, 1 row affected (0.00 sec)

mysql> commit; （步骤五）

Query OK, 0 rows affected (0.01 sec)

＝。＝ what? 明明步骤六查询结果没有值为100的id，为啥插入时提示重复key呢？这个值为100的id在事务A中“凭空”的产生了，这种现象就称之为幻读。

由于REPEATABLE-READ隔离级别是参照第一次查询的时间点快照来保持一致性读的，所以当事务B提交插入数据后，事务A仍然显示旧版本的结果集来保持数据的一致性。而这样恰好就成为了引起幻读的原因。

那么，如何解决幻读问题呢？要解决幻读问题，需要在查询事务中显示添加锁，如使用select * from b for update声明来获取最新的数据。但这样就与一致性读的性质相矛盾了。所以具体问题使用什么办法解决要具体分析。

死锁的侦测与回滚

Innodb能自动侦测事务死锁，当侦测到死锁时，Innodb能自动回滚小事务。事务的大小由插入、更新或删除的行数决定。

如果没有设置Innodb_table_locks参数或者将事务设置成自动，那么Innodb将不能侦测死锁。如果MySQL使用LOCK TABLES声明或使用其它的存储引擎加锁，MySQL也不能侦测死锁。

当Innodb执行一个事务回滚后，所有被该事务设置的锁都将被释放。如果一个SQL声明执行后返回错误。那么被该声明设置的锁将不被释放。

如何避免死锁

1、如果频繁的出现死锁警告，可以开启innodb_print_all_deadlocks选项，查看在error log里的关于死锁的详细信息。

2、尽量使用小事务，这样可以减少冲突的概率。

3、如果你使用SELECT...FOR UPDATE类似声明，尽量使用更低的隔离级别，比如READ-COMMITTED。

4、掌握好事务内的操作顺序，这样可以有效防止死锁。

5、优化索引，这样在查询时能扫描更少的索引记录，对更少的索引加锁。

本文出自 “架构师之路” 博客，请务必保留此出处http://wangweiak47.blog.51cto.com/2337362/1591765