O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Locking queue

No two locks can be held together on a table except shared locks (a table can have multiple shared locks). If a table already has a shared lock and an exclusive lock comes, it will be kept in a queue until the shared lock is released. When an exclusive lock is in a queue, all subsequent shared locks are also blocked and kept in a queue.

InnoDB acquires a metadata lock when reading/writing from a table. If a second transaction requests WRITE LOCK, it will be kept in a queue until the first transaction completes. If a third transaction wants to read the data, it has to wait until the second transaction completes.

Transaction 1:

mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM employees LIMIT 10;+--------+------------+------------+-----------+--------+------------+ ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required