Suppose I have a table with NO indexes:
create table foo (a int, b int, c int) engine=InnoDB;
and then I run:
update foo set c=5 where b=1; <-- updates one row
Because there is no index, InnoDB must read the entire table to find
the single row to update.
Here is my question: what type of lock is grabbed for each row read?
Is it a shared (read) lock or an exclusive (write) lock? I know the
row that is modified gets a write lock, but what about the rest of the
rows and the gaps in between the rows that are read but not modified?
Are those exclusive locks or shared locks?
Also, I assume the behavior is similar with respect to deletes?