InnoDB использует стратегию блокировки на уровне строк.Хотя стратегия блокировки на уровне строк значительно улучшает одновременную производительность программы, из-за уменьшения детализации блокировок InnoDB может занимать несколько блокировок строк одновременно при выполнении определенных операций, увеличивая количество блокировок Вероятность конфликта Разные уровни изоляции транзакций предъявляют разные требования к консистентности данных Например, повышенный разрыв блокировки на уровне RR может привести к большому количеству конфликтов блокировок В то же время неправильный бизнес-план может также вызвать взаимоблокировку. Взаимоблокировки будут занимать системные ресурсы и блокировать запросы, что повлияет на эффективность выполнения SQL и замедлит работу системы или приведет к недоступности сервера. Следовательно, нам нужно максимально избегать возникновения взаимоблокировок в процессе использования, а при обнаружении взаимоблокировок мы можем проверять взаимоблокировки на основе соответствующей информации, чтобы предотвратить их.

тупиковый механизм

Во-первых, давайте рассмотрим четыре необходимых условия, которые приводят к взаимоблокировке:

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

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

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Предустановленные данные следующие:

id	name
1	Чжан Сан
2	Li Si.
3	Ван Ву

Теперь удалите две части данных Zhang San и Li Si следующим образом:

серийный номер	Транзакция 1	условие	Транзакция 2	условие
1	begain transaction;
2	delete from test where id = 1;	Выполнение прошло успешно, блокировка X удерживается с id=1
3			begain transaction;
4			delete from test where id = 2;	Выполнение прошло успешно, блокировка X удерживается с id=2
5	delete from test where id = 2;	Ожидание снятия блокировки транзакцией 2
6			delete from test where id = 1;	Ожидание транзакции 1 для снятия блокировки. Система обнаруживает взаимоблокировку и откатывает транзакцию 2
7	commit;
8			commit;

Во время описанной выше операции мы создали очень типичное условие взаимоблокировки Механизм взаимоблокировки InnoDB обнаруживает взаимоблокировку и выборочно откатывает транзакции с малым весом. Статус взаимоблокировки можно запросить с помощью инструкции:

show engine innodb status;

Из приведенного выше процесса выполнения мы видим, что InnoDB может обнаруживать взаимоблокировки, так как же InnoDB обнаруживает взаимоблокировки?

Политика тупиковой политики InnoDB

InnoDB имеет два метода обработки тупиков:

ждать, до истечения времени ожидания (черезinnodb_lock_wait_timeoutконтроль параметров)
обнаружение взаимоблокировки, активно откатывать транзакцию и позволять другим транзакциям продолжать выполняться (innodb_deadlock_detect=on), этот механизм применим только к случаю, когда есть только блокировки строк

Обнаружение взаимоблокировки в InnoDB выполняется с помощьюWaiting For Graphреализуется по алгоритму. В InnoDB блокировки всех транзакций записываются для формирования ориентированного графа с транзакциями в качестве вершин и блокировками в качестве ребер. Только оценивая, есть ли в ориентированном графе петля, можно узнать, есть ли взаимоблокировка. В приведенном выше сценарии взаимоблокировки транзакция 1 ожидает, пока транзакция 2 освободит блокировку с идентификатором = 2, а транзакция 2 также ожидает, пока транзакция 1 освободит блокировку с идентификатором = 1, образуя структуру обратной связи.Поэтому InnoDB определяет, что в системе тупик.

Итак, предположим, что есть много транзакций, и транзакции ждут друг друга, образуя очень длинную цепочку ожидания, как InnoDB справится с этим?В этом случае, когда InnoDB ожидает, пока длина списка графов превысит200, InnoDB считает, что существует взаимоблокировка. Кроме того, InnoDB также считает, что существует взаимоблокировка, когда он-чейн транзакции удерживают слишком много блокировок (необходимо получить более 1 000 000 блокировок строк).

