Всестороннее понимание механизма блокировки mysql (InnoDB) и устранение неполадок

задняя часть база данных MySQL SQL

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

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

  • Блокировка на уровне таблицы: низкие накладные расходы и быстрая блокировка, отсутствие взаимоблокировок, большая степень детализации блокировки, самая высокая вероятность конфликта блокировок и самый низкий уровень параллелизма.
  • Блокировки страниц: накладные расходы и время блокировки находятся между блокировками таблиц и блокировками строк; могут возникать взаимоблокировки; степень детализации блокировки находится между блокировками таблиц и блокировками строк, а степень параллелизма средняя.
  • Блокировки на уровне строк: большие накладные расходы, медленная блокировка, тупиковые ситуации, наименьшая степень детализации блокировки, наименьшая вероятность конфликтов блокировок и наивысший уровень параллелизма.

Просмотр типа механизма хранения базы данных
SHOW ENGINES

оптимистическая блокировка

Он реализован с помощью механизма записи версии данных (Version), который является наиболее часто используемой реализацией оптимистической блокировки. Что такое версия данных? Это означает добавление идентификатора версии к данным, обычно путем добавления числового поля «версия» в таблицу базы данных. При чтении данных считывайте значение поля версии вместе и добавляйте 1 к значению версии каждый раз, когда данные обновляются. Когда мы отправляем обновление, сравниваем информацию о текущей версии соответствующей записи в таблице базы данных со значением версии, извлеченным в первый раз.Если номер текущей версии таблицы базы данных равен значению версии, извлеченному для первого время, они будут обновлены, в противном случае они считаются просроченными данными.

Пример:

1. В таблице БД есть три поля, а именно id, value, version
select id,value,version from TABLE where id = #{id}
2. Каждый раз, когда вы обновляете поле значения в таблице, во избежание конфликтов, вам нужно сделать это

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version}

пессимистический замок

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

Сказав это, появляются две другие концепции блокировки, связанные с пессимистическими блокировками, это разделяемые блокировки и эксклюзивные блокировки.Разделяемые блокировки и эксклюзивные блокировки — это разные реализации пессимистической блокировки, и обе относятся к категории пессимистической блокировки.

общий замок

Общая блокировка, также известная как блокировка чтения, — это блокировка, созданная операцией чтения. Другие пользователи могут читать данные одновременно, но ни одна транзакция не может вносить изменения в данные (получать эксклюзивную блокировку данных), пока не будут сняты все общие блокировки. Когда транзакция изменяет блокировку чтения, это может привести к взаимоблокировке. Как показано ниже.

Если транзакция T добавляет общую блокировку к данным A, другие транзакции могут добавлять только общие блокировки к A, но не эксклюзивные блокировки. Транзакция, которая получает общую блокировку, может только читать данные и не может изменять данные.

Откройте первое окно запроса

begin;/begin work;/start transaction;  (三者选一就可以)
#(lock in share mode 共享锁)
SELECT * from TABLE where id = 1  lock in share mode;

Затем в другом окне запроса обновите данные с идентификатором 1.
update TABLE set name="www.souyunku.com" where id =1;
В этот момент рабочий интерфейс перешел в зависшее состояние, и после длительного перерыва появилось сообщение об ошибке.
Если до истечения времени ожидания выполняется первое окноcommit, оператор обновления завершится успешно.

[SQL]update test_one set name="www.souyunku.com" where id =1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

После добавления общей блокировки также появляется сообщение об ошибке.

update test_one set name="www.souyunku.com" where id =1 lock in share mode;
[SQL]update  test_one set name="www.souyunku.com" where id =1 lock in share mode;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

добавить после запросаLOCK IN SHARE MODE, Mysql добавит общую блокировку к каждой строке в результате запроса. Если ни один другой поток не использует эксклюзивную блокировку для любой строки в наборе результатов запроса, он может успешно применить общую блокировку, в противном случае он будет заблокирован. .Другие потоки также могут читать таблицу, используя общую блокировку, и эти потоки читают одну и ту же версию данных.

После добавления разделяемой блокировки к операторам обновления, вставки и удаления автоматически добавляется монопольная блокировка.

эксклюзивный замок

Эксклюзивная блокировка Эксклюзивная блокировка (также называемая блокировкой записи) также известна как блокировка записи.
Объяснение терминов: Если что-то добавляет монопольную блокировку к строке, только эта транзакция может читать и записывать ее.До окончания этой транзакции другие транзакции не могут добавлять к ней никаких блокировок, другие процессы могут ее читать, но не могут писать операцию, нужно дождаться его выхода.Эксклюзивная блокировка — это реализация пессимистической блокировки., пессимистическая блокировка также была введена выше.

Если транзакция 1 добавляет блокировку X к объекту данных A, транзакция 1 может читать A или изменять A, а другие транзакции не могут добавлять какие-либо блокировки к A, пока транзакция 1 не снимет блокировку с A. Это гарантирует, что другие транзакции больше не смогут читать и изменять A, пока транзакция 1 не снимет блокировку с A.Эксклюзивные блокировки блокируют все эксклюзивные и общие блокировки

Зачем нужна блокировка чтения для чтения? Предотвращает запись данных другими потоками во время их чтения. Эксклюзивное использование блокировки: добавьте оператор после оператора, который необходимо выполнить.for updateпросто хорошоselect status from TABLE where id=1 for update;

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

