MySQL использует для InnoDB таблиц для поддержки одновременного доступа для записи несколькими сессиями, что делает их подходящими для многопользовательских, высококонкурентных и OLTP-приложений.

Для избежания при выполнении нескольких одновременных операций записи над одной InnoDB таблицей, необходимо получить необходимые блокировки в начале транзакции, выпустив SELECT ... FOR UPDATE оператор для каждой группы строк, ожидаемых для изменения, даже если операторы изменения данных появляются позже в транзакции. Если транзакции изменяют или блокируют более одной таблицы, выпустите соответствующие операторы в одном и том же порядке внутри каждой транзакции. Тупики влияют на производительность, а не представляют собой серьезную ошибку, так как InnoDB автоматически условия тупика и откатывает одну из затронутых транзакций.

В высококонкурентных системах обнаружение тупиков может привести к замедлению, когда множество потоков ожидают одну и ту же блокировку. Иногда может быть эффективнее отключить обнаружение тупиков и положиться на настройку innodb_lock_wait_timeout для отката транзакции при возникновении тупика. Обнаружение тупиков можно отключить с помощью параметра конфигурации innodb_deadlock_detect.

Преимущества блокировки на уровне строк:

MySQL использует для MyISAM, MEMORY и MERGE таблиц, разрешая только одной сессии обновлять эти таблицы за раз. Этот уровень блокировки делает эти движки хранения более подходящими для приложений только для чтения, преимущественно для чтения или однопользовательских приложений.

Эти движки хранения избегают путем запроса всех необходимых блокировок сразу в начале запроса и всегда блокировки таблиц в одном и том же порядке. Недостатком является то, что эта стратегия снижает конкуренцию; другие сессии, которые хотят изменить таблицу, должны ждать, пока текущий оператор изменения данных не завершится.

Преимущества блокировки на уровне таблиц:

MySQL предоставляет блокировки записи таблиц следующим образом:

MySQL предоставляет блокировки чтения таблиц следующим образом:

Обновления таблиц имеют более высокий приоритет, чем извлечение данных из таблицы. Поэтому при освобождении блокировки блокировка становится доступной для запросов в очереди блокировки записи, а затем для запросов в очереди блокировки чтения. Это гарантирует, что обновления таблицы не “голодают” даже при большой активности SELECT для таблицы. Однако, если для таблицы много обновлений, SELECT операторы ожидают, пока не останется обновлений.

Сведения об изменении приоритета чтений и записей см. в Разделе 8.11.2, «Вопросы блокировки таблиц».

Вы можете проанализировать конфликт блокировки таблиц в вашей системе, проверив переменные состояния сервера Table_locks_immediate и Table_locks_waited, которые указывают соответственно на количество раз, когда запросы блокировок таблиц могли быть предоставлены немедленно, и количество, которое должно было ждать:

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

Схема производительности также предоставляет информацию о блокировках. См. Раздел 25.12.12, «Таблицы блокировок схемы производительности».

Движок хранения MyISAM поддерживает одновременные вставки для уменьшения конкуренции между читателями и писателями для данной таблицы: Если в MyISAM таблице нет свободных блоков посередине файла данных, строки всегда вставляются в конец файла данных. В этом случае вы можете свободно смешивать одновременные INSERT и SELECT операторы для MyISAM таблицы без блокировок. То есть вы можете вставлять строки в MyISAM таблицу в то же время, когда другие клиенты считывают данные из нее. Пропуски могут возникнуть из-за строк, удаленных или обновленных в середине таблицы. Если есть пропуски, одновременные вставки отключены, но снова включаются автоматически, когда все пропуски заполняются новыми данными. Для управления этим поведением используйте системную переменную concurrent_insert. См. Раздел 8.11.3, «Одновременные вставки».

Если вы явно получите блокировку таблицы с помощью LOCK TABLES, вы можете запросить READ LOCAL блокировку, а не READ блокировку, чтобы разрешить другим сессиям выполнять одновременные вставки, пока вы блокируете таблицу.

Для выполнения многих INSERT и SELECT операций над таблицей t1, когда одновременные вставки невозможны, вы можете вставлять строки в временную таблицу temp_t1 и обновлять реальную таблицу строками из временной таблицы:

mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;
mysql> INSERT INTO t1 SELECT * FROM temp_t1;
mysql> DELETE FROM temp_t1;
mysql> UNLOCK TABLES;

Как правило, блокировки таблиц превосходят блокировки на уровне строк в следующих случаях:

С более высокими уровнями блокировок вы можете легче настроить приложения, поддерживая блокировки разных типов, поскольку накладные расходы на блокировку меньше, чем при блокировках на уровне строк.

Варианты помимо блокировки на уровне строк: