|
|
|
Deadlock при тяжелом SELECT
|
|||
|---|---|---|---|
|
#18+
Доброго времени суток уважаемые форумчане. Возникла такая проблема. Необходимо рассчитывать статистику на боевой базе. В принципе все более менее понятно, но... Если Клиент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 ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 08.04.2015, 06:15:20 |
|
||
|
Deadlock при тяжелом SELECT
|
|||
|---|---|---|---|
|
#18+
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. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 08.04.2015, 08:36:57 |
|
||
|
Deadlock при тяжелом SELECT
|
|||
|---|---|---|---|
|
#18+
tanglir, Нет уровень изоляции транзакций используется по умолчанию REPEATABLE-READ ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 08.04.2015, 08:47:05 |
|
||
|
Deadlock при тяжелом SELECT
|
|||
|---|---|---|---|
|
#18+
Jura.KЕсли Клиент1 начинает формировать статистику т.е. выполняется сложный SELECT по большой таблице, с LEFT JOINами, а в это время Клиент2 в транзакции вставляет новую запись и делает UPDATE по 10 записям этой таблицы то при COMMITe получаем DEADLOCK. Такого на самом деле просто не может быть. SELECT (без FOR UPDATE) не может блокировать UPDATE в InnoDB. А UPDATE не может блокировать SELECT. Так что дедлока между ними быть не может. Видимо, есть ещё какой-то другой процесс, который также меняет данные и работает с ними параллельно. Ещё возможно, что в той же транзакции, где работает SELECT ранее него проходил какой-то update или delete, и от него висят блокировки. В любом случае, тебе нужно дополнительно исследовать deadlock показать определения всех таблиц и все запросы, проходящие в двух сессиях от начала транзакций. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 08.04.2015, 09:30:46 |
|
||
|
Deadlock при тяжелом SELECT
|
|||
|---|---|---|---|
|
#18+
Jura.K, Ну и ещё -- я сейчас не нашёл подтверждения этому, но насколько я помню, графы блокировок InnoDB не строит, а использует таймауты, поэтому настоящие deadlock-и не отличимы на самом деле от просто таймаутов на получение блокировок, так что вполне возможно, что это не дедлок вообще, а таймаут. Это надо учитывать при анализе ситуации. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 08.04.2015, 09:38:38 |
|
||
|
Deadlock при тяжелом SELECT
|
|||
|---|---|---|---|
|
#18+
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 ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 08.04.2015, 11:58:42 |
|
||
|
Deadlock при тяжелом SELECT
|
|||
|---|---|---|---|
|
#18+
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. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 08.04.2015, 12:40:21 |
|
||
|
Deadlock при тяжелом SELECT
|
|||
|---|---|---|---|
|
#18+
Jura.Kт.е. идет выполнение сложного SELECTа и запись результата во временную таблицу. В это время попытка UPDATE по таблице по которой делается запрос вываливается DEADLOCK Это не SELECT, это -- INSERT. Так что теперь всё понятно. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 08.04.2015, 13:30:21 |
|
||
|
|
start [/forum/topic.php?fid=47&msg=38930038&tid=1833338]: |
0ms |
get settings: |
7ms |
get forum list: |
10ms |
check forum access: |
2ms |
check topic access: |
2ms |
track hit: |
33ms |
get topic data: |
6ms |
get forum data: |
1ms |
get page messages: |
28ms |
get tp. blocked users: |
1ms |
| others: | 195ms |
| total: | 285ms |
| 0 / 0 |