Эксклюзивная блокировка - пример:

Чтобы использовать эксклюзивные блокировки, мы должны отключить атрибут autocommit базы данных mysql, потому что MySQL по умолчанию использует режим autocommit, то есть при выполнении операции обновления MySQL немедленно отправит результат.

Мы можем установить MySQL в режим без автоматической фиксации с помощью команды:

set autocommit=0;
# 设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
# 1. 开始事务
begin;/begin work;/start transaction; (三者选一就可以)
# 2. 查询表信息（for update加锁）
select status from TABLE where id=1 for update;
# 3. 插入一条数据
insert into TABLE (id,value) values (2,2);
# 4. 修改数据为
update TABLE set value=2 where id=1;
# 5. 提交事务
commit;/commit work

блокировка строки

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

# Transaction-A
mysql> set autocommit = 0;
mysql> update innodb_lock set v='1001' where id=1;
mysql> commit;

# Transaction-B
mysql> update innodb_lock set v='2001' where id=2;
Query OK, 1 row affected (0.37 sec)
mysql> update innodb_lock set v='1002' where id=1;
Query OK, 1 row affected (37.51 sec)

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

Резюме: блокировки строк InnoDB — это блокировки для индексов, а не для записей. И индекс нельзя сделать недействительным, иначе он будет обновлен с блокировки строки до блокировки таблицы.

  • Недостатки блокировки строк: большие накладные расходы, медленная блокировка, тупиковые ситуации.
  • Преимущества блокировок строк: малая степень детализации блокировок, низкая вероятность конфликтов блокировок, хорошая возможность обработки параллелизма.
  • Метод блокировки: автоматическая блокировка. Для операторов UPDATE, DELETE и INSERT InnoDB автоматически добавит эксклюзивные блокировки к задействованным наборам данных; для обычных операторов SELECT InnoDB не добавит никаких блокировок; конечно, мы также можем отобразить блокировки:

Как видно из приведенного выше случая, блокировка строки в блокировку таблицы кажется ямой, но MySQL не настолько скучен, чтобы копать вам яму. Это связано с тем, что у MySQL есть собственный план выполнения. Когда вам нужно обновить большую часть или даже все данные большой таблицы. И вы по глупости используете индекс как условие поиска. Блокировка строки была случайно открыта (нет проблем! Обеспечьте согласованность данных!). Тем не менее, MySQL считает, что использование большого количества блокировок строк в таблице приведет к низкой эффективности выполнения транзакций, что может привести к тому, что другие транзакции будут долго ждать блокировок и больше конфликтов блокировок, что приведет к серьезному снижению производительности. Таким образом, MySQL увеличивает блокировки строк до блокировки таблиц, т.е. индексы фактически не используются. Если хорошенько подумать, то можно понять, что, поскольку большая часть данных во всей таблице нуждается в обновлении, менее эффективно блокировать построчно. Фактически, мы можем просмотреть план выполнения MySQL с помощью команды объяснения, и вы обнаружите, что ключ равен нулю. Это показывает, что MySQL на самом деле не использует индексы, и обновление блокировок строк до блокировок таблиц также согласуется с приведенным выше выводом.

Примечание: Блокировки на уровне строк основаны на индексах.Если оператор SQL не использует индекс, он не будет использовать блокировки на уровне строк, но будут использоваться блокировки на уровне таблицы.

гэп замок

Когда мы извлекаем данные с условиями диапазона вместо условий равенства и запрашиваем общую или эксклюзивную блокировку, InnoDB блокирует записи индекса существующих записей данных, которые соответствуют условиям; для записей, значения ключей которых находятся в пределах диапазона условий, но не существует, Он называется «GAP», и InnoDB также заблокирует этот «пробел». Например, если в таблице emp всего 101 запись, а значения empid равны 1, 2, ..., 100, 101, следующий SQL:

Select * from  emp where empid > 100 for update;

Это поиск условия диапазона InnoDB не только блокирует записи со значением empid 101, которые соответствуют условиям, но также блокирует «пробел» с empid больше 101 (эти записи не существуют).

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

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

Следует также отметить, что в дополнение к использованию блокировок с промежутком, когда InnoDB блокирует через условия диапазона, если условие равенства используется для блокировки несуществующей записи, InnoDB также будет использовать блокировки с промежутком!

Пример: Если в таблице emp всего 101 запись, значения empid равны 1, 2, ......, 100, 101 соответственно.
Пример блокировки Gap Lock в механизме хранения InnoDB

session_1 session_2
mysql> select @@tx_isolation; mysql> select @@tx_isolation;
+-----------------+ +-----------------+
@@tx_isolation @@tx_isolation
+-----------------+ +-----------------+
REPEATABLE-READ REPEATABLE-READ
+-----------------+ +-----------------+
1 row in set (0.00 sec) 1 row in set (0.00 sec)
mysql> set autocommit = 0; mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
Текущая сессия добавляет блокировку обновления к несуществующим записям:
mysql> select * from emp where empid = 102 for update;
Empty set (0.00 sec)
В это время, если другой сеанс вставит запись с empid 201 (примечание: такой записи не существует), также произойдет ожидание блокировки:
mysql>insert into emp(empid,...) values(201,...);
блокировка ожидания
Session_1 выполняет откат:
mysql> rollback;
Query OK, 0 rows affected (13.04 sec)
Поскольку блокировка Next-Key снимается после отката другого сеанса_1, текущий сеанс может получить блокировку и успешно вставить запись:
mysql>insert into emp(empid,...) values(201,...);
Query OK, 1 row affected (13.35 sec)

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

