Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Deadlock при тяжелом SELECT / 9 сообщений из 9, страница 1 из 1
08.04.2015, 06:15:20
    #38929965
Jura.K
Jura.K
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock при тяжелом SELECT
Доброго времени суток уважаемые форумчане.
Возникла такая проблема. Необходимо рассчитывать статистику на боевой базе.
В принципе все более менее понятно, но...
Если Клиент1 начинает формировать статистику т.е. выполняется сложный SELECT по большой таблице, с LEFT JOINами, а в это время Клиент2 в транзакции вставляет новую запись и делает UPDATE по 10 записям этой таблицы то при COMMITe получаем DEADLOCK.

Собственно вопрос, как в такой ситуации избавиться от DEADLOCK, можно ли обыграть эту ситуацию уровнем изоляции транзакций?
Или может быть SELECT сделать с опцией FOR UPDATE, в этом случае Клиент2 будет ждать когда выполнится SELECT или я ошибаюсь?
Или переделывать ПО что бы Клиент2 по циклу пытался запускать COMMIT при получении ошибки DEADLOCKa.

p.s.Допустимо при рассчете статистики не брать последние несколько секунд в тяжелый SELECT.
Для UPDATE (Клиент2) все индексы есть, для SELECT индекс тоже есть, но там используется LEFT JOIN и в условии по OR проверяется отсутствие записи во второй таблице.
INNODB, mySQL 5.6.23
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2015, 08:36:57
    #38929990
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock при тяжелом SELECT
Jura.KИли может быть SELECT сделать с опцией FOR UPDATE, в этом случае Клиент2 будет ждать когда выполнится SELECT или я ошибаюсь?Наоборот, фор апдейт не даст вашему второму апдейту выполниться (если пересекутся по записям)
авторUse less locking. If you can afford to permit a SELECT to return data from an old snapshot, do not add the clause FOR UPDATE or LOCK IN SHARE MODE to it. Using the READ COMMITTED isolation level is good here, because each consistent read within the same transaction reads from its own fresh snapshot. You should also set the value of innodb_support_xa to 0, which will reduce the number of disk flushes due to synchronizing on disk data and the binary log.Я так понимаю, сейчас у вас везде SERIALIZABLE. Попробуйте свой отчёт собирать в READ COMMITTED.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2015, 08:47:05
    #38929994
Jura.K
Jura.K
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock при тяжелом SELECT
tanglir,

Нет уровень изоляции транзакций используется по умолчанию
REPEATABLE-READ
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2015, 09:30:46
    #38930032
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock при тяжелом SELECT
Jura.KЕсли Клиент1 начинает формировать статистику т.е. выполняется сложный SELECT по большой таблице, с LEFT JOINами, а в это время Клиент2 в транзакции вставляет новую запись и делает UPDATE по 10 записям этой таблицы то при COMMITe получаем DEADLOCK.

Такого на самом деле просто не может быть.
SELECT (без FOR UPDATE) не может блокировать UPDATE в InnoDB.
А UPDATE не может блокировать SELECT.
Так что дедлока между ними быть не может.

Видимо, есть ещё какой-то другой процесс, который также меняет данные и работает с ними параллельно.

Ещё возможно, что в той же транзакции, где работает SELECT ранее него проходил какой-то update или delete,
и от него висят блокировки.


В любом случае, тебе нужно
дополнительно исследовать deadlock

показать определения всех таблиц и все запросы, проходящие в двух сессиях от начала транзакций.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2015, 09:38:38
    #38930038
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock при тяжелом SELECT
Jura.K,

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

Это надо учитывать при анализе ситуации.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2015, 11:58:42
    #38930232
Jura.K
Jura.K
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock при тяжелом SELECT
MasterZiv,
Мда немного ввел в заблуждение
В принципе конечно не чистое чтение данных, а чтение и вставку данных во временную таблицу.
вот что дает SHOW ENGINE INNODB STATUS
 
