Hello, I’m Xiao Lin.
Last night while we were paddling, a reader said this.
Probably is, execute an online update statement to modify the database data, didn’t take the where condition index, lead to business directly collapsed, taught a wave by the boss
Let’s take a look this time:
- Why did this happen?
- And how to avoid such accidents?
The following cases are all based on the InnoDB storage engine and the transaction isolation level is repeatable.
Why did this happen?
The InnoDB storage engine’s default transaction isolation level is “repeatable read”, but under the isolation level, in a number of transaction concurrency, there will be a problem, phantom read so-called phantom read means under the same transaction, two consecutive years of the same query statement, the second existed before the query might return line.
So the InnoDB storage engine implements row locking itself, using a next-key lock (a combination of record locking and gap locking) to lock the “gap” between the record itself and the record, preventing other transactions from inserting new records between the record and avoiding phantom reading.
When we execute an UPDATE statement, we actually place an exclusive lock (X lock) on the record and block if other transactions modify the record that holds the exclusive lock. In addition, the lock is not released immediately after the update statement is executed, but only when the transaction ends.
In InnoDB transactions, the basic unit of record locking is next-key locking, but it can degenerate into gap locking or record locking due to certain conditions. To be precise, the lock is placed on the index, not on the row.
For example, if a unique index is used in the WHERE condition of an UPDATE statement, the next-key lock degrades to a record lock, where only one row of records is locked.
As an example, here is a database table with id as the primary key index.
Suppose there are two transactions executed in the following order:
Update (id = 1); update (id = 1); update (id = 1);
However, if no index is used in the WHERE condition of the UPDATE statement, a full table scan is performed, and a next-key lock (record lock + gap lock) is applied to all records, which locks the entire table.
Suppose there are two transactions executed in the following order:
As you can see, the UPDATE statement for transaction B is blocked.
SQL > alter table UPDATE (WHERE); SQL > alter table UPDATE (where);
Therefore, when an UPDATE statement is executed on a large database table, if no index is used, a next-key lock will be placed on the entire table, and the lock will last for a long time until the end of the transaction, except for select… The from statement, the other statements will be locked and cannot be executed, the business will stop, and the next thing you know, the boss will scold you.
Update where with index can avoid full table lock?
And it isn’t.
The key depends on whether the optimizer ultimately selects an index scan or a full table scan, which locks all table records.
And how to avoid such accidents?
Set the sql_safe_updates parameter to 1 to enable security update mode.
The 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.
When SQL_SAFE_updates is set to 1.
The UPDATE statement must meet one of the following conditions to be successfully executed:
- Use where, and the where condition must have indexed columns;
- Use the limit;
- If you use both WHERE and limit, there can be no index columns in the WHERE condition.
The DELETE statement must meet one of the following conditions to be successfully executed:
- Use where, and the where condition must have indexed columns;
- If you use both WHERE and limit, there can be no index columns in the WHERE condition.
Force index([index_name]) can be used to tell the optimizer which index to use if the where condition contains index columns, but the optimizer eventually scans for the full table instead of the index.
conclusion
Do not underestimate an UPDATE statement. Improper use of an UPDATE statement on a production machine can cause business to stall or even crash.
When we execute an UPDATE statement, make sure that the WHERE condition contains the index column and the test machine verifies that the statement does an index scan to prevent all records in the table from being locked because the entire table is scanned.
We can turn on the MySQL SQL_SAFE_updates parameter in case the UPDATE operation does not have an indexed column in the WHERE condition.
Force index([index_name]) is used to tell the optimizer which index to use if the optimizer does a full index scan even though the WHERE condition contains the index column.
That’s all for now. Be careful next time. Don’t be scolded by the boss again.