блокировка стола

Как добавить блокировку таблицы? Блокировка строки Innodb происходит в случае индекса, а таблица без индекса блокирует всю таблицу.

Блокировки строк и блокировки таблиц в Innodb

Как упоминалось ранее, в движке Innodb поддерживаются как блокировки строк, так и блокировки таблиц, поэтому когда будет заблокирована вся таблица, а когда будет заблокирована только одна строка?InnoDB использует блокировки на уровне строк только при извлечении данных через условия индекса, в противном случае InnoDB будет использовать блокировки таблиц!

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

Все блокировки на уровне строк основаны на индексах.Если оператор SQL не использует индекс, он не будет использовать блокировки на уровне строк, а будет использовать блокировки на уровне таблицы. Недостатком блокировок на уровне строк является то, что из-за необходимости запрашивать большое количество ресурсов блокировки скорость низкая, а потребление памяти велико.

тупик

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

Есть два способа выйти из тупиковой ситуации:
Первое:

  1. Запрос на блокировку таблицыshow OPEN TABLES where In_use > 0;
  2. Процессы запросов (если у вас есть привилегия SUPER, вы можете видеть все потоки. В противном случае вы можете видеть только свои собственные потоки)
    show processlist
  3. Убейте идентификатор процесса (то есть столбец идентификатора вышеуказанной команды)
    kill id

Секунда:

  1. Просмотр текущих транзакций
    SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
  2. Просмотр заблокированных транзакций
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
  3. Просмотр текущих транзакций, ожидающих блокировки
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    убить процесс
    kill 进程ID

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

  1. Условие взаимного исключения: ресурс может использоваться только одним процессом одновременно.
  2. Условие запроса и удержания: когда процесс блокируется запросом ресурса, он будет удерживать полученный ресурс.
  3. Условие отсутствия лишения: ресурсы, полученные процессом, не могут быть принудительно лишены до тех пор, пока ресурсы не будут израсходованы.
  4. Условие циклического ожидания: Между несколькими процессами формируются отношения ресурсов циклического ожидания.

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

Следующие методы могут помочь свести к минимуму взаимоблокировки:

  1. Доступ к объектам осуществляется в том же порядке.
  2. Избегайте взаимодействия с пользователем в транзакциях.
  3. Держите транзакции короткими и в одном пакете.
  4. Используйте низкий уровень изоляции.
  5. Используйте облигационные соединения.

Механизм хранения MyISAM

Между InnoDB и MyISAM есть два основных различия:

  1. InnoDB поддерживает транзакции, MyISAM не поддерживает транзакции.
  2. Innodb по умолчанию использует блокировки строк, а MyISAM по умолчанию использует блокировки таблиц. Блокировка может обеспечить согласованность транзакций.Можно сказать, что там, где есть люди (замки), есть реки и озера (транзакции).
  3. MyISAM не подходит для высокой параллелизма

общая блокировка чтения

Операция чтения (добавление блокировки чтения) в таблицу MyISAM не блокирует операцию чтения той же таблицы другими процессами, но блокирует операцию записи той же таблицы. Только после снятия блокировки чтения можно выполнять операции записи других процессов. Никакие другие таблицы не могут быть прочитаны, пока блокировка не будет снята.

Эксклюзивная блокировка записи

Операция записи (добавление блокировки записи) в таблицу MyISAM заблокирует операции чтения и записи других процессов в ту же таблицу, и только когда блокировка записи будет снята, операции чтения и записи других процессов будут выполняться. Никакие другие таблицы не могут быть записаны, пока блокировка не будет снята.

Суммировать:

  1. Блокировки таблицы, блокировки чтения блокируют запись, но не блокируют чтение. Блокировка записи блокирует как чтение, так и запись.
  2. Блокировка/разблокировка блокировок таблиц: MyISAM автоматически добавит блокировки чтения ко всем задействованным таблицам перед выполнением оператора запроса (SELECT) и автоматически предоставит задействованные таблицы перед выполнением операции обновления (UPDATE, DELETE, INSERT и т. д.). запись блокировки, этот процесс не требует вмешательства пользователя, поэтому пользователям обычно не нужно напрямую использовать команду LOCK TABLE для явной блокировки таблицы MyISAM.

Если пользователь хочет отобразить замок, можно использовать следующую команду:

Таблица блокировки:

LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

Разблокировать стол:

UNLOCK TABLES

С использованиемLOCK TABLESПри явном добавлении блокировок таблицы к таблице все блокировки, связанные с таблицей, должны быть получены одновременно. в исполненииLOCK TABLESПосле этого доступ будет возможен только к тем таблицам, которые явно заблокированы, а к разблокированным таблицам доступ будет невозможен;

Если добавлена ​​блокировка чтения, можно выполнять только операции запроса, но нельзя выполнять операции обновления.

В основном это происходит в случае автоматической блокировки, MyISAM всегда получает все блокировки, требуемые оператором SQL, одновременно. Это также является причиной того, что таблицы MyISAM не заблокированы (Deadlock Free).

