Row and table locks for the InnoDB storage engine in MySQL

MySQL's InnoDB storage engine supports transactions, which by default are row locks. Because of this feature, the database supports high concurrency, but if the InnoDB update data is not a row lock, but a table lock, then its concurrency will be greatly compromised, but also may cause your program error.

One of the things that causes row locks to become table locks is:

An index is not used in a SQL update or DELETE statement, causing the InnoDB to lock up the entire table for retrieval (table lock) when the data is being manipulated accordingly. If an index is used, the INNODB only retrieves the data by index criteria, and only the row (row lock) corresponding to the index is locked.

The following is a note of the problems I encountered:

　　Describe:

1. The system has a real-time scheduled task, when the conditional trigger, will update the corresponding table, first called it a table;
2. But at the same time there is a task that sometimes has a write operation on the a table, so when testing, sometimes it is not fixed "Lock wait timeout exceeded" error.

When this problem occurs, it is analyzed from a number of places, and then the correct solution is not available (because the Description 1 module is not written by me, so I did not go to see the Update table code operation)

　　Cause:

When updating table A in the schedule task in Description 1, the index is not used in the update condition, resulting in a table lock when the table is updated for the scheduled task. Then, because table A has a large amount of data, the retrieval is slower, and then causes the lock timeout of the write operation for table A in the description 2.

The most common indexes are:

• Primary key: Well-known, with the most efficient index properties
• Unique Index: Property value repetition rate is 0 and can be used as a business primary key
• Normal Index: Property value repetition rate greater than 0, cannot be specified as a unique condition

Note: For normal indexes, when the "repetition rate" is low , even when the effect is close to the primary key or the unique index, it is still a row lock, but if the "repetition rate" is high , MySQL does not take this normal index as an index. A table lock is created by creating a SQL that is not indexed.

Row and table locks for the InnoDB storage engine in MySQL