Update has no index and the business collapsed. The boss scolded for an hour

Abstract: One day, when a friend executed an update statement online to modify the database data, the where condition did not include an index, which caused the business to collapse directly, and was taught by the boss.

This article is shared from the HUAWEI CLOUD community " update has no index, it will lock the entire table! ", author: Kobayashi coding.

One day, when a friend executed an update statement online to modify the database data, the where condition did not include an index, which caused the business to collapse directly, and was taught a lesson by the boss.

This time we will take a look:

• Why does this kind of accident happen?
• How to avoid this kind of accident?

As a prerequisite, the following cases are based on the InnoDB storage engine, and the transaction isolation level is repeatable read.

Why does this kind of accident happen?

The default transaction isolation level of the InnoDB storage engine is "repeatable read", but under this isolation level, when multiple transactions are concurrent, there will be a problem of phantom reading. The so-called phantom read refers to continuous execution under the same transaction The same query statement twice, the second query statement may return rows that did not exist before.

Therefore, the InnoDB storage engine implements row locks by itself, using next-key locks (a combination of record locks and gap locks) to lock the "gap" between the record itself and the record, preventing other transactions from inserting new records between this record , Thereby avoiding the phenomenon of phantom reading.

When we execute the update statement, an exclusive lock (X lock) is actually added to the record. If other transactions modify the record holding the exclusive lock, it will be blocked. In addition, this lock is not released after the update statement is executed, but will not be released until the end of the transaction.

In an InnoDB transaction, the basic unit of record locking is the next-key lock, but it will degenerate into a gap lock or record lock due to some conditions. The location of the lock is to be precise, the lock is added to the index rather than the row.

For example, if a unique index is used in the where condition of the update statement, the next-key lock will degenerate into a record lock, that is, only one row of records will be locked.

Here is an example, here is a database table, where id is the primary key index.

Assume that the order of execution of two transactions is as follows:

As you can see, where in the update statement of transaction A is an equivalent query, and id is a unique index, so only the record with id = 1 will be locked. Therefore, the update operation of transaction B will not block.

However, if does not use an index in the where condition of the update statement, the full table will be scanned, and then the next-key lock (record lock + gap lock) will be added to all records, which is equivalent to locking the entire table.

Assume that the order of execution of two transactions is as follows:

As you can see, this time the update statement of transaction B is blocked.

This is because the where condition in the update statement of transaction A does not have an index column, and all records will be locked, that is, this update statement generates 4 record locks and 5 gap locks, which is equivalent to locking the entire table.

Therefore, when the update statement is executed on a database table with a very large amount of data, if the index is not used, the next-key lock will be added to the entire table, and the lock will continue for a long time until the end of the transaction, and this Except for the select ... from statement, all other statements will be locked and cannot be executed, and the business will be stagnated. The next thing waiting for you is the scolding of the boss.

Then, with the index in the update statement, can the entire table record be locked?

Not really.

key to 161494aba81d7b depends on the execution of this statement. The optimizer finally chooses an index scan or a full table scan. If a full table scan is taken, the records of the entire table will be locked.

How to avoid this kind of accident?

We can set the sql_safe_updates parameter in MySQL to 1 to turn on the safe update mode.

Official explanation:
If set to 1, MySQL aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. (Specifically, UPDATE statements must have a WHERE clause that uses a key or a LIMIT clause, or both. DELETE statements must have both.) This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows. The default value is 0.

The rough meaning is when sql_safe_updates is set to 1.

The update statement must meet one of the following conditions to execute successfully:

• Use where, and there must be an index column in the where condition;
• Use limit;
• Use where and limit at the same time, at this time, there can be no index column in the where condition;

The delete statement must meet one of the following conditions to execute successfully:

• Use where, and there must be an index column in the where condition;
• Use where and limit at the same time, at this time, there can be no index column in the where condition;

If the where condition includes the index column, but the optimizer finally scans the whole table instead of the index, we can use force index([index_name]) to tell the optimizer which index to use, so as to avoid the possibility of locking the whole The hidden dangers brought by the watch.

Summarize

Don't underestimate an update statement. Improper use on a production machine may cause business stagnation or even crash.

When we want to execute the update statement, ensure that the index column is included in the where condition, and confirm whether the statement is an index scan on the test machine to prevent the entire table from being scanned and lock all records in the table.

We can turn on the MySQL sql_safe_updates parameter to prevent the where condition does not include the index column during the update operation.

If it is found that even if the column index column is included in the where condition, the optimizer still takes a full-standard scan, then we have to use force index([index_name]) to tell the optimizer which index to use.

That's it for this time. Be careful next time and don't get scolded by the boss anymore.

follow, and learn about Huawei Cloud's fresh technology for the first time~