MySQL Forums
Forum List  »  InnoDB

Re: reproduce select statement blocking in InnoDB
Posted by: Nishant Deshpande
Date: September 08, 2008 05:27PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: reproduce select statement blocking in InnoDB
2009
September 08, 2008 05:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.