How to know which rows were locked by SELECT ... FOR UPDATE in MySQL?

Question

The SELECT ... FOR UPDATE statement locks rows, but how can you know which (if any) rows were locked? It does not seem to allow selecting records into variables or temporary tables.

Answer 1

All rows examined by the query are locked.

So you could fetch the results of the SELECT, and at least those rows are locked.

But it could be that more rows are locked. It's really all rows matched by indexes, even if the rows are ultimately not returned by the SELECT.

For example:

SELECT * FROM MyTable WHERE a = 123 AND b = 456 FOR UPDATE;

Suppose column a is indexed, but b is not part of the index. This query would lock all the rows matching a = 123, even those that do not match the second condition.

This makes it difficult to know exactly which rows are locked by a given query, because the answer might be different depending on which index is chosen by the optimizer.

You can even get into a situation where all the rows in the table are locked, because the optimizer chose to do a table-scan instead of using any index (it might do this because you searched for a value that the optimizer knows occurs so commonly in the indexed column that it just skips the index).

I don't know of any way to get a report of which rows are locked in your current transaction. Your best plan is just to finish the transaction promptly and that will release all locks.