Início / dba / Perguntas / 9592
Accepted
quanta
Asked: 2011-12-23 03:05:09 +0800 CST

Impasse do MySQL - não pode reiniciar normalmente?

  • 772

MySQL versão 5.5.13-1

Um trecho do SHOW ENGINE INNODB STATUS\G:

LATEST DETECTED DEADLOCK
------------------------
111218 10:22:34
*** (1) TRANSACTION:
TRANSACTION 1318D95B, ACTIVE 0 sec starting index read
mysql tables in use 6, locked 6
LOCK WAIT 53 lock struct(s), heap size 14776, 77 row lock(s)
MySQL thread id 60933, query id 124472414 192.168.6.31 thanhnt Copying to tmp table
INSERT INTO usertmp(userid,npayvalue,balance)       
    SELECT B.`userid`, SUM(C.`moneyv`) a,(B.`balance` + B.`promotions`+ B.`promotions1`+ B.`overdraft`) b
    FROM `ox_campaigns` A
    INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
    INNER JOIN `selfserving_users_balances` B ON B.`userid` = A.`uid`
    INNER JOIN `selfserving_users` F ON F.`user_id` = B.`userid`
    INNER JOIN `selfserving_users_group_balances` E ON E.`groupid` = F.`groupid` AND E.`ispostpaid` = 0
    WHERE A.`revenue_type` = 6 AND C.`dt` BETWEEN A.`activate` AND A.`expire` AND C.`dt` >=  (SELECT DATE_ADD( A.ssv_payment_date_lastest, INTERVAL 1 DAY)  FROM `000_sys_params_v3` A)
    AND C.`viewcharge` >0 
    GROUP BY B.`userid`
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 222397 page no 2576 n bits 304 index `GEN_CLUST_INDEX` of table `reportingdb`.`v3_cam_date` /* Partition `p3` */ trx id 1318D95B lock mode S locks rec but not gap waiting
Record lock, heap no 170 PHYSICAL RECORD: n_fields 15; compact format; info bits 0
 0: len 6; hex 0000a61821ff; asc     ! ;;
 1: len 6; hex 00000b168ac0; asc       ;;
 2: len 7; hex 5200035d760432; asc R  ]v 2;;
 3: len 3; hex 8f661d; asc  f ;;
 4: len 3; hex 0000ac; asc    ;;
 5: len 4; hex 000cc08c; asc     ;;
 6: len 3; hex 0000ac; asc    ;;
 7: len 3; hex 000000; asc    ;;
 8: len 4; hex 000852a6; asc   R ;;
 9: len 3; hex 0519d2; asc    ;;
 10: len 3; hex 00009b; asc    ;;
 11: len 3; hex 8fb747; asc   G;;
 12: len 3; hex 800000; asc    ;;
 13: len 4; hex 00000000; asc     ;;
 14: len 4; hex 00249eda; asc  $  ;;

    ...

*** (2) TRANSACTION:
TRANSACTION 1318D958, ACTIVE 1 sec inserting, thread declared inside InnoDB 123
mysql tables in use 3, locked 3
4808 lock struct(s), heap size 555448, 1122860 row lock(s), undo log entries 7058
MySQL thread id 130380, query id 124472416 192.168.6.31 thanhnt
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date FIELDS TERMINATED BY  ','
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 222397 page no 2576 n bits 304 index `GEN_CLUST_INDEX` of table `reportingdb`.`v3_cam_date` /* Partition `p3` */ trx id 1318D958 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 222397 page no 6296 n bits 1000 index `ix_campaignid_dt` of table `reportingdb`.`v3_cam_date` /* Partit
ion `p3` */ trx id 1318D958 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 213 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 3; hex 8f63a5; asc  c ;;
 1: len 3; hex 8fb73b; asc   ;;;
 2: len 6; hex 0000a617374f; asc     7O;;

*** WE ROLL BACK TRANSACTION (1)

Em algum momento, ele combina com um enorme "Waiting for global read lock" no SHOW FULL PROCESSLIST\Ge meu banco de dados está completamente bloqueado. Não é possível reiniciar com o script init, então devo fazer um kill -9 <PID>(isso causa muitas tabelas travadas).

