Why does MySQL not lock rows while searching indexed column

For example, I lock some rows:

select * from t1 where c2 = 1 for update;

c2 is not indexed. It means MySQL has to search entire table and if it read uncommitted or read committed isolation levels it places locks on every scanned row and if it doesn’t satisfy the WHERE condition it immediately release the lock.

If it is repeatable read those locks that do not satisfy the WHERE condition remain until the end of the transaction.

When MySQL searchs indexed column for some reason it doesn’t place locks on rows that do not satisfy the WHERE condition. Yes, it uses another algorithm which allows it to find the row in 3-4 fetches but it still touches some rows before it find correct one.

Answer

Actually, the real problem isn’t why MySQL doesn’t lock rows while using an index, but why it locks those rows while not using an index.

Simplifying a bit (and depending on your isolation level), there are two things that shall be prevented by the locks issued for your query:

• a row with c=1 should not be changed by a different transaction, e.g. to c=2, as it would no longer fulfill your original condition

• a row with c=2 should not be changed to c=1 (and no new row with c=1 should be inserted), as it would now fulfill your original condition (so it would have been selected by your query if the other transaction had come first)

Locking for the first bulletpoint basically just requires locking of rows with c=1. There is no fundamental difference between the indexed and the unindexed case: rows that currently have c=1 will end up being locked.

For the second bulletpoint however, that is trickier:

For the unindexed case, unfortunately, MySQL has no way to distinguish if another transaction changes a row with c=2 to c=5 (which would be fine) or a row with c=2 to c=1 (which has to be prevented). Since it’s better to lock too many rows than not enough, MySQL will just do that: lock all rows. This makes sure it prevents the modification c=2 to c=1. The locks on the other rows are collateral, the price you have to pay for the greater good (or for not adding an index).

MySQL will keep this “overprotecting behaviour” up: to prevent an insert of a new row with c=1, it will basically prevent any insert (into the primary key) by, well, locking all rows (and gaps, which I am not going into detail here).

For the indexed case, MySQL has another option: if a modification wants to change a row with c=2 to c=1, it would require that the row gets a new entry in the c=1 area of the index. So MySQL can “just” place a lock there to prevent injections (inserts or updates) into that index at c=1. Basically, it doesn’t have to pre-emptively lock all rows just to be safe, but can detect unallowed modifications in hindsight.