------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-04-08 10:39:18 141c
*** (1) TRANSACTION:
TRANSACTION 1616197, ACTIVE 5 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 1195 lock struct(s), heap size 128552, 75289 row lock(s)
MySQL thread id 13957, OS thread handle 0x13c0, query id 2680932 10.5.15.247 TESTUSER Sending data
INSERT INTO base_test_rep.RepTE2 SELECT FE2ID, FE2FE2OID, FE2FE1Par FROM (SELECT FE2ParId, MAX(FE2OrdNum) AS mNum FROM TE2 WHERE FE2Status IN ('1', '2') AND FE2vNum <= 6.19804000000000000e+006 AND FE2R00P1K <= '20150408000000' GROUP BY FE2ParId) AS _TE2 INNER JOIN TE2 ON _TE2.FE2ParId = TE2.FE2ParId AND mNum = FE2OrdNum WHERE FE2Delete <> 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 95 page no 1816 n bits 360 index `FE2ParId` of table `base_test`.`te2` trx id 1616197 lock mode S waiting
Record lock, heap no 138 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 25; hex 31313030393034424e304d5331484c30303034323931383734; asc 1100904BN0MS1HL0004291874;;
1: len 25; hex 31313030393034424e304d5331484c30303034323931383734; asc 1100904BN0MS1HL0004291874;;

*** (2) TRANSACTION:
TRANSACTION 1616159, ACTIVE 7 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 7
MySQL thread id 13726, OS thread handle 0x141c, query id 2680966 10.2.240.2 TESTUSER updating
UPDATE TE2 SET FE2Status=IF(FE2Status='3','4',IF(FE2Status='5','6','2')) WHERE FE2ParID='1100904BN0MS1HL0004291874' AND FE2Status IN ('1','3','5') AND FE2ID<>'1100904BN0MS1HL0004291874'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 95 page no 1816 n bits 360 index `FE2ParId` of table `base_test`.`te2` trx id 1616159 lock_mode X locks rec but not gap
Record lock, heap no 138 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 25; hex 31313030393034424e304d5331484c30303034323931383734; asc 1100904BN0MS1HL0004291874;;
1: len 25; hex 31313030393034424e304d5331484c30303034323931383734; asc 1100904BN0MS1HL0004291874;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 95 page no 1816 n bits 360 index `FE2ParId` of table `base_test`.`te2` trx id 1616159 lock_mode X waiting
Record lock, heap no 138 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 25; hex 31313030393034424e304d5331484c30303034323931383734; asc 1100904BN0MS1HL0004291874;;
1: len 25; hex 31313030393034424e304d5331484c30303034323931383734; asc 1100904BN0MS1HL0004291874;;

*** WE ROLL BACK TRANSACTION (2)


т.е. идет выполнение сложного SELECTа и запись результата во временную таблицу.
В это время попытка UPDATE по таблице по которой делается запрос вываливается DEADLOCK
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2015, 12:40:21
    #38930316
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock при тяжелом SELECT
http://dba.stackexchange.com/questions/21239/innodb-deadlock-from-one-reader-and-one-writer
авторDue to how MySQL handles replication of INSERT INTO ... SELECT, it will actually do some locking on the rows of the tables involved in the SELECT portion of your query. Here is an older article detailing the issue.

The solution is to use row-based replication and set the transaction level to READ-COMMITTED.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2015, 13:30:21
    #38930416
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock при тяжелом SELECT
Jura.Kт.е. идет выполнение сложного SELECTа и запись результата во временную таблицу.
В это время попытка UPDATE по таблице по которой делается запрос вываливается DEADLOCK

Это не SELECT, это -- INSERT. Так что теперь всё понятно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.04.2015, 14:03:52
    #38930491
Jura.K
Jura.K
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Deadlock при тяжелом SELECT
tanglir,

Спасибо,
попробую. Результаты отпишу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Deadlock при тяжелом SELECT / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 294ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1833338]:
 0ms
get settings:
 4ms
get forum list:
 8ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 36ms
get topic data:
 6ms
get forum data:
 1ms
get page messages:
 25ms
get tp. blocked users:
 1ms
others: 209ms
total:  294ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]