O InnoDB Lock Monitor foi ativado, siga este guia. information_schema.innodb_locksestá vazio e abaixo está a innodb_trxtabela:

*************************** 1. row ***************************
                    trx_id: 135937A0
                 trx_state: RUNNING
               trx_started: 2011-12-19 11:16:04
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 43929
       trx_mysql_thread_id: 257731
                 trx_query: INSERT INTO `__tZoneTempTable` (zoneid, rc, v, dt)  
    (SELECT a.`zoneid`, sum(a.`realclick`) as rc, sum(a.`totalview`) as v , a.`dt` FROM `v3_zone_date` a INNER JOIN `ox_zones` b ON a.`zoneid` = b.`zoneid` where b.`is_smallbiz` = 3 AND b.`deleted` = 0 AND a.`zoneid` > 0 AND a.`dt` =  NAME_CONST('_date',_latin1'2011-12-17' COLLATE 'latin1_swedish_ci') GROUP BY a.`zoneid`)
       trx_operation_state: fetching rows
         trx_tables_in_use: 10
         trx_tables_locked: 9
          trx_lock_structs: 43929
     trx_lock_memory_bytes: 6093240
           trx_rows_locked: 15961781
         trx_rows_modified: 0
   trx_concurrency_tickets: 359
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
                    trx_id: 13590BD9
                 trx_state: RUNNING
               trx_started: 2011-12-19 11:15:23
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 43929
       trx_mysql_thread_id: 257456
                 trx_query: INSERT INTO `__tZoneTempTable` (zoneid, rc, v, dt)  
    (SELECT a.`zoneid`, sum(a.`realclick`) as rc, sum(a.`totalview`) as v , a.`dt` FROM `v3_zone_date` a INNER JOIN `ox_zones` b ON a.`zoneid` = b.`zoneid` where b.`is_smallbiz` = 3 AND b.`deleted` = 0 AND a.`zoneid` > 0 AND a.`dt` =  NAME_CONST('_date',_latin1'2011-12-17' COLLATE 'latin1_swedish_ci') GROUP BY a.`zoneid`)
       trx_operation_state: fetching rows
         trx_tables_in_use: 10
         trx_tables_locked: 9
          trx_lock_structs: 43929
     trx_lock_memory_bytes: 6093240
           trx_rows_locked: 15961781
         trx_rows_modified: 0
   trx_concurrency_tickets: 359
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
*************************** 3. row ***************************
                    trx_id: 1358EA92
                 trx_state: RUNNING
               trx_started: 2011-12-19 11:14:57
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 257363
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000

Como sugeriu @Chris, gostaria de fornecer as SHOW CREATE TABLEsaídas:

mysql> show create table v3_cam_date\G
*************************** 1. row ***************************
       Table: v3_cam_date
