Re: reproduce select statement blocking in InnoDB

[Nishant Deshpande]

ok thanks.

i have another query perhaps someone can help with.

i often do queries like

insert into foo
select ...

in this case, i see that i do need to acquire some locks.
from the documentation
"""
INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive (non-next-key) lock on each row inserted into T. InnoDB sets shared next-key locks on S, unless innodb_locks_unsafe_for_binlog is enabled, in which case it does the search on S as a consistent read. InnoDB has to set locks in the former case: In roll-forward recovery from a backup, every SQL statement has to be executed in exactly the same way it was done originally.
"""

most of my queries of the above form are for extracting reporting data, and i am perfectly happy with a consistent read, although i cannot set 'innodb_locks_unsafe_for_binlog' on the whole server.

is there a suggested way to not set the 'innodb_locks_unsafe_for_binlog' option but tell the insert into ... select * from.. query i just want a consistent read? or some other way of being more specific about the locks i need per query/connection.