Добавьте блокировку чтения в таблицу test_table:

LOCK TABLES test_table READ 
UNLOCK test_table

Добавить блокировку записи в таблицу test_table

LOCK TABLES test_table WRITE
UNLOCK test_table

При использовании LOCK TABLES нужно не только заблокировать все таблицы, используемые одновременно, но и сколько раз одна и та же таблица появляется в операторе SQL, сколько раз ее нужно заблокировать тем же псевдонимом, что и в операторе SQL. , иначе все пойдет не так!

Например, следующий оператор SQL:

select a.first_name,b.first_name, from actor a,actor b where a.first_name = b.first_name;

В операторе Sql таблица акторов появляется дважды в виде псевдонимов, а именно a и b. В настоящее время, если вы хотите заблокировать до выполнения Sql, вам нужно использовать следующий Sql:

lock table actor as a read,actor as b read;

одновременная вставка

Выше было сказано, что чтение и запись таблиц MyISAM последовательны, но это в общем случае. При определенных условиях таблицы MyISAM также поддерживают параллельные операции запроса и вставки. Механизм хранения MyISAM имеет системную переменную concurrent_insert, которая специально используется для управления поведением одновременной вставки, и ее значение может быть 0, 1 или 2 соответственно.

  • Когда для параметра concurrent_insert установлено значение 0, параллельные вставки не допускаются.
  • Когда concurrent_insert имеет значение 1, если в таблице MyISAM нет дыр (то есть нет удаленных строк в середине таблицы), MyISAM позволяет одному процессу читать таблицу, в то время как другой процесс вставляет записи с конца таблицы. Таблица. Это также настройка по умолчанию для MySQL.
  • Когда для параметра concurrent_insert установлено значение 2, одновременная вставка записей в конец таблицы разрешена независимо от наличия дыр в таблице MyISAM.

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

Планирование блокировки MyISAM

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

Ответ заключается в том, что процесс записи первым получает блокировку.

Мало того, даже если запрос на чтение поступает в очередь ожидания блокировки первым, а запрос на запись поступает позже, блокировка записи будет вставлена ​​до запроса блокировки чтения! Это связано с тем, что MySQL считает запросы на запись более важными, чем запросы на чтение. Вот почему таблицы MyISAM не подходят для приложений с большим количеством операций обновления и операций запросов, поскольку большое количество операций обновления затруднит получение блокировок чтения операциями запросов, которые могут заблокироваться навсегда. Иногда эта ситуация может стать очень плохой!

К счастью, мы можем настроить поведение планирования MyISAM с помощью некоторых настроек.

указавПараметр запуска low-priority-updates, чтобы механизм MyISAM по умолчанию отдавал приоритет запросам на чтение.

  • выполнив командуSET LOWPRIORITYUPDATES=1,Снижает приоритет запросов на обновление, сделанных этим соединением.
  • Указав оператор INSERT, UPDATE, DELETELOW_PRIORITYатрибут, который снижает приоритет отчета.
  • Кроме того, MySQL также предоставляет компромиссный метод для корректировки конфликтов чтения и записи, т. е.Системный параметр max_write_lock_countУстановите соответствующее значение.Когда блокировка чтения таблицы достигает этого значения, MySQL временно снижает приоритет запроса на запись, давая процессу чтения определенный шанс получить блокировку.

Суммировать

  • Блокировки в базе данных можно разделить на блокировки на уровне строки, блокировки на уровне страницы и блокировки на уровне таблицы в зависимости от степени детализации блокировки.
  • Механизм MySQL MyISAM поддерживает блокировку на уровне таблицы.
  • Замок на уровне таблицы делится на два типа: совместно используемый замок чтения, взаимно эксклюзивный замок записи. Оба блокировки блокируют замки.
  • Блокировки чтения можно добавить к блокировкам чтения, но нельзя добавить блокировки записи к блокировкам чтения. Блокировки записи не могут быть добавлены к блокировкам записи.
  • По умолчанию MySql добавит блокировки чтения перед выполнением операторов запроса и выполнит блокировку записи перед выполнением операторов обновления.
  • Если вы хотите отобразить заблокированные/разблокированные цветы, вы можете использовать LOCK TABLES и UNLOCK для этого.
  • После использования LOCK TABLES вы не можете работать с разблокированной таблицей, пока она не будет разблокирована.
  • При блокировке, если индикация состоит в том, чтобы добавить блокировку чтения, перед разблокировкой могут выполняться только операции чтения, но операции записи выполняться не могут.
  • Если таблица, с которой должен работать оператор Sql, появляется несколько раз в виде псевдонима, то необходимо указать псевдоним таблицы, которая будет заблокирована при блокировке.
  • Механизм хранения MyISAM имеет системную переменную concurrent_insert, которая специально используется для управления поведением одновременной вставки, и ее значение может быть 0, 1 или 2 соответственно.
  • Поскольку блокировка чтения и блокировка записи являются взаимоисключающими, в процессе планирования MySql по умолчанию использует принцип приоритета блокировки записи. Можно установить через низкоприоритетные обновления.

практическое решение

Анализ блокировки строк