Create Table: CREATE TABLE `v3_cam_date` (
  `campaignid` mediumint(9) NOT NULL DEFAULT '0',
  `totalclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `totalview` int(11) unsigned NOT NULL DEFAULT '0',
  `realclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `clickcharge` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `viewcharge` int(11) unsigned NOT NULL DEFAULT '0',
  `uv` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `uc` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `dt` date NOT NULL DEFAULT '0000-00-00',
  `ctr` decimal(5,3) NOT NULL DEFAULT '0.000' COMMENT '=-1: meaning not available(N/A)',
  `moneyc` int(11) unsigned NOT NULL DEFAULT '0',
  `moneyv` int(11) unsigned NOT NULL DEFAULT '0',
  KEY `ix_campaignid_dt` (`campaignid`,`dt`),
  KEY `ix_dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (734502) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (734683) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Essa situação de impasse acontece com frequência. Quero descobrir o que causa isso para evitar no futuro.

mysql innodb

2 respostas

  1. Best Answer

    Esse impasse específico está sendo causado pela tentativa de leitura de sua v3_cam_datetabela para inserir linhas em sua usertmptabela enquanto outro thread está atualizando a v3_cam_datetabela. Indicado por esta declaração:

    LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date FIELDS TERMINATED BY  ','

    Esta página de documentação tem algumas etapas úteis para lidar com impasses, mas talvez a dica mais útil em sua situação seja usar o nível de isolamento de transação READ COMMITTED ao fazer sua inserção na usrtmptabela.

    Isso basicamente significa que o que você está selecionando são apenas os valores confirmados, não quaisquer alterações que estejam acontecendo em sua LOAD DATA INFILEdeclaração se ela não tiver concluído a confirmação.

    Se isso for aceitável para você, algo assim pode funcionar * :

    -- SET SESSION TO READ COMMITTED --
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- ISSUE THE INSERT...SELECT --
INSERT INTO usertmp(userid,npayvalue,balance)       
SELECT B.`userid`, SUM(C.`moneyv`) a,(B.`balance` + B.`promotions`+ B.`promotions1`+ B.`overdraft`) b
FROM `ox_campaigns` A
INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
INNER JOIN `selfserving_users_balances` B ON B.`userid` = A.`uid`
INNER JOIN `selfserving_users` F ON F.`user_id` = B.`userid`
INNER JOIN `selfserving_users_group_balances` E ON E.`groupid` = F.`groupid` AND E.`ispostpaid` = 0
WHERE A.`revenue_type` = 6 AND C.`dt` BETWEEN A.`activate` AND A.`expire` AND C.`dt` >=  (SELECT DATE_ADD( A.ssv_payment_date_lastest, INTERVAL 1 DAY)  FROM `000_sys_params_v3` A)
AND C.`viewcharge` >0 
GROUP BY B.`userid`

-- SET BACK TO 'DEFAULT' --
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    * Observe que eu não tentei pessoalmente o procedimento acima para garantir que READ COMMITTED interromperá o impasse.

    Mas quanto tempo leva para carregar este arquivo? Você pode adicionar um bloqueio de tabela ao redor do LOAD DATA INFILE, mas é claro que isso fará com que INSERTo arquivo seja carregado o tempo todo.

    • 4

  2. EXPLICAÇÃO DE IMPACTO

    Se as tabelas subjacentes usarem o mecanismo de armazenamento InnoDB , os bloqueios de linha (mesmo na mesma linha) nunca poderão bloquear as leituras, mas os bloqueios ainda serão possíveis durante as gravações. Com AUTOCOMMIT=0definido em /etc/my.cnf, cada instrução DML (INSERT,UPDATE e DELETE) será executada como uma transação de linha única. Bloqueios de linha individuais são emitidos. Assim, 50 conexões de banco de dados podem ir depois de 50 linhas diferentes e nada de trágico acontece.

    Onde podem entrar os impasses?

    Uma vez que a PRIMARY KEY das tabelas InnoDB está contida no Clustered Index (internamente conhecido como gen_clust_index) , os dados da linha são fortemente acoplados às entradas do índice. Qualquer índice feito em colunas que não fazem parte da CHAVE PRIMÁRIA são catalogados com dois itens básicos, o valor da coluna e a chave gen_clust_index. Às vezes, as colunas de índices de atualização no InnoDB podem causar o que chamo de brincadeira de constipação de índice. Isso ocorre quando dois ou mais bloqueios são gerados em entradas de índice armazenadas próximas umas das outras. Isso é possível em um site com muito tráfego.

    Certa vez, ajudei um desenvolvedor a ver por que isso pode acontecer no DBA StackExchange. Esse desenvolvedor fez alterações no código posteriormente. Aqui estavam essas postagens:

    SUA PERGUNTA

    No snippet que você forneceu, você tem isto:

    /* Partition p3 */ trx id 1318D95B lock mode S locks rec but not gap waiting

    Esta mensagem mostra uma Linha Compartilhada Bloqueada (modo de bloqueio S) nesta Partição. Em algum lugar do SHOW ENGINE INNODB STATUS\G, você também deve ter visto outro Row Lock marcado como Exclusive (lock mode X).

    • 2

relate perguntas