Why is this deadlock happening?
Posted by: Vinicius Faleiro
Date: May 04, 2024 07:58AM
Date: May 04, 2024 07:58AM
I see that one microsservice is causing this deadlock bellow but couldn't find the root cause. I wouldn't expect this to happen sice 2 different threads are executing a replace command for 2 different keys on the table. Any idea?
Table DDL
CREATE TABLE faturamento (
DT_FATURA date NOT NULL COMMENT 'Data da fatura',
COD_GRUPO_FATURA varchar(500) NOT NULL
JSON_CFG_COBRANCA json NOT NULL
VL_BRUTO decimal(32,16) NOT NULL
VL_DESCONTO decimal(32,16) DEFAULT NULL
VL_LIQUIDO decimal(32,16) NOT NULL
JSON_APURACOES json NOT NULL
DESCONTOS_APLICADOS json NOT NULL
PRIMARY KEY (DT_FATURA,COD_GRUPO_FATURA)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Database output (table was empty)
2024-05-03 19:26:03 367417139072
*** (1) TRANSACTION:
TRANSACTION 8020, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 1172, OS thread handle 370219057024, query id 47288 172.19.0.9 root update
replace into faturamento (
DT_FATURA,
COD_GRUPO_FATURA,
JSON_CFG_COBRANCA,
VL_BRUTO,
VL_DESCONTO,
VL_LIQUIDO,
DESCONTOS_APLICADOS,JSON_APURACOES)
values ('2030-02-01', 'B', '{\n "cobrancaItens" : [ {\n "metodo" : "DELEGATE",\n "configuracao" : "{\\"delegateConfig\\":{\\"autoConfirmada\\":true,\\"queue\\":\\"delegate.queue\\"}}"\n } ],\n "configRetentativas" : [ {\n "metodo" : "DELEGATE",\n "totalRetentativas" : 1,\n "contadorRetentativas" : null,\n "frequencia" : "DIARIA",\n "dia" : 0\n } ]\n}', 102.90, 0.00, 102.90, '[ ]', '[ {\n "dtMovimento" : "2030-02-01",\n "idItemApurado" : "c6bf5dff-9c88-4952-98d5-a43df68a20b3",\n "idCicloApuracao" : "2030-01-01-1-2030",\n "frequencia" : "DIARIA",\n "vlPrecoApuracaoRef" : 102.90,\n "tpValorApuracao" : "TARIFA",\n "qtdCumulativaApuracaoCiclo" : 31,\n "vlSaldoAcumulado" : 102.9000000000000000,\n "cfgUltimaApuracao" : {\n "modelo" : "FIXO",\n "frequencia" : "DIARIA",\n "dia" : 1,\n "condicao" : null\n },\n "jsonItemApurado" : "{\\n \\"id\\" : \\"c6bf5dff-9c88-4952-98d5-a43df68a20b3\\",\\n \\"codContratante\\" : 9000001,\\n \\"codContratada\\" : 1000006,\\n \\"dtInicioVigencia\\" : \\"2999-01-01\\",\\n \\"dtFimVigencia\\" : \\"2024-01-01\\",\\n \\"infoAdicionalContratante\\" : \\"{\\\\\\"mcc\\\\\\": \\\\\\"105\\\\\\", \\\\\\"cnae\\\\\\": 3350707, \\\\\\"cnpj\\\\\\": \\\\\\"12.22.184/0001-04\\\\\\"}\\",\\n \\"infoAdicionalContratada\\" : \\"{}\\",\\n \\"cfgPrecificacao\\" : {\\n \\"insumo\\" : null,\\n \\"refContrato\\" : null,\\n \\"faixas\\" : null,\\n \\"fixo\\" : {\\n \\"tipo\\" : \\"TARIFA\\",\\n \\"valor\\" : 102.9\\n }\\n },\\n \\"cfgApuracao\\" : {\\n \\"modelo\\" : \\"FIXO\\",\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 1,\\n \\"condicao\\" : null\\n },\\n \\"cfgDesconto\\" : null,\\n \\"cfgFaturamento\\" : {\\n \\"frequencia\\" : \\"MENSAL\\",\\n \\"dia\\" : 1,\\n \\"codAgrupamentoFatura\\" : \\"B\\",\\n \\"diasFaturamento\\" : 0\\n },\\n \\"cfgCobranca\\" : {\\n \\"cobrancaItens\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"configuracao\\" : \\"{\\\\\\"delegateConfig\\\\\\":{\\\\\\"autoConfirmada\\\\\\":true,\\\\\\"queue\\\\\\":\\\\\\"delegate.queue\\\\\\"}}\\"\\n } ],\\n \\"configRetentativas\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"totalRetentativas\\" : 1,\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 0\\n } ]\\n },\\n \\"cdStatus\\" : \\"ATIVO\\"\\n}",\n "dtCriacaoRegistro" : "2024-04-29T19:01:52-03:00",\n "dtUltimaAtualiacao" : "2024-05-03T13:23:52-03:00",\n "tpValorDesconto" : null\n} ]' )
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8020 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8020 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 8022, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 1193, OS thread handle 372167667584, query id 47290 172.19.0.9 root update
replace into faturamento (
DT_FATURA,
COD_GRUPO_FATURA,
JSON_CFG_COBRANCA,
VL_BRUTO,
VL_DESCONTO,
VL_LIQUIDO,
DESCONTOS_APLICADOS,JSON_APURACOES)
values ('2030-02-01', 'A', '{\n "cobrancaItens" : [ {\n "metodo" : "DELEGATE",\n "configuracao" : "{\\"delegateConfig\\":{\\"autoConfirmada\\":true,\\"queue\\":\\"delegate.queue\\"}}"\n } ],\n "configRetentativas" : [ {\n "metodo" : "DELEGATE",\n "totalRetentativas" : 1,\n "contadorRetentativas" : null,\n "frequencia" : "DIARIA",\n "dia" : 0\n } ]\n}', 89.90, 0.00, 89.90, '[ ]', '[ {\n "dtMovimento" : "2030-02-01",\n "idItemApurado" : "7b3107c0-930e-4e6f-9090-54cf73ac5b05",\n "idCicloApuracao" : "2030-01-01-1-2030",\n "frequencia" : "DIARIA",\n "vlPrecoApuracaoRef" : 89.90,\n "tpValorApuracao" : "TARIFA",\n "qtdCumulativaApuracaoCiclo" : 30,\n "vlSaldoAcumulado" : 89.9000000000000000,\n "cfgUltimaApuracao" : {\n "modelo" : "FIXO",\n "frequencia" : "DIARIA",\n "dia" : 1,\n "condicao" : null\n },\n "jsonItemApurado" : "{\\n \\"id\\" : \\"7b3107c0-930e-4e6f-9090-54cf73ac5b05\\",\\n \\"codContratante\\" : 9000001,\\n \\"codContratada\\" : 7000005,\\n \\"dtInicioVigencia\\" : \\"2999-01-01\\",\\n \\"dtFimVigencia\\" : \\"2024-01-01\\",\\n \\"infoAdicionalContratante\\" : \\"{\\\\\\"mcc\\\\\\": \\\\\\"998\\\\\\", \\\\\\"cnae\\\\\\": 3250709, \\\\\\"cnpj\\\\\\": \\\\\\"11.156.184/0001-08\\\\\\"}\\",\\n \\"infoAdicionalContratada\\" : \\"{}\\",\\n \\"cfgPrecificacao\\" : {\\n \\"insumo\\" : null,\\n \\"refContrato\\" : null,\\n \\"faixas\\" : null,\\n \\"fixo\\" : {\\n \\"tipo\\" : \\"TARIFA\\",\\n \\"valor\\" : 89.9\\n }\\n },\\n \\"cfgApuracao\\" : {\\n \\"modelo\\" : \\"FIXO\\",\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 1,\\n \\"condicao\\" : null\\n },\\n \\"cfgDesconto\\" : null,\\n \\"cfgFaturamento\\" : {\\n \\"frequencia\\" : \\"MENSAL\\",\\n \\"dia\\" : 1,\\n \\"codAgrupamentoFatura\\" : \\"A\\",\\n \\"diasFaturamento\\" : 0\\n },\\n \\"cfgCobranca\\" : {\\n \\"cobrancaItens\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"configuracao\\" : \\"{\\\\\\"delegateConfig\\\\\\":{\\\\\\"autoConfirmada\\\\\\":true,\\\\\\"queue\\\\\\":\\\\\\"delegate.queue\\\\\\"}}\\"\\n } ],\\n \\"configRetentativas\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"totalRetentativas\\" : 1,\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 0\\n } ]\\n },\\n \\"cdStatus\\" : \\"ATIVO\\"\\n}",\n "dtCriacaoRegistro" : "2024-04-29T22:22:50-03:00",\n "dtUltimaAtualiacao" : "2024-05-03T13:23:52-03:00",\n "tpValorDesconto" : null\n} ]' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8022 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8022 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
I tried to analyze de db logs but couldn't understand why a replace command in two different table keys caused a deadlock.
I was able to reproduce the problem using 2 mysql clients. Steps:
execute begin transaction on both clients
execute query on both clients using different values for parameter COD_GRUPO_FATURA: SELECT DT_FATURA, COD_GRUPO_FATURA, JSON_CFG_COBRANCA, VL_BRUTO, VL_DESCONTO, VL_LIQUIDO, DESCONTOS_APLICADOS,JSON_APURACOES from faturamento where DT_FATURA = ? and COD_GRUPO_FATURA = ? FOR UPDATE
execute query on both clients using same parameters from step 2: replace into faturamento ( DT_FATURA, COD_GRUPO_FATURA, JSON_CFG_COBRANCA, VL_BRUTO, VL_DESCONTO, VL_LIQUIDO, DESCONTOS_APLICADOS,JSON_APURACOES)
question is: aren't both commands using row lock? why the deadlock?
Table DDL
CREATE TABLE faturamento (
DT_FATURA date NOT NULL COMMENT 'Data da fatura',
COD_GRUPO_FATURA varchar(500) NOT NULL
JSON_CFG_COBRANCA json NOT NULL
VL_BRUTO decimal(32,16) NOT NULL
VL_DESCONTO decimal(32,16) DEFAULT NULL
VL_LIQUIDO decimal(32,16) NOT NULL
JSON_APURACOES json NOT NULL
DESCONTOS_APLICADOS json NOT NULL
PRIMARY KEY (DT_FATURA,COD_GRUPO_FATURA)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Database output (table was empty)
2024-05-03 19:26:03 367417139072
*** (1) TRANSACTION:
TRANSACTION 8020, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 1172, OS thread handle 370219057024, query id 47288 172.19.0.9 root update
replace into faturamento (
DT_FATURA,
COD_GRUPO_FATURA,
JSON_CFG_COBRANCA,
VL_BRUTO,
VL_DESCONTO,
VL_LIQUIDO,
DESCONTOS_APLICADOS,JSON_APURACOES)
values ('2030-02-01', 'B', '{\n "cobrancaItens" : [ {\n "metodo" : "DELEGATE",\n "configuracao" : "{\\"delegateConfig\\":{\\"autoConfirmada\\":true,\\"queue\\":\\"delegate.queue\\"}}"\n } ],\n "configRetentativas" : [ {\n "metodo" : "DELEGATE",\n "totalRetentativas" : 1,\n "contadorRetentativas" : null,\n "frequencia" : "DIARIA",\n "dia" : 0\n } ]\n}', 102.90, 0.00, 102.90, '[ ]', '[ {\n "dtMovimento" : "2030-02-01",\n "idItemApurado" : "c6bf5dff-9c88-4952-98d5-a43df68a20b3",\n "idCicloApuracao" : "2030-01-01-1-2030",\n "frequencia" : "DIARIA",\n "vlPrecoApuracaoRef" : 102.90,\n "tpValorApuracao" : "TARIFA",\n "qtdCumulativaApuracaoCiclo" : 31,\n "vlSaldoAcumulado" : 102.9000000000000000,\n "cfgUltimaApuracao" : {\n "modelo" : "FIXO",\n "frequencia" : "DIARIA",\n "dia" : 1,\n "condicao" : null\n },\n "jsonItemApurado" : "{\\n \\"id\\" : \\"c6bf5dff-9c88-4952-98d5-a43df68a20b3\\",\\n \\"codContratante\\" : 9000001,\\n \\"codContratada\\" : 1000006,\\n \\"dtInicioVigencia\\" : \\"2999-01-01\\",\\n \\"dtFimVigencia\\" : \\"2024-01-01\\",\\n \\"infoAdicionalContratante\\" : \\"{\\\\\\"mcc\\\\\\": \\\\\\"105\\\\\\", \\\\\\"cnae\\\\\\": 3350707, \\\\\\"cnpj\\\\\\": \\\\\\"12.22.184/0001-04\\\\\\"}\\",\\n \\"infoAdicionalContratada\\" : \\"{}\\",\\n \\"cfgPrecificacao\\" : {\\n \\"insumo\\" : null,\\n \\"refContrato\\" : null,\\n \\"faixas\\" : null,\\n \\"fixo\\" : {\\n \\"tipo\\" : \\"TARIFA\\",\\n \\"valor\\" : 102.9\\n }\\n },\\n \\"cfgApuracao\\" : {\\n \\"modelo\\" : \\"FIXO\\",\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 1,\\n \\"condicao\\" : null\\n },\\n \\"cfgDesconto\\" : null,\\n \\"cfgFaturamento\\" : {\\n \\"frequencia\\" : \\"MENSAL\\",\\n \\"dia\\" : 1,\\n \\"codAgrupamentoFatura\\" : \\"B\\",\\n \\"diasFaturamento\\" : 0\\n },\\n \\"cfgCobranca\\" : {\\n \\"cobrancaItens\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"configuracao\\" : \\"{\\\\\\"delegateConfig\\\\\\":{\\\\\\"autoConfirmada\\\\\\":true,\\\\\\"queue\\\\\\":\\\\\\"delegate.queue\\\\\\"}}\\"\\n } ],\\n \\"configRetentativas\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"totalRetentativas\\" : 1,\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 0\\n } ]\\n },\\n \\"cdStatus\\" : \\"ATIVO\\"\\n}",\n "dtCriacaoRegistro" : "2024-04-29T19:01:52-03:00",\n "dtUltimaAtualiacao" : "2024-05-03T13:23:52-03:00",\n "tpValorDesconto" : null\n} ]' )
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8020 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8020 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 8022, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 1193, OS thread handle 372167667584, query id 47290 172.19.0.9 root update
replace into faturamento (
DT_FATURA,
COD_GRUPO_FATURA,
JSON_CFG_COBRANCA,
VL_BRUTO,
VL_DESCONTO,
VL_LIQUIDO,
DESCONTOS_APLICADOS,JSON_APURACOES)
values ('2030-02-01', 'A', '{\n "cobrancaItens" : [ {\n "metodo" : "DELEGATE",\n "configuracao" : "{\\"delegateConfig\\":{\\"autoConfirmada\\":true,\\"queue\\":\\"delegate.queue\\"}}"\n } ],\n "configRetentativas" : [ {\n "metodo" : "DELEGATE",\n "totalRetentativas" : 1,\n "contadorRetentativas" : null,\n "frequencia" : "DIARIA",\n "dia" : 0\n } ]\n}', 89.90, 0.00, 89.90, '[ ]', '[ {\n "dtMovimento" : "2030-02-01",\n "idItemApurado" : "7b3107c0-930e-4e6f-9090-54cf73ac5b05",\n "idCicloApuracao" : "2030-01-01-1-2030",\n "frequencia" : "DIARIA",\n "vlPrecoApuracaoRef" : 89.90,\n "tpValorApuracao" : "TARIFA",\n "qtdCumulativaApuracaoCiclo" : 30,\n "vlSaldoAcumulado" : 89.9000000000000000,\n "cfgUltimaApuracao" : {\n "modelo" : "FIXO",\n "frequencia" : "DIARIA",\n "dia" : 1,\n "condicao" : null\n },\n "jsonItemApurado" : "{\\n \\"id\\" : \\"7b3107c0-930e-4e6f-9090-54cf73ac5b05\\",\\n \\"codContratante\\" : 9000001,\\n \\"codContratada\\" : 7000005,\\n \\"dtInicioVigencia\\" : \\"2999-01-01\\",\\n \\"dtFimVigencia\\" : \\"2024-01-01\\",\\n \\"infoAdicionalContratante\\" : \\"{\\\\\\"mcc\\\\\\": \\\\\\"998\\\\\\", \\\\\\"cnae\\\\\\": 3250709, \\\\\\"cnpj\\\\\\": \\\\\\"11.156.184/0001-08\\\\\\"}\\",\\n \\"infoAdicionalContratada\\" : \\"{}\\",\\n \\"cfgPrecificacao\\" : {\\n \\"insumo\\" : null,\\n \\"refContrato\\" : null,\\n \\"faixas\\" : null,\\n \\"fixo\\" : {\\n \\"tipo\\" : \\"TARIFA\\",\\n \\"valor\\" : 89.9\\n }\\n },\\n \\"cfgApuracao\\" : {\\n \\"modelo\\" : \\"FIXO\\",\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 1,\\n \\"condicao\\" : null\\n },\\n \\"cfgDesconto\\" : null,\\n \\"cfgFaturamento\\" : {\\n \\"frequencia\\" : \\"MENSAL\\",\\n \\"dia\\" : 1,\\n \\"codAgrupamentoFatura\\" : \\"A\\",\\n \\"diasFaturamento\\" : 0\\n },\\n \\"cfgCobranca\\" : {\\n \\"cobrancaItens\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"configuracao\\" : \\"{\\\\\\"delegateConfig\\\\\\":{\\\\\\"autoConfirmada\\\\\\":true,\\\\\\"queue\\\\\\":\\\\\\"delegate.queue\\\\\\"}}\\"\\n } ],\\n \\"configRetentativas\\" : [ {\\n \\"metodo\\" : \\"DELEGATE\\",\\n \\"totalRetentativas\\" : 1,\\n \\"frequencia\\" : \\"DIARIA\\",\\n \\"dia\\" : 0\\n } ]\\n },\\n \\"cdStatus\\" : \\"ATIVO\\"\\n}",\n "dtCriacaoRegistro" : "2024-04-29T22:22:50-03:00",\n "dtUltimaAtualiacao" : "2024-05-03T13:23:52-03:00",\n "tpValorDesconto" : null\n} ]' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8022 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 4 n bits 72 index PRIMARY of table `apurador`.`faturamento` trx id 8022 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
I tried to analyze de db logs but couldn't understand why a replace command in two different table keys caused a deadlock.
I was able to reproduce the problem using 2 mysql clients. Steps:
execute begin transaction on both clients
execute query on both clients using different values for parameter COD_GRUPO_FATURA: SELECT DT_FATURA, COD_GRUPO_FATURA, JSON_CFG_COBRANCA, VL_BRUTO, VL_DESCONTO, VL_LIQUIDO, DESCONTOS_APLICADOS,JSON_APURACOES from faturamento where DT_FATURA = ? and COD_GRUPO_FATURA = ? FOR UPDATE
execute query on both clients using same parameters from step 2: replace into faturamento ( DT_FATURA, COD_GRUPO_FATURA, JSON_CFG_COBRANCA, VL_BRUTO, VL_DESCONTO, VL_LIQUIDO, DESCONTOS_APLICADOS,JSON_APURACOES)
question is: aren't both commands using row lock? why the deadlock?
Subject
Views
Written By
Posted
Sorry, only registered users may post in this forum.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.