Пройден осмотрInnoDB_row_lockСостояние гонки в области переменная на блокировке системы BOC

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
  • innodb_row_lock_current_waits:Количество ожидающих блокировок
  • innodb_row_lock_time:Общая продолжительность времени от запуска системы до блокировки; очень важный параметр,
  • innodb_row_lock_time_avg:Среднее время, затрачиваемое на ожидание; очень важный параметр,
  • innodb_row_lock_time_max:Время ожидания наиболее частого от старта системы до настоящего момента;
  • innodb_row_lock_waits:Общее количество ожиданий системы с момента ее запуска, очень важный параметр. Непосредственно определить направление и стратегию оптимизации.

оптимизация блокировки строк

  1. Весь поиск данных должен выполняться через индексы, насколько это возможно, чтобы избежать обновления блокировок строк до блокировок таблиц из-за неиндексированных строк или сбоев индекса.
  2. Насколько это возможно, избегайте деградации производительности, вызванные зазором замком, или уменьшить использование разумного поиска.
  3. Максимально уменьшите детализацию транзакции, например, контролируйте размер транзакции, уменьшите количество ресурсов блокировки и продолжительность времени, тем самым уменьшив конкуренцию блокировок и т. д., чтобы обеспечить производительность.
  4. Самый низкий уровень изоляции транзакций насколько это возможно, чем выше уровень изоляции, тем ниже возможности параллельной обработки.

Оптимизация блокировки таблицы

Проверить состояние блокировкикак открывать столы; 1 означает заблокировано, 0 означает разблокировано.

mysql> show open tables where in_use > 0;
+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| lock     | myisam_lock |      1 |           0 |
+----------+-------------+--------+-------------+

Блокировка таблицы анализа

Блокировку таблиц в системе можно проанализировать, изучив переменные состояния table_locks_waited и table_locks_immediate:show status like 'table_locks%'

mysql> show status like 'table_locks%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 104   |
| Table_locks_waited         | 0     |
+----------------------------+-------+
  • table_locks_immediate:Указывает количество блокировок таблицы, которые необходимо снять немедленно.
  • table_locks_waited:Указывает количество ожидаемых блокировок таблицы. Более высокие значения указывают на более серьезную конкуренцию за блокировку на уровне таблицы.

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

В каких сценариях используются блокировки таблиц?

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

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

  1. InnoDB поддерживает блокировки таблиц и строк. Блокировки строк используются, когда данные изменяются с использованием индексов в качестве условий извлечения, а блокировки таблиц используются в противном случае.
  2. InnoDB автоматически блокирует операции модификации, но не блокирует автоматически операции запросов.
  3. Блокировка строки может быть обновлена ​​до блокировки таблицы, поскольку индекс не используется, поэтому в дополнение к проверке того, создан ли индекс, также необходимо запросить, действительно ли индекс используется с помощью плана выполнения объяснения.
  4. Преимущество блокировок строк по сравнению с блокировками таблиц заключается в том, что они более заметны в сценариях с высокой степенью параллелизма, поскольку степень детализации блокировок невелика.
  5. Когда необходимо изменить большую часть данных в таблице или когда выполняется сложный запрос к нескольким таблицам, рекомендуется использовать блокировки таблиц вместо блокировок строк.
  6. Чтобы обеспечить непротиворечивую целостность данных, любая база данных имеет механизм блокировки. Качество механизма блокировки напрямую влияет на возможности параллельной обработки и производительность базы данных.

В MySQL 5.6, если в условии where во время обновления и удаления нет поля индекса, вызовет ли эта транзакция блокировку таблицы? Кто-то ответил: Только первичный ключ и уникальный индекс являются блокировками строк, а обычные индексы — блокировками таблиц.

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

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

Частота повторения значения атрибута

Когда «частота повторения значений» низкая, даже близкая к эффекту первичного ключа или уникального индекса, «обычный индекс» по-прежнему является блокировкой строки; когда «частота повторения значений» высока, MySQL не будет обрабатывать это. обычный индекс» в качестве индекса. Выполняется SQL без индекса, и в это время срабатывает блокировка таблицы.

Подобно тому, как JVM автоматически оптимизирует код Java, MySQL также имеет функцию автоматической оптимизации SQL. Неэффективные индексы игнорируются, что вынуждает разработчиков использовать правильные и эффективные индексы.

Высокая частота повторения значений атрибутов

Чтобы выделить эффект, я буду, «Общий индекс», установленный в рамках высоких свойств «скорости повторения ценности». Относительно экстремальный способ расширить его влияние на результаты.

Я создам "таблицу уровней баллов" с атрибутами "id", "score (score)", "level (level)", имитирующую полуавтоматический бизнес - "score" импортируется автоматически, а "level" обновляется вручную требуется.

Этапы операции следующие:

  1. Отменить автофиксацию транзакции MySQL
  2. Создайте таблицу, автоматически увеличивайте идентификатор и создайте общий индекс для «оценки».
  3. Вставьте значение оценки, ранг равен нулю
  4. Откройте две транзакции session_1, session_2, две транзакции задают разные значения на основе условия «оценка» и блокируют данные
  5. session_1 и session_2 последовательно обновляют «уровень» своего соответствующего содержимого блокировки транзакций.
  6. Наблюдайте за реакцией базы данных на две транзакции.

Отменить автофиксацию транзакции:

