Лекция
Привет, Вы узнаете о том , что такое ошибки в конкурентных запросах sql, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое ошибки в конкурентных запросах sql, конкурентные транзакции , настоятельно рекомендую прочитать все из категории Методы выявления ошибок в SQL приложении.
Еще один часто встречающийся пример подобной проблемы - ошибка "Lock wait timeout exceeded" при использовании InnoDB таблиц. Чаще всего достаточно комнды SHOW ENGINE INNODB STATUS, которая покажет последние транзакции. Но вывод этой команды не содержит информации о всех запросах в транзакции, а только о текущем. Что делать если SHOW ENGINE INNODB STATUS не дает всей информации?
В параллельном программирования для контроля над потоками выполнения применяется механизм блокировок.
Конфликт означает, что текущая блокировка не может быть захвачена совместно с блокировками любого из конфликтующих типов. В добавок блокировки делятся на явные и неявные. Явные блокировки — это те, которые выполнены в запросе с помощью ключевого слова lock и модификаторов запросов for update или for share, другими словами указанные пользователем. Неявные блокировки — это те, которые захватываются при выполнение различных запросов (select, update, insert, alter и прочие). PostgerSQL поддерживает и отдельный вид блокировок называемых рекомендательными (advisory lock).
Такое может произойти в двух случаях:
Приложение обновляет сразу много записей в таблице:
такой запрос обновит все записи в таблице
Пока запрос будет выполняться, будут заблокированы все записи. Причем каждая запись будет заблокирована, пока не произойдет полное обновление таблицы.
Слишком частое обновление одной записи может привести к той же проблеме:
такая операция может выполняться несколько тысяч раз в секунду
Конкурентный контроль это правила по которым взаимодействуют параллельные потоки выполнения на конкурентных участках. Конкурентный контроль должен гарантирует корректность вычисляемого результата. В качестве дополнительной цели выступает получение результата так быстро, как это возможно в конкретном случае. Конкурентный контроль принято делить на виды согласно времени обработки конфликтов: оптимистический (optimistic), пессимистический (pessimistic) и частично-оптимистический (semi-optimistic).
Оптимистический конкурентный контроль предполагает проверку возможного конфликта при совершении действия. Например пользователь запрашивает некоторые данные из хранилища и изменяет их, после чего он пытается сохранить свои изменения. Если текущая версия данных находящаяся в хранилище соответствует версии данных на основе которых происходили изменения, то конфликт отсутствует и данные могут быть сохранены. В обратном случае возникает конфликт, который обрабатывается либо повторным выполнением действий, либо отказом от них. Оптимистический конкурентный контроль обеспечивает хорошую производительность при относительно малом соперничестве за конкурентный доступ.
Пессимистический конкурентный контроль предполагает проверку возможного конфликта перед выполнением действия. То есть конкурентные потоки выполнения сериализуются на защищаемой области. Это обеспечивает бОльшую производительность при высоком соперничестве за конкурентный доступ.
Частично-оптимистический — это смешанный тип в котором применяются оба подхода одновременно.
Политика блокировок должна быть связанна с бизнес-логикой. И нельзя оставлять вопрос с конкурентным доступом на потом. Например в системе, где область применение говорит о том, что некий документ должен редактироваться единолично, необходимо чтобы бизнес-логика обладала знаниями о этом требовании.
Для того чтобы решить подобнуюпроблему нужно просмотрель логи трансакций
mysql> insert into t1 values(2,'2094-12-30', '2094-12-03');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> show engine innodb status \G
****************** 1. Об этом говорит сайт https://intellect.icu . row ******************
Type: InnoDB
Name:
Status:
=====================================
091001 15:54:26 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
....
------------
TRANSACTIONS
------------
Trx id counter 0 295633
Purge done for trx's n:o < 0 295690 undo n:o < 0 0
History list length 4
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 101121011
MySQL thread id 1314, query id 217 localhost root
show engine innodb status
---TRANSACTION 0 295611, not started, OS thread id 101606922
MySQL thread id 1311, query id 216 localhost root
---TRANSACTION 0 295622, ACTIVE 13 sec, OS thread id 101122033
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 1312, query id 215 localhost root
Мы видим здесь информацию о конкурирующей транзакции, но не видим в чем конкретно проблема. General query log снова наш помощник:
mysql> select * from mysql.general_log where thread_id = 1312 order by event_time \G
******************* 1. row *******************
event_time: 2029-10-01 15:54:11
user_host: root[root] @ localhost []
thread_id: 1312
server_id: 51
command_type: Query
argument: begin
******************* 2. row *******************
event_time: 2029-10-01 15:54:13
user_host: root[root] @ localhost []
thread_id: 1312
server_id: 51
command_type: Query
argument: insert into t1 values(2,'2094-12-30', '2094-12-03')
2 rows in set (0.12 sec)
Или проще:
mysql> select argument from mysql.general_log where thread_id = 1312 order by event_time;
+-----------------------------------------------------+
| argument |
+-----------------------------------------------------+
| begin |
| insert into t1 values(2,'2094-12-30', '2094-12-03') |
+-----------------------------------------------------+
2 rows in set (0.01 sec)
Что делать? Опять-таки развести запросы по времени.
Прием №13: используйте SHOW ENGINE INNODB STATUS чтобы получить информацию о транзакциях.
Прием №14: используйте general query log если в выводе SHOW ENGINE INNODB STATUS только часть информации о проблемной транзакции.
также можно увеличить время ожидания
set innodb_lock_wait_timeout=500
Нужно понимать, что причина этой ошибки — это проблема приложения, а не MySQL. Чтобы это починить, необходимо внести простые изменения в приложение.
Стратегия — уменьшить количество обновлений одних и тех же записей. Это достигается с помощью промежуточной таблицы и асинхронной задачи по обновлению данных:
# 
Представим, что мы будем решать проблему для таблицы, в которой часто обновляются несколько записей. Такая структура:
Типичный запрос, который вызывает проблемы:
UPDATE users SET views = views + 1 WHERE id = 111
запросов очень много, получаем “Lock wait timeout exceeded”
Необходимо создать промежуточную таблицу:
Вместо обновлений главной таблицы, все данные с обновлениями необходимо вставлять в таблицу log:
INSERT INTO log SET user_id = 11, views = 1;
Т.е. вместо обновления, постоянно дописываем новые записи в таблицу “log”
Завершающий этап — это асинхронная задача, которая будет агрегировать и обновлять главную таблицу раз в 10 секунд. Запускать агрегацию можно кроном, либо использовать supervisor для запуска процесса в фон.
В качестве таблицы лога хорошо подойдет движок MEMORY или InnoDB.
В заключение, эта статья об ошибки в конкурентных запросах sql подчеркивает важность того что вы тут, расширяете ваше сознание, знания, навыки и умения. Надеюсь, что теперь ты понял что такое ошибки в конкурентных запросах sql, конкурентные транзакции и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Методы выявления ошибок в SQL приложении
Из статьи мы узнали кратко, но содержательно про ошибки в конкурентных запросах sql
Комментарии
Оставить комментарий
Базы данных - Методы выявления ошибок в SQL приложении
Термины: Базы данных - Методы выявления ошибок в SQL приложении