mysql locks relation to indexes

Question

I am trying to understand the relation between locks and indexes.

I have the following table

CREATE TABLE example_table (
    id INT AUTO_INCREMENT,
    col1 VARCHAR(255),
    col2 INT,
    PRIMARY KEY (id)
);
INSERT INTO example_table (col1, col2) VALUES ('a',1); 
INSERT INTO example_table (col1, col2) VALUES ('b',2); 
INSERT INTO example_table (col1, col2) VALUES ('c',3); 
INSERT INTO example_table (col1, col2) VALUES ('d',4); 

when im running the following queries on 2 separate transactions im getting

SELECT * FROM example_table where col1="a" FOR UPDATE;

and

SELECT * FROM example_table where col1="b" FOR UPDATE;

and when running the 2nd query, it waits for the 1st to release the exclusive lock.

but if I add an index to col1, running these 2 slecet queries do not wait for each other's lock.

my question is: why when querying select ... for update on non indexed query the who table is locked and when querying select ... for update on indexed column only the required rows are locked?

Answer 1

SELECT ... FOR UPDATE locks every row that it examines, not just the ones that are selected.

When there's no index on col1, it has to examine every row to see if the condition is true, so the whole table is locked.

When there's an index on col1, it can use that index to find the rows that match the condition, and only those rows will be locked.