mysql> set autocommit = off;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like "autocommit";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| autocommit               | OFF   |
+--------------------------+-------+
1 rows in set (0.01 sec)

Создайте таблицы, создайте индексы и вставьте данные:

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
`ID`  int(5) NOT NULL AUTO_INCREMENT ,
`SCORE`  int(3) NOT NULL ,
`LEVEL`  int(2) NULL DEFAULT NULL ,
PRIMARY KEY (`ID`)
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE `test2` ADD INDEX index_name ( `SCORE` );

INSERT INTO `test1`(`SCORE`) VALUE (100);
……
INSERT INTO `test1`(`SCORE`) VALUE (0);

«Частота повторения значения» атрибута «SCORE» чрезвычайно высока, достигает 50%, а меч наклонен:

mysql> select * from test1;
+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
|  2 |     0 | NULL  |
|  5 |   100 | NULL  |
|  6 |   100 | NULL  |
|  7 |   100 | NULL  |
|  8 |   100 | NULL  |
|  9 |   100 | NULL  |
| 10 |   100 | NULL  |
| 11 |   100 | NULL  |
| 12 |   100 | NULL  |
| 13 |   100 | NULL  |
| 14 |     0 | NULL  |
| 15 |     0 | NULL  |
| 16 |     0 | NULL  |
| 17 |     0 | NULL  |
| 18 |     0 | NULL  |
| 19 |     0 | NULL  |
| 20 |     0 | NULL  |
| 21 |     0 | NULL  |
| 22 |     0 | NULL  |
| 23 |     0 | NULL  |
| 24 |   100 | NULL  |
| 25 |     0 | NULL  |
| 26 |   100 | NULL  |
| 27 |     0 | NULL  |
+----+-------+-------+
25 rows in set

Откройте две транзакции (одно окно соответствует одной транзакции) и выберите данные:

-- SESSION_1，选定 SCORE = 100 的数据
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 100 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
|  5 |   100 | NULL  |
|  6 |   100 | NULL  |
|  7 |   100 | NULL  |
|  8 |   100 | NULL  |
|  9 |   100 | NULL  |
| 10 |   100 | NULL  |
| 11 |   100 | NULL  |
| 12 |   100 | NULL  |
| 13 |   100 | NULL  |
| 24 |   100 | NULL  |
| 26 |   100 | NULL  |
+----+-------+-------+
12 rows in set

Откройте другое окно:

-- SESSION_2，选定 SCORE = 0 的数据
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 0 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  2 |     0 | NULL  |
| 14 |     0 | NULL  |
| 15 |     0 | NULL  |
| 16 |     0 | NULL  |
| 17 |     0 | NULL  |
| 18 |     0 | NULL  |
| 19 |     0 | NULL  |
| 20 |     0 | NULL  |
| 21 |     0 | NULL  |
| 22 |     0 | NULL  |
| 23 |     0 | NULL  |
| 25 |     0 | NULL  |
| 27 |     0 | NULL  |
+----+-------+-------+
13 rows in set

окно session_1, обновление "LEVEL" не удалось:

mysql> UPDATE `test1` SET `LEVEL` = 1 WHERE `SCORE` = 100;
1205 - Lock wait timeout exceeded; try restarting transaction

В предыдущей операции session_1 выбралSCORE= 100 данных, выбрано session_2SCORE= 0 данных, кажется, что две транзакции не виноваты в воде, но невозможность обновления данных, заблокированных самой собой в транзакции session_1, может указывать только на то, что в это время сработала блокировка таблицы. Не волнуйтесь, вы только что впали в одну крайность — значение атрибута индекса чрезвычайно повторяющееся, а затем впадает в другую крайность.

Частота повторения значения атрибута низкая

   по-прежнему остается той же таблицей, осталось только две части данных, а «коэффициент дублирования значений» «SCORE» равен 0:

mysql> delete from test1 where id > 2;
Query OK, 23 rows affected

mysql> select * from test1;
+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
|  2 |     0 | NULL  |
+----+-------+-------+
2 rows in set

Закройте два окна операций с транзакциями, снова откройте session_1 и session_2 и выберите нужные им данные:

-- SESSION_1，选定 SCORE = 100 的数据
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 100 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  1 |   100 | NULL  |
+----+-------+-------+
1 row in set

-- -----------------新窗口----------------- --

-- SESSION_2，选定 SCORE = 0 的数据
mysql> BEGIN;
SELECT t.* FROM `test1` t WHERE t.`SCORE` = 0 FOR UPDATE;
Query OK, 0 rows affected

+----+-------+-------+
| ID | SCORE | LEVEL |
+----+-------+-------+
|  2 |     0 | NULL  |
+----+-------+-------+
1 row in set

session_1 успешно обновил данные:

mysql> UPDATE `test1` SET `LEVEL` = 1 WHERE `SCORE` = 100;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings:0

Одна и та же структура таблицы, одна и та же операция, два разных результата — это неожиданно. Первый результат заставляет людей думать, что «обычный индекс» вызывает блокировку таблицы, а второй результат отменяет первый.Единственная разница между двумя операциями заключается в «частоте повторения значения» атрибута индекса. Согласно методу доказательства с одной переменной можно сделать вывод, что:Когда «частота повторения значений» низкая, даже близкая к эффекту первичного ключа или уникального индекса, «обычный индекс» по-прежнему является блокировкой строки; когда «частота повторения значений» высока, MySQL не будет обрабатывать это. обычный индекс» в качестве индекса. Выполняется SQL без индекса, и в это время срабатывает блокировка таблицы.

