MySQL Indexes and locks

Indexes and locks allow queries to lock fewer rows. If your query never accesses rows that you do not need access to, it will lock in fewer rows, which in two respects is good for performance. First, although the InnoDB is highly efficient and uses little memory, locking the rows still brings additional overhead, and second, locking more rows than needed increases lock contention and reduces concurrency.

InnoDB locks the rows only when they are accessed, and the index reduces the number of locks by reducing the number of rows accessed by InnoDB. However, the InnoDB is only valid if the storage engine is able to filter out unneeded rows. If the index cannot filter out invalid rows, the MySQL server can apply a WHERE clause after INNODB retrieves the data and returns it to the server layer. At this point, there is no way to avoid locking the line: Inno represents the ability to release locks after filtering out the server side, but in earlier versions of MySQL, InnoDB can only release locks after a transaction has been committed.

The following example is a good explanation for these situations

SELECT actor_id from actor WHERE actor_id < 5 and actor_id<>1 for UPDATE;

These tables only return rows between 2-4, but actually get an exclusive lock between 1-4 rows, InnoDB locks the first row because MySQL chooses the execution plan for the query, which is the index range scan; in other words, the underlying storage engine operates " Get a record of the actor_id<5 that satisfies the condition starting at the beginning of the index "the server did not tell InnoDB to filter out the where condition of the first row. Note that the "using where" appears in the extra column of explain, which means that the MySQL server will store the engine back rows and then apply the Where filter condition.

The second query below proves that the first row is indeed locked, even though the first query does not have a single line in the result. Keep the first connection open, and then turn on the second connection and execute the following statement:

SELECT actor_id from actor WHERE actor_id = 1 for UPDATE.

The query is suspended until the first transaction releases the first row of locks. This behavior is necessary for the normal operation of statement-based replication.

As this example shows, even with an index, InnoDB may lock some unwanted data. If you can't use an index to find and lock rows, the problem can be bad, and MySQL does a full table scan and locks all rows, whether it's needed or not.

With regard to InnoDB, indexes and locks have few details to be known: InnoDB uses shared locks (read locks) on level two indexes, but access to primary key indexes requires an exclusive lock (write), which eliminates the possibility of using the overwrite index and makes the select for UPDATE more than the lock in SHARE MODE or non-locking queries are much slower.

　　

MySQL Indexes and locks