Различия

Здесь показаны различия между двумя версиями данной страницы.

--- mysql:locks_mysql [2023/09/06 21:39]
werwolf создано
+++ mysql:locks_mysql [2023/09/06 21:45] (текущий)
werwolf
@@ Строка 27: / Строка 27: @@
 Проанализировать конкуренцию за блокировку таблиц можно командой
-<code>
+<code sql>
 SHOW STATUS LIKE ‘Table%’.
 </code>
@@ Строка 57: / Строка 57: @@
 Пользовательскую блокировку можно получить с помощью функции GET_LOCK(**key**, **timeout**), где key — произвольный строковый ключ длиной до 64 символов, а timeout — таймаут в секундах. Отрицательное значение таймаута означает бесконечное время ожидания.
-<code>
+<code sql>
 SELECT GET_LOCK(‘key’, 10);
 </code>
@@ Строка 131: / Строка 131: @@
 Табличные блокировки бывают явные и неявные. Явная захватывается командой ''LOCK TABLES''. Синтаксис следующий:
-<code>
+<code sql>
 LOCK TABLES table_name1 {lock_type}, table_name2 {lock_type}...table_name3 {lock_type};
 </code>
@@ Строка 137: / Строка 137: @@
 где lock_type — тип блокировки.
-<code>
+<code sql>
 lock_type: {
   READ [LOCAL] | WRITE
@@ Строка 162: / Строка 162: @@
 Правильный способ сделать LOCK/UNLOCK TABLES при использовании транзакционных движков, таких как InnoDB, — это начинать транзакцию с SET autocommit = 0 (а не со START TRANSACTION), затем применить блокировку LOCK TABLES и не выполнять UNLOCK TABLES, пока транзакция не будет зафиксирована явно.
-<code>
+<code sql>
 SET autocommit = 0;
 LOCK TABLES drivers WRITE, clients READ;
@@ Строка 185: / Строка 185: @@
   * Вы не можете работать с таблицами, которые не перечислены в LOCK TABLES.
-<code>
+<code sql>
 LOCK TABLES users WRITE;
 INSERT INTO users (name) VALUES (‘Вася’); // успех
@@ Строка 199: / Строка 199: @@
   * Если вы используете псевдоним таблицы, то должны явно обращаться к этому псевдониму после LOCK TABLES.
-<code>
+<code sql>
 LOCK TABLES drivers as d WRITE;
 SELECT * FROM drivers;
@@ Строка 209: / Строка 209: @@
 То же самое правило относится и к псевдониму в выражениях после LOCK TABLES.
-<code>
+<code sql>
 LOCK TABLES drivers WRITE;
 SELECT * FROM drivers as d;
@@ Строка 272: / Строка 272: @@
 Пример 1:
-<code>
+<code sql>
 # Транзакция 1 (IS)
 SELECT id, name FROM drivers FOR SHARE;
@@ Строка 286: / Строка 286: @@
 Пример 2:
-<code>
+<code sql>
 # Транзакция 1 (IX)
 SELECT id, name FROM drivers FOR UPDATE;
@@ Строка 304: / Строка 304: @@
 Пример с NOWAIT.
-<code>
+<code sql>
 # Транзакция 1
 START TRANSACTION;
@@ Строка 317: / Строка 317: @@
 Пример со SKIP LOCKED.
-<code>
+<code sql>
 # Транзакция 1
 START TRANSACTION;
@@ Строка 339: / Строка 339: @@
 Пример 1:
-<code>
+<code sql>
 # Транзакция 1
 START TRANSACTION;
@@ Строка 353: / Строка 353: @@
 Пример 2:
-<code>
+<code sql>
 # Транзакция 1
 START TRANSACTION;
@@ Строка 367: / Строка 367: @@
 Пример 3:
-<code>
+<code sql>
 # Транзакция 1
 START TRANSACTION;
@@ Строка 386: / Строка 386: @@
   * SELECT … FOR SHARE и SELECT … FOR UPDATE не блокируют таблицы в подзапросах.
-<code>
+<code sql>
 # В этом примере строки из таблицы blocked_drivers не блокируются
@@ Строка 409: / Строка 409: @@
 Добавим индекс по колонке car_id в нашей таблице Drivers, и теперь запрос
-<code>
+<code sql>
 SELECT id, name
 FROM drivers
@@ Строка 430: / Строка 430: @@
 A. Между записями индекса:
-<code>
+<code sql>
 SELECT id, name, car_id
 FROM drivers
@@ Строка 439: / Строка 439: @@
 Если мы попытаемся в другой транзакции выбрать записи, входящие в этот промежуток, нам придется ждать снятия блокировки.
-<code>
+<code sql>
 SELECT id, name, car_id
 FROM drivers
@@ Строка 446: / Строка 446: @@
 </code>
-<code>
+<code sql>
 SHOW ENGINE INNODB STATUS;
 </code>
@@ Строка 454: / Строка 454: @@
 B. До записи индекса:
-<code>
+<code sql>
 SELECT id, name, car_id
 FROM drivers
@@ Строка 465: / Строка 465: @@
 C. После записи индекса:
-<code>
+<code sql>
 SELECT id, name, car_id
 FROM drivers
@@ Строка 496: / Строка 496: @@
 Для примера давайте добавим пару новых записей в нашу таблицу:
-<code>
+<code sql>
 INSERT INTO drivers (name, car_id, balance) VALUES (‘new1’, 14, 100);
 INSERT INTO drivers (name, car_id, balance) VALUES (‘new2’, 17, 100);
@@ Строка 503: / Строка 503: @@
 Теперь в первой транзакции сделаем запрос:
-<code>
+<code sql>
 START TRANSACTION;
 SELECT * FROM drivers WHERE car_id > 13 FOR UPDATE;
@@ Строка 510: / Строка 510: @@
 Вторая же транзакция пытается вставить новую запись в наш промежуток car_id > 13:
-<code>
+<code sql>
 INSERT INTO drivers (name, car_id, balance) VALUES (‘new2’, 15, 100);
 </code>
@@ Строка 516: / Строка 516: @@
 Команда ''SHOW ENGINE INNODB STATUS'' покажет нам следующую информацию:
-> RECORD LOCKS space id 2 page no 5 n bits 80 index drivers_car_id_index of table ''drivers'' trx id 3165 lock_mode X locks **gap before rec insert intention waiting** Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000011; asc     ;; 1: len 4; hex 8000000b; asc     ;;
+<code sql>
+RECORD LOCKS space id 2 page no 5 n bits 80 index drivers_car_id_index of table drivers trx id 3165 lock_mode X locks gap before rec insert intention waiting
+Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
+: len 4; hex 80000011; asc     ;;
+: len 4; hex 8000000b; asc     ;;
+</code>
 Однако вторая транзакция не будет блокировать другие намерения вставок в этот промежуток и они могут сосуществовать бесконфликтно.
@@ Строка 642: / Строка 650: @@
 Стоит добавить, что если ваши транзакции начали периодически отваливаться с ошибкой “Lock wait timeout exceeded”, значит ваше приложение столкнулось с проблемой конфликта за блокировку и/или долгосрочными блокировками. В этом случае может помочь увеличение времени ожидания снятия блокировки транзакцией с помощью изменения переменной innodb_lock_wait_timeout (по умолчанию 50 секунд).
-<code>
+<code sql>
 SET innodb_lock_wait_timeout = {количество_секунд}
 </code>
@@ Строка 652: / Строка 660: @@
 Для определения, какие запросы сейчас заблокированы и почему, можно использовать следующий запрос:
-<code>
+<code sql>
 SELECT
         r.trx_id waiting_trx_id,
@@ Строка 669: / Строка 677: @@
 В качестве примера я специально выполню долгий блокирующий запрос, а затем сделаю два запроса к этой таблице, которые будут ожидать снятия блокировки:
-<code>
+<code sql>
 # блокирующий запрос
 SELECT id, SLEEP(30) FROM drivers FOR UPDATE;
@@ Строка 701: / Строка 709: @@
 Если всё плохо и вам нужно срочно прибить блокирующий запрос, то можно воспользоваться командой ''KILL {blocking_thread}''. Для данного примера это будет выглядеть так:
-<code>
+<code sql>
 KILL 47;
 </code>
@@ Строка 707: / Строка 715: @@
 Чуть более подробный, но примерно такой же результат можно увидеть просто выполнив команду:
-<code>
+<code sql>
 SELECT * FROM sys.innodb_lock_waits;
 </code>
@@ Строка 713: / Строка 721: @@
 Например:
-<code>
+<code sql>
 # блокирующий запрос
 START TRANSACTION;
@@ Строка 725: / Строка 733: @@
 </code>
-> %%******%% 1.row %%******%% -- wait_started: 2021-12-03 09:50:18 -- wait_age: 00:00:34 -- wait_age_secs: 34 -- locked_table: ‘locks’.drivers -- locked_table_schema: locks -- locked_table_name: drivers -- locked_table_partition: NULL -- locked_table_subpartition: NULL -- locked_index: PRIMARY -- locked_type: RECORD -- waiting_trx_id: 3253 -- waiting_trx_started: 2021-12-03 09:50:18 -- waiting_trx_age: 00:00:34 -- waiting_trx_rows_locked: 1 -- waiting_trx_rows_modified: 0 -- waiting_pid: 53 -- waiting_query: SELECT name FROM drivers FOR UPDATE; -- waiting_lock_id: 140500149771672:2:4:25:140500057267936 -- waiting_lock_mode: X -- blocking_trx_id: 3252 -- blocking_pid: 52 -- blocking_query: NULL -- blocking_lock_id: 140500149772528:2:4:25:140500057274160 -- blocking_lock_mode: X -- blocking_trx_started: 2021-12-03 09:50:16 -- blocking_trx_age: 00:00:36 -- blocking_trx_rows_locked: 11 -- blocking_trx_rows_modified: 0 -- sql_kill_blocking_query: KILL QUERY 52 -- sql_kill_blocking_connection: KILL 52
+<code sql>
+****** 1.row ******
+-- wait_started: 2021-12-03 09:50:18
+-- wait_age: 00:00:34
+-- wait_age_secs: 34
+-- locked_table: ‘locks’.drivers
+-- locked_table_schema: locks
+-- locked_table_name: drivers
+-- locked_table_partition: NULL
+-- locked_table_subpartition: NULL
+-- locked_index: PRIMARY
+-- locked_type: RECORD
+-- waiting_trx_id: 3253
+-- waiting_trx_started: 2021-12-03 09:50:18
+-- waiting_trx_age: 00:00:34
+-- waiting_trx_rows_locked: 1
+-- waiting_trx_rows_modified: 0
+-- waiting_pid: 53
+-- waiting_query: SELECT name FROM drivers FOR UPDATE;
+-- waiting_lock_id: 140500149771672:2:4:25:140500057267936
+-- waiting_lock_mode: X
+-- blocking_trx_id: 3252
+-- blocking_pid: 52
+-- blocking_query: NULL
+-- blocking_lock_id: 140500149772528:2:4:25:140500057274160
+-- blocking_lock_mode: X
+-- blocking_trx_started: 2021-12-03 09:50:16
+-- blocking_trx_age: 00:00:36
+-- blocking_trx_rows_locked: 11
+-- blocking_trx_rows_modified: 0
+-- sql_kill_blocking_query: KILL QUERY 52
+-- sql_kill_blocking_connection: KILL 52
+</code>
 Однако в ряде случаев в колонке blocking_query будет значение NULL. Такое происходит, когда сеанс переходит в неактивное состояние, то есть не делает ничего. Для примера я начну транзакцию и не буду выполнять COMMIT:
-<code>
+<code sql>
 # блокирующий запрос
 START TRANSACTION;
@@ Строка 751: / Строка 821: @@
 С помощью нужного blocking_thread получить идентификатор процесса блокирующей транзакции:
-<code>
+<code sql>
 SELECT blocking_pid
 FROM sys.innodb_lock_waits
@@ Строка 759: / Строка 829: @@
 С помощью полученного blocking_pid получить идентификатор потока:
-<code>
+<code sql>
 SELECT THREAD_ID
 FROM performance_schema.threads
@@ Строка 767: / Строка 837: @@
 С помощью полученного THREAD_ID получить текст запроса:
-<code>
+<code sql>
 SELECT THREAD_ID, SQL_TEXT
 FROM performance_schema.events_statements_current
@@ Строка 775: / Строка 845: @@
 Если запрос не предоставил достаточную информацию и в SQL_TEXT вы получили что-то типа ''SELECT @@session.transaction_isolation'', то можно запросить историю, чтобы посмотреть последние 10 команд:
-<code>
+<code sql>
 SELECT THREAD_ID, SQL_TEXT
 FROM performance_schema.events_statements_history
@@ Строка 784: / Строка 854: @@
 Один запрос для всего вышеперечисленного:
-<code>
+<code sql>
 SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
 WHERE THREAD_ID = (
@@ Строка 812: / Строка 882: @@
 Еще один способ узнать о блокировках в системе — это воспользоваться командой SHOW ENGINE INNODB STATUS;. Но предварительно нужно установить переменную ''innodb_status_output_locks'' в значение ON.
-<code>
+<code sql>
 SET GLOBAL innodb_status_output_locks = ON;
 SHOW ENGINE INNODB STATUS;

Инструменты пользователя

Инструменты сайта

Различия

Инструменты страницы