Возьмите каштан:

  1. У пользователя А на банковской карте 100 юаней, в определенный момент пользователь Б переводит А 50 юаней (операция Б), и в то же время пользователь С переводит 50 юаней А (операция С);
  2. Операция B считывает его текущий баланс 100 из базы данных и вычисляет новый баланс как 100+50=150.
  3. Операция C также считывает его текущий баланс 100 из базы данных и вычисляет новый баланс как 100+50=150.
  4. Операция B записывает в базу данных balance=150, а операция C также записывает в базу данных balance=150.
  5. Окончательный баланс А становится 150

В приведенном выше примере А получает два перевода по 50 юаней одновременно, и окончательный баланс должен быть 200 юаней, но он становится 150 юаней из-за проблемы параллелизма.Причина в том, что, когда B и C инициируют запросы на перевод к A , две транзакции открываются одновременно.В сеансе базы данных выполняются две транзакции, и последняя транзакция получает данные промежуточного состояния предыдущей транзакции, что приводит к потере обновлений.
Есть два распространенных решения:

  • блокировать синхронное выполнение
  • Проверяйте согласованность данных перед обновлением

Следует отметить, что как пессимистичные блокировки, так и оптимистичные блокировки являются определениями уровней бизнес-логики, и разные конструкции могут иметь разные реализации. Обычно используемая реализация пессимистической блокировки на уровне MySQL заключается в добавлении монопольной блокировки.

Однако это не так, фактически добавляются данные с монопольной блокировкой, и другие транзакции не могут заблокировать данные, пока блокировка не будет снята (транзакция не завершится). Причина, по которой монопольные блокировки могут препятствовать обновлению, удалению и другим операциям, заключается в том, что операции обновления и удаления автоматически добавляют монопольные блокировки. То есть, даже если добавлена ​​монопольная блокировка, операция выбора не может быть заблокирована.Синтаксис select XX for update может добавить монопольную блокировку к операции select.Таким образом, чтобы предотвратить потерю обновлений, вы можете добавить блокировку обновления при выборе, чтобы вы могли предотвратить выбор для обновления других транзакций.(но обратите внимание, что выбор не может быть заблокирован)

Пример оптимистичной блокировки:

begin;
select balance from account where id=1;
-- 得到balance=100;然后计算balance=100+50=150
update account set balance = 150 where id=1 and balance = 100;
commit;

Как и выше, если sql обнаружит, что затронутое обновление равно 0 в процессе выполнения, это означает, что баланс не равен 100, то есть данные были изменены другими транзакциями.В это время бизнес может вернуться к сбой или повторный выбор и перерасчет

Если я откатываюсь, почему только некоторые операторы обновления терпят неудачу, а не все обновления во всей транзакции?

Это потому, что наш innodb автоматически фиксируется по умолчанию:
Следует отметить, что обычно существует другая ситуация, которая также может привести к откату некоторых операторов, что требует особого внимания. В innodb есть параметр: innodb_rollback_on_timeout

show VARIABLES LIKE 'innodb_rollback_on_timeout'
+----------------------------+---------+
| Variable_name              | Value   |
|----------------------------+---------|
| innodb_rollback_on_timeout | OFF     |
+----------------------------+---------+

Официальный мануал описывает это так:
В MySQL 5.1 InnoDB по умолчанию откатывает только последний оператор по тайм-ауту транзакции.Если указано –innodb_rollback_on_timeout, тайм-аут транзакции заставляет InnoDB прервать и откатить всю транзакцию (такое же поведение, как в MySQL 4.1).Эта переменная была добавлено в MySQL 5.1.15.

Объяснение: Если этот параметр закрыт или не существует, по истечении времени ожидания будет откатан только последний запрос транзакции. Если он открыт, по истечении времени ожидания транзакции будет выполнен откат всей транзакции.

Уведомление:

  • Откат вставки, обновления, замены в тупиковой ситуации MySQL не будет записывать оператор DML в binlog по умолчанию, и не будет журнала отката и ошибки.Если код возврата jdbc не обработан, задачи вычисления больших данных, такие как Mapreduce и hive, будут Это показывает, что успех привел к успешному выполнению части вставки и обновления, а части — к сбою, но вы можете увидеть журнал отката базы данных из SHOW ENGINE INNODB STATUS\G. В этом случае рекомендуется добавить механизм повторных попыток или создать исключение/ошибку на основе кода ошибки jdbc или SQLException.
  • В транзакционной системе взаимоблокировки существуют, и их нельзя полностью избежать. InnoDB автоматически обнаруживает взаимоблокировки транзакций, немедленно откатывает одну из транзакций и возвращает ошибку. Он выбирает самую простую (наименее дорогую) транзакцию для отката в соответствии с некоторым механизмом. Не беспокойтесь о взаимоблокировках, которые случаются время от времени, но обращайте внимание на частые взаимоблокировки. Механизм хранения InnoDB имеет поток мониторинга фоновой блокировки, который ищет возможные проблемы взаимоблокировки и автоматически информирует пользователя.

