From the MySQL Documentation
The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE:
By default, InnoDB uses stronger locks and the SELECT part acts like READ COMMITTED, where each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
To use a consistent read in such cases, enable the innodb_locks_unsafe_for_binlog option and set the isolation level of the transaction to READ UNCOMMITTED, READ COMMITTED, or REPEATABLE READ (that is, anything other than SERIALIZABLE). In this case, no locks are set on rows read from the selected table.
Evidently, you do not need stronger locks when just doing reads. Picture copying data into a table using INSERT ... SELECT or CREATE TABLE ... SELECT. The SELECT needs to be a frozen snapshot for loading the table. If it were a moving target, that would require a transaction within a transaction. If you wanted transaction within a transaction behavior, you would need to script that using SAVEPOINT. Otherwise, stronger locks allow for consistent SELECTs for an atomic INSERT.
Doing the simple SELECT allows your DB Session to trust other transactions not to play smoke-and-mirrors with your view of the data. If it has to violate that trust, it will politely lock every row it can to do its work, rather than deceive DB Sessions that are only doing SELECTs into thinking the data is stable when it is, in fact, changing.
No matter which isolation level you pick, none of them is so granular that a simple SELECT gets held up. That's why SELECT ... FOR UPDATE and SELECT ... FROM ... LOCK IN SHARE MODE were invented, so you can be that granular if politeless is not an option.
I wrote about this before : How can I make a select statement get blocked?
select t.table_schema,t.engine,t.table_name,c.column_name,c.column_type
from information_schema.tables t
inner join information_schema.columns c on c.table_schema = t.table_schema and c.table_name = t.table_name
left outer join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,'/',t.table_name)
left outer join information_schema.innodb_sys_columns isc on isc.table_id = ist.table_id and isc.name = c.column_name
where c.column_type in ('time','timestamp','datetime')
and t.table_schema not in ('mysql','information_schema','performance_schema')
and t.table_type = 'base table'
and (t.engine != 'innodb' or (t.engine = 'innodb' and isc.mtype = 6))
order by t.table_schema,t.table_name,c.column_name;
http://mechanics.flite.com/blog/2014/05/01/upgrading-temporal-columns-from-mysql-5-dot-5-to-mysql-5-dot-6-format/
Best Answer
The default value has been
ON, since 5.6.6.You're looking at the 5.5 documentation, which only refers to the defaults in 5.5 -- not 5.7. In version 5.5.7, a decision was made to revert the defaults to match 5.1 in an effort to avoid a replication incompatibility.
In 5.6.6 (before 5.6 GA) the default value changed back to
ON.In 5.7, the default is still
ON.