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 …
[Read more]