Как уменьшить вероятность взаимоблокировки innodb?

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

  • Попробуйте использовать более низкий уровень изоляции.Например, если происходит блокировка пробела, вы можете изменить уровень изоляции транзакции сеанса или транзакции на уровень RC (чтение зафиксировано), чтобы избежать этого, но вам нужно установить binlog_format в строку или смешанный формат.
  • Тщательно разработайте индекс и попытайтесь использовать индекс для доступа к данным, чтобы сделать блокировку более точной, тем самым уменьшив вероятность конфликта блокировок;
  • Выберите разумный размер транзакции, и вероятность конфликтов блокировок в небольших транзакциях также будет меньше;
  • При явной блокировке набора записей лучше всего за один раз запросить достаточный уровень блокировки. Например, если вы хотите изменить данные, лучше применить эксклюзивную блокировку напрямую, а не сначала применять общую блокировку, а затем запрашивать эксклюзивную блокировку при изменении, что может привести к взаимоблокировке;
  • Когда разные программы обращаются к набору таблиц, они должны попытаться согласовать доступ к таблицам в одном и том же порядке.Для таблицы доступ к строкам в таблице должен осуществляться в максимально возможном фиксированном порядке. Это может значительно снизить вероятность взаимоблокировки;

пример:

DELETE FROM onlineusers WHERE datetime <= now() - INTERVAL 900 SECOND
至
DELETE FROM onlineusers WHERE id IN (SELECT id FROM onlineusers
    WHERE datetime <= now() - INTERVAL 900 SECOND order by id) u;

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

Странный тайм-аут ожидания блокировки

# 默认 lock 超时时间 50s，这个时间真心不短了
show variables like 'innodb_lock_wait_timeout';
+--------------------------+---------+
| Variable_name            |   Value |
|--------------------------+---------|
| innodb_lock_wait_timeout |      50 |
+--------------------------+---------+

И на этот разSHOW ENGINE INNODB STATUS\GИнформации о взаимоблокировках не было, и тогда я обратил свое внимание на журнал MySQL-сервера, надеясь увидеть, что данные делали до и после этого времени из журнала. Вот краткое введение в состав файловой системы журнала MySQL:

  1. журнал ошибок: регистрирует проблемы при запуске, работе или остановке mysqld, включен по умолчанию.
  2. общий журнал: общий журнал запросов, который записывает все операторы и команды.При открытии базы данных будет потеря производительности около 5%.
  3. Журнал binlog: двоичный формат, записывает все операторы, которые изменяют данные, в основном используется для подчиненной репликации и восстановления данных.
  4. медленный журнал: регистрируйте все запросы, которые выполняются дольше, чем long_query_time секунд, или запросы, которые не используют индексы, отключены по умолчанию.
  5. Журнал Innodb: журнал повторов innodb, журнал отмены, используемый для восстановления данных и отмены операций.

Как видно из приведенного выше вступления, текущий лог этой проблемы может быть в 2 и 4. Если нет лога в 4, то можно включить только 2, но 2 имеет некоторый проигрыш в производительности БД , Поскольку это полный журнал, сумма очень большая, поэтому вы должны быть осторожны при открытии:

-- general_log 日志默认关闭，开启会影响数据库 5% 左右性能：
show variables like 'general%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
|------------------+---------------------------------|
| general_log      | OFF                             |
| general_log_file | /opt/data/mysql/tjtx-103-26.log |
+------------------+---------------------------------+

-- 全局 session 级别开启：
set global general_log=1

-- 如果需要对当前 session 生效需要：
set general_log=1

-- set 指令设置的动态参数在 MySQL 重启后失效，如果需要永久生效需要在 /etc/my.cnf 中配置静态变量/参数。
-- 如果不知道 my.cnf 位置，可以根据 mysql -? | grep ".cnf" 查询
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Динамические параметры, установленные командой set, станут недействительными после перезапуска MySQL.Если они должны действовать постоянно, вам необходимо настроить статические переменные/параметры в /etc/my.cnf.

Для получения дополнительной информации см.
Настоятельно рекомендуется — технический блог He Dengcheng
Он Дэнчэн, старший технический эксперт, руководитель основной группы базы данных Alibaba, статья очень интересная.глубина

Анализ проблемы взаимоблокировки mysql
Вставить, обновить, удалить блокировки в mysql
Блокировки MySQL InnoDB — официальная документация

Суммировать

Есть два способа выйти из тупиковой ситуации:

  1. Запрос на блокировку таблицыshow OPEN TABLES where In_use > 0;
  2. Процессы запросов (если у вас есть привилегия SUPER, вы можете видеть все потоки. В противном случае вы можете видеть только свои собственные потоки)
    show processlist
  3. Убейте идентификатор процесса (то есть столбец идентификатора вышеуказанной команды)
    kill id

Секунда:

  1. Просмотр текущих транзакций
    SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
  2. Просмотр заблокированных транзакций
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
  3. Просмотр текущих транзакций, ожидающих блокировки
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    убить процесс
    kill 进程ID 
# 通过检查 InnoDB_row_lock 状态变量分析系统上中行锁的争夺情况
show status like 'innodb_row_lock%';```

# 查看加锁情况 
show open tables where in_use > 0;

#具体使用说明可查看上文内容
show status like 'table_locks%';
show VARIABLES LIKE 'innodb_rollback_on_timeout';
show variables like 'general%';

Категории