Locking is an important concept in databases. They help regulate
access to the data, so your SELECT queries return
consistent results and DML and DDL statements leave the data and
schema in a consistent state. For the data, there are four
different transaction isolation levels that influence which locks
are taken. The most two commonly used isolation levels are
REPEATABLE READ (the default in InnoDB) and
READ COMMITTED (the default in some other
databases). Both of those are said to provide non-locking reads,
but there is a little more to it than that.
Selecting into a user variable causing a lock wait timeout.
One case where reads are always locking is when you explicitly
requests locks by adding the FOR SHARE or FOR
UPDATE modifiers. However there are also cases where
SELECT statements becomes locking due to the way the
result of the statement is used. …