Chat about MySQL's InnoDB storage Engine (ii)

Chat about MySQL InnoDB storage engine (i)

In the previous article, we discussed the locking mechanism and the isolation mechanism of the INNODB. This article is going to work with you on the problems that arise when using INNODB and how to solve them.

InnoDB is how to solve the problem of phantom reading

What is Phantom reading? Sounds like a high-end, but in fact it just reflects a data inconsistency in the transaction. Now, let me describe a scene in which you can clearly see what it means to read the illusion.

Open two clients, set to A and B

A client

mysql> start transaction; (Step One)

Query OK, 0 rows Affected (0.00 sec)

Mysql> SELECT * from B; (Step Two)

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

| 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; (step VI)

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

| 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 (+, ' abc '); (Step Seven)

ERROR 1062 (23000): Duplicate entry ' for key ' PRIMARY '

B Client

mysql> start transaction; (Step three)

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into B (id,name) VALUES (+, ' abc '); (Step Four)

Query OK, 1 row Affected (0.00 sec)

Mysql> commit; (Step Five)

Query OK, 0 rows affected (0.01 sec)

= = What? Clearly step six query results do not have a value of 100 ID, why insert when prompted to repeat key? The ID of 100 is "out of thin air" in transaction A, a phenomenon called Phantom reading.

Because the Repeatable-read isolation level is consistent read by referencing a point-in-time snapshot of the first query, transaction a still displays the old version of the result set to maintain data consistency when transaction B commits the insert data. And that happens to be the cause of the Phantom reading.

So, how to solve the phantom reading problem? To solve the Phantom read problem, you need to display the add lock in the query transaction, such as using the SELECT * from B for Update declaration to get the latest data. But this contradicts the nature of consistent reading. So the concrete problem uses what method solves to be specific analysis.

Detection and rollback of deadlocks

InnoDB can automatically detect transaction deadlocks, and when a deadlock is detected, InnoDB can automatically roll back small transactions. The size of the transaction is determined by the number of rows inserted, updated, or deleted.

If the Innodb_table_locks parameter is not set or the transaction is set to Automatic, then InnoDB will not be able to detect deadlocks. If MySQL uses lock tables to declare or lock with other storage engine, MySQL cannot detect deadlocks.

When InnoDB performs a rollback of a transaction, all locks set by the transaction are freed. Returns an error if an SQL declaration is executed. Then the lock set by the declaration will not be freed.

How to avoid deadlocks

1. If there is a frequent deadlock warning, you can turn on the innodb_print_all_deadlocks option to view the details of the deadlock in error log.

2, try to use small business, this can reduce the probability of conflict.

3. If you use SELECT ... For update similar declarations, try to use a lower isolation level, such as read-committed.

4, grasp the operation sequence within the transaction, this can effectively prevent deadlock.

5, optimize the index, so that in the query can scan fewer index records, to the less index lock.

This article is from the architect's path blog, so be sure to keep this source http://wangweiak47.blog.51cto.com/2337362/1591765

Chat about MySQL's InnoDB storage Engine (ii)