Tenho um deadlock em 10.6.21-MariaDB-ubu2004. Aqui está meu esquema:
CREATE TABLE INT_CHANNEL_MESSAGE
(
MESSAGE_ID CHAR(36) NOT NULL,
GROUP_KEY CHAR(36) NOT NULL,
CREATED_DATE BIGINT NOT NULL,
MESSAGE_PRIORITY BIGINT,
MESSAGE_SEQUENCE BIGINT NOT NULL AUTO_INCREMENT UNIQUE,
MESSAGE_BYTES BLOB,
REGION VARCHAR(100) NOT NULL,
PRIMARY KEY (REGION, GROUP_KEY, CREATED_DATE, MESSAGE_SEQUENCE)
) ENGINE = InnoDB;
CREATE INDEX INT_CHANNEL_MSG_DELETE_IDX ON INT_CHANNEL_MESSAGE (REGION, GROUP_KEY, MESSAGE_ID);
Inicialmente, tenho uma única linha na tabela. Vamos supor que os valores de cada linha sejam únicos, únicos REGIONe GROUP_KEYfixos.
A transação nº 1 insere duas linhas usando INSERTinstruções separadas:
INSERT into INT_CHANNEL_MESSAGE(
MESSAGE_ID,
GROUP_KEY,
REGION,
CREATED_DATE,
MESSAGE_PRIORITY,
MESSAGE_BYTES)
values (?, ?, ?, ?, ?, ?)
A primeira inserção é executada, depois a transação trava. O nível de isolamento para a Transação nº 1 é REPEATABLE_READ(embora tenha tentado mudar para READ_COMMITED).
A transação nº 2 inicia imediatamente após a execução da primeira inserção (da transação nº 1) (é acionada pela aplicação). O nível de isolamento é definido como READ_COMMITED. A linha inicial é selecionada para atualização e, em seguida, a transação trava na DELETEchamada:
SELECT INT_CHANNEL_MESSAGE.MESSAGE_ID, INT_CHANNEL_MESSAGE.MESSAGE_BYTES
from INT_CHANNEL_MESSAGE
where INT_CHANNEL_MESSAGE.GROUP_KEY = ? and INT_CHANNEL_MESSAGE.REGION = ?
order by CREATED_DATE, MESSAGE_SEQUENCE LIMIT 1 FOR UPDATE SKIP LOCKED
DELETE from INT_CHANNEL_MESSAGE where MESSAGE_ID=? and GROUP_KEY=? and REGION=?
SHOW ENGINE INNODB STATUSsaída:
| InnoDB | |
=====================================
2025-04-04 16:06:44 0x7fc6241b3700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 2376 srv_idle
srv_master_thread log flush and writes: 2376
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 1646
Purge done for trx's n:o < 1646 undo n:o < 0 state: running
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1643, ACTIVE 31 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MariaDB thread id 76, OS thread handle 140489014748928, query id 8535 172.21.0.1 nbs
---TRANSACTION 1640, ACTIVE 31 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MariaDB thread id 75, OS thread handle 140488995890944, query id 8537 172.21.0.1 nbs Updating
SET STATEMENT SQL_SELECT_LIMIT=1 FOR DELETE from INT_CHANNEL_MESSAGE where MESSAGE_ID='ce0ce618-2430-0b4c-727b-7250e5388f15' and GROUP_KEY='cb18446f-633c-3a46-b5ac-95ab539126d1' and REGION='DEFAULT'
------- TRX HAS BEEN WAITING 31293172 us FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 3 n bits 320 index PRIMARY of table `nbs_biometric`.`INT_CHANNEL_MESSAGE` trx id 1640 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 7; hex 44454641554c54; asc DEFAULT;;
1: len 30; hex 63623138343436662d363333632d336134362d623561632d393561623533; asc cb18446f-633c-3a46-b5ac-95ab53; (total 36 bytes);
2: len 8; hex 80000196018d82b2; asc ;;
3: len 8; hex 8000000000000012; asc ;;
4: len 6; hex 00000000066b; asc k;;
5: len 7; hex bf000001410110; asc A ;;
6: len 30; hex 63623533326436662d343362352d393164352d636561612d623965616434; asc cb532d6f-43b5-91d5-ceaa-b9ead4; (total 36 bytes);
7: SQL NULL;
8: len 30; hex aced0005737200346f72672e737072696e676672616d65776f726b2e6d65; asc sr 4org.springframework.me; (total 1252 bytes);
------------------
---TRANSACTION (0x7fc6388d7180), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
166 OS file reads, 332 OS file writes, 594 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 891776
Log flushed up to 891776
Pages flushed up to 42676
Last checkpoint at 42664
0 pending log flushes, 0 pending chkp writes
334 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 167772160
Dictionary memory allocated 931712
Buffer pool size 8112
Free buffers 7349
Database pages 763
Old database pages 261
Modified db pages 621
Percent of dirty pages(LRU & free pages): 7.654
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 152, created 611, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 763, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 63, updated 0, deleted 18, read 1182
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
O deadlock não ocorre quando a Transação nº 1 insere uma única linha. Como posso evitar isso?
Estou anexando logs do aplicativo para um contexto mais amplo:
2025-04-04 18:06:13,426 [tx1]: TX START
2025-04-04 18:06:13,426 [tx1]: Executing prepared SQL update
2025-04-04 18:06:13,426 [tx1]: Executing prepared SQL statement [INSERT into INT_CHANNEL_MESSAGE(
MESSAGE_ID,
GROUP_KEY,
REGION,
CREATED_DATE,
MESSAGE_PRIORITY,
MESSAGE_BYTES)
values (?, ?, ?, ?, ?, ?)
]
2025-04-04 18:06:13,427 [tx2]: Executing prepared SQL query
2025-04-04 18:06:13,427 [tx2]: Executing prepared SQL statement [ SELECT INT_CHANNEL_MESSAGE.MESSAGE_ID, INT_CHANNEL_MESSAGE.MESSAGE_BYTES
from INT_CHANNEL_MESSAGE
where INT_CHANNEL_MESSAGE.GROUP_KEY = ? and INT_CHANNEL_MESSAGE.REGION = ?
order by CREATED_DATE, MESSAGE_SEQUENCE LIMIT 1 FOR UPDATE SKIP LOCKED]
2025-04-04 18:06:13,429 [tx2]: Executing prepared SQL update
2025-04-04 18:06:13,429 [tx2]: Executing prepared SQL statement [DELETE from INT_CHANNEL_MESSAGE where MESSAGE_ID=? and GROUP_KEY=? and REGION=?]
2025-04-04 18:07:03,430 [tx2]: Error: 1205-HY000: Lock wait timeout exceeded; try restarting transaction
2025-04-04 18:07:03,432 [tx2]: Extracted SQL state class 'HY' from value 'HY000'
2025-04-04 18:07:03,434 [tx1]: Executing prepared SQL update
2025-04-04 18:07:03,434 [tx1]: Executing prepared SQL statement [INSERT into INT_CHANNEL_MESSAGE(
MESSAGE_ID,
GROUP_KEY,
REGION,
CREATED_DATE,
MESSAGE_PRIORITY,
MESSAGE_BYTES)
values (?, ?, ?, ?, ?, ?)
]
2025-04-04 18:07:03,435 [tx2]: Resetting isolation level of JDBC Connection [HikariProxyConnection@1449683964 wrapping org.mariadb.jdbc.Connection@5b1420f9] to 4
2025-04-04 18:07:03,436 [tx1]: TX END
Em primeiro lugar, o mecanismo de armazenamento InnoDB não possui bloqueio em nível de linha, mas algo mais refinado: bloqueio de registro de índice. Se uma tabela incluir qualquer índice secundário, como neste caso, será mais fácil entrar em um deadlock entre uma
INSERTleitura de bloqueio e qualquer leitura de bloqueio (incluindo aquelas executadas como parte deDELETEouUPDATE). A leitura de bloqueioINSERToperaria primeiro no índice clusterizado (oPRIMARY KEYíndice) e, em seguida, propagaria as alterações para os índices secundários. Uma leitura de bloqueio que utiliza um índice secundário primeiro adquiriria um bloqueio no registro de índice secundário e, em seguida, no registro de índice clusterizado correspondente. A inversão da ordem de bloqueio tem um bom potencial para criar deadlocks, pois é um pré-requisito para ter ciclos no grafo de espera. Ao procurar um exemplo anterior disso, me deparei com o relatório de bug MDEV-23560 .A saída que você está compartilhando parece sugerir que o detector de deadlocks integrado do InnoDB no MariaDB Server pode não funcionar. De acordo com a saída, uma transação foi bloqueada por 31,3 segundos. Isso ainda está dentro do padrão
innodb_lock_wait_timeoutde 50 segundos. A expectativa seria que, se houver um deadlock, uma das transações seja revertida imediatamente, em vez de após um tempo limite de espera de bloqueio. Basicamente, a única maneira de obter um tempo limite de espera de bloqueio é que haja outra transação ativa que esteja segurando o bloqueio conflitante e ainda não tenha sido confirmada (por exemplo, ela esteja aguardando novas entradas da conexão do cliente). Se o detector de deadlocks estiver realmente quebrado (em vez de ser desabilitado viainnodb_deadlock_detect=OFF), você pode registrar um bug com um caso de teste reproduzível e independente?Gostaria também de mencionar o parâmetro introduzido recentemente
innodb_snapshot_isolation( MDEV-35124 ). Em alguns casos, habilitá-lo pode substituir os tempos limite de espera de bloqueio por outro erroER_CHECKREAD. Se esse parâmetro não estiver habilitado, oREPEATABLE READerro não é repetível, como foi apontado em uma análise de Jepsen do MySQL 8.0.34 .Acontece que, na verdade, era um impasse no nível do aplicativo. Havia fortes evidências disso:
SHOW ENGINE INNODB STATUSnão apresentou nenhum impasseNo entanto, o DELETE no tx2 estava de fato aguardando para adquirir o bloqueio, que estava sendo mantido pelo tx1. A resposta de Marko explica o porquê.
A solução foi eliminar o bloqueio redundante no nível do aplicativo.