Thiết kế website giá rẻ

Question

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_unicode_520_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" : 0n  } ]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" : nulln  },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" : nulln} ]' )

*** (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" : 0n  } ]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" : nulln  },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" : nulln} ]' )

*** (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

Thiết kế website giá rẻ

Danh mục

What is causing this mysql DeadLock?