Как InnoDB освобождает транзакцию при обнаружении взаимоблокировки?InnoDB Чтобы определить важность транзакции посредством веса транзакции, будет выпущен вес, вес транзакции обычно вставлен, обновляется, обновляется, удаленные операторы, выполняющие количество стержней данных, выполняющие оператор транзакции, меньшее количество стержней данных влияют на количество баров данных. Чем ниже вес транзакции.

Анализ состояния InnoDB

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

show engine innodb status;

Стандартный журнал состояния выглядит следующим образом:

=====================================
2019-11-19 23:19:23 0x7fa850f58700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 48 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 852151 srv_active, 0 srv_shutdown, 4608208 srv_idle
srv_master_thread log flush and writes: 5459518
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2440006
OS WAIT ARRAY INFO: signal count 2411688
RW-shared spins 0, rounds 1948637, OS waits 972484
RW-excl spins 0, rounds 1702845, OS waits 160434
RW-sx spins 4496, rounds 134826, OS waits 4138
Spin rounds per wait: 1948637.00 RW-shared, 1702845.00 RW-excl, 29.99 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-11-19 23:19:08 0x7fa860156700
*** (1) TRANSACTION:
TRANSACTION 25321837, ACTIVE 29 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2348540, OS thread handle 140360898553600, query id 66353526 163.125.229.225 root updating
delete from test where id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2266 page no 3 n bits 80 index PRIMARY of table `test`.`test` trx id 25321837 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000001826186; asc     a ;;
 2: len 7; hex 65000001250c88; asc e   %  ;;
 3: len 6; hex e69d8ee59b9b; asc       ;;

*** (2) TRANSACTION:
TRANSACTION 25321862, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2348917, OS thread handle 140361143248640, query id 66353634 163.125.229.225 root updating
delete from test where id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2266 page no 3 n bits 80 index PRIMARY of table `test`.`test` trx id 25321862 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000001826186; asc     a ;;
 2: len 7; hex 65000001250c88; asc e   %  ;;
 3: len 6; hex e69d8ee59b9b; asc       ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2266 page no 3 n bits 80 index PRIMARY of table `test`.`test` trx id 25321862 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000182616d; asc     am;;
 2: len 7; hex 58000001412fef; asc X   A/ ;;
 3: len 6; hex e5bca0e4b889; asc       ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 25321913
Purge done for trx's n:o < 25321911 undo n:o < 0 state: running but idle
History list length 35
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421836854896032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854885088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854895120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854891472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854888736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854886912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854900592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854898768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854893296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854892384, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854890560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854886000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421836854883264, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 25321912, ACTIVE 0 sec
5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 2346328, OS thread handle 140360896157440, query id 66354003 47.98.152.73 root
Trx read view will not see trx with id >= 25321911, sees < 25321837
---TRANSACTION 25321837, ACTIVE 44 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 2348540, OS thread handle 140360898553600, query id 66353526 163.125.229.225 root
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
846404 OS file reads, 16785073 OS file writes, 5999964 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 8.37 writes/s, 3.17 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 185, seg size 187, 28201 merges
merged operations:
 insert 528, delete mark 7663041, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 138389, node heap has 31 buffer(s)
Hash table size 138389, node heap has 2 buffer(s)
Hash table size 138389, node heap has 3983 buffer(s)
Hash table size 138389, node heap has 218 buffer(s)
Hash table size 138389, node heap has 5 buffer(s)
Hash table size 138389, node heap has 1 buffer(s)
Hash table size 138389, node heap has 2 buffer(s)
Hash table size 138389, node heap has 5 buffer(s)
139.08 hash searches/s, 4.46 non-hash searches/s
---
LOG
---
Log sequence number 24370884626
Log flushed up to   24370882478
Pages flushed up to 24370875924
Last checkpoint at  24370875333
0 pending log flushes, 0 pending chkp writes
9315962 log i/o's done, 2.31 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 549715968
Dictionary memory allocated 10240009
Buffer pool size   32764
Free buffers       1024
Database pages     27493
Old database pages 10128
Modified db pages  34
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 964069, not young 143276717
0.00 youngs/s, 0.00 non-youngs/s
Pages read 845408, created 77961, written 6649614
0.00 reads/s, 0.04 creates/s, 5.40 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 27493, unzip_LRU len: 0
I/O sum[276]:cur[1], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=1773, Main thread ID=140361173219072, state: sleeping
Number of rows inserted 19006577, updated 853025, deleted 5584787, read 210247854332
0.85 inserts/s, 0.83 updates/s, 0.06 deletes/s, 21633.22 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

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

выходной заголовок

Заголовок вывода стандартной информации мониторинга включает следующие части, в которых отображается время запроса текущего мониторинга, имя мониторинга и интервал времени между текущим выходом и последним выходом в секундах.

Информация о фоновом потоке

В этом разделе показана рабочая нагрузка фонового основного потока innodb. Вообще говоря,srv_activeчем большеsrv_idleЧем меньше токmysqlБаза данных не находится под давлением.

Информация о семафоре

Раздел семафора подсчитывает количество бездействующих потоков и количество раз, ожидающих получения мьютекса или семафора блокировки чтения-записи. Блокировки взаимного исключения и блокировки чтения-записи здесь относятся к Latch в mysql, и их основная цель — обеспечить правильность параллельных операций потока на критических ресурсах. Если семафор ожидает большое количество потоков, это может быть вызвано узким местом в производительности дискового ввода-вывода или жесткой конкуренцией за внутренние ресурсы InnoDB, а внутренние ресурсы InnoDB обычно вызваны слишком большим количеством параллельных запросов или проблемами с планированием потоков операционной системы. . Если количество os_waits велико, это означает, что конкуренция защелки более частая.

Последняя информация о взаимоблокировках

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

информация о транзакции

Информация о транзакциях показывает связанные транзакции в текущем innodb за предыдущий период, что помогает нам устранять взаимоблокировки.

Информация о файловом вводе-выводе

В этом разделе показана ситуация с вводом-выводом фоновых потоков.

Вставить информацию о буфере и адаптивном хеш-индексе

В этом разделе представлена информация о производительности буфера вставки и адаптивном индексировании хэшей.

информация журнала

Информация журнала показывает текущее состояние журнала повторов.

Буферный пул и информация о памяти

В этом разделе показана статистика чтения и записи страниц в пуле буферов и статистика использования его памяти.

информация об операции строки

В этом разделе показано, что делает основной поток, например количество и статистика производительности различных типов операций со строками.

Как избежать тупика

Чтобы уменьшить влияние взаимоблокировки на систему, мы должны максимально избегать взаимоблокировки. Обычно для избежания взаимоблокировок можно использовать следующие методы:

Используйте транзакции, старайтесь не блокировать таблицы, такие как таблицы блокировки, В случае нескольких типов блокировки InnoDB не может выполнять обнаружение взаимоблокировки и может только ждать тайм-аута взаимоблокировки.
Минимизируйте время выполнения транзакций и избегайте длинных транзакций, длинные транзакции могут удерживать блокировки слишком долго, что с большей вероятностью приведет к взаимоблокировкам.
более низкий уровень изоляции, на уровне RC отсутствует блокировка разрыва, что значительно снижает вероятность взаимоблокировки.
Когда оператор MDL изменяет несколько таблиц или строк данных, последовательность операций должна быть последовательной., чтобы избежать взаимоблокировки, вызванной неправильным процессом блокировки.
Операторы MDL пытаются использовать первичные ключи или индексыБлокировка InnoDB - это заблокировать индекс. Использование первичного ключа или индекса может уменьшить вероятность конфликта блокировки.
Если взаимоблокировки нельзя избежать из-за потребностей бизнеса, рассмотрите возможность блокировки таблиц или управляйте распределенной блокировкой.

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

благодарный

15.7.5 Deadlocks in InnoDB
14.17.2 Enabling InnoDB Monitors
14.17.3 InnoDB Standard Monitor and Lock Monitor Output
Общий анализ случаев взаимоблокировки MySQL
Анализ блокировки MySQL
Приложение B показывает подробное объяснение состояния innodb движка
Разговор о блокировках MySQL
Углубленный анализ и оценка споров о защелках MySQL
Путь к решению тупиковых ситуаций (конец) - Прощай, тупиковые ситуации