Hello,
in my company we are using Entity framework Core to access a DB2 database. We are using the library IBM.EntityFrameworkCore version 8.00.300 on a .net project.
We have a bunch of identical transactions, which operate at the same time on the same sequence of tables but obviously on different records (though for a couple of tables, it may occasionally happen that two distinct transactions concur on the same record) . We start the transaction with isolation level SERIALIZABLE,to avoid inconsistency on the data.
From time to time, we have a deadlock exception , so the exception is managed and the transaction is (or at least we expect so, since we explicitly call Rollback () on it) rolled back . What we observe, from time to time, is that some of the modifications made by the transaction in some tables appear to have been committed, others remain in the initial state. How is it possible ? isn't a transaction expected to be totally committed, or totally rolled back ?
The code is similar to the following :
using (var transcation = _db2Context.Database.BeginTransaction(IsolationLevel.Serializable))
{
try
{
#region Eliminazione EC03 e Aggiornamento Spxx
_LogRepository.SalvaLogErrore(1, "RendicontazioneRata", "INF", $"Pensione: {Rata.Pensione} - Anno: {Rata.Anno} - Nrata {Rata.Rata} - Matricola: {matricola} - Lettura record Ec03");
EsitoQuery<Pntbec03> recordEc03 = _operationQuery.LeggiRateEC03(Rata, _db2Context);
//Errore in caso non venga trovato la rata
if (recordEc03.record == null)
{
throw new Exception(...);
}
_LogRepository.SalvaLogErrore(...);
_db2Context.Pntbec03.Remove(recordEc03.record);
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(...);
int resultUpdateSp01 = _operationQuery.UpdateSPXX(Rata, dtNotifica, _db2Context);
if (resultUpdateSp01 != 1)
{
throw new Exception(...);
}
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(...);
#endregion
#region Lettura SP01 e Aggiornamento RB03 e Update RCCS
_LogRepository.SalvaLogErrore(...);
EsitoQuery<Pnvosp01> recordSp01 = _operationQuery.LeggiRateSP01(Rata, _db2Context);
if (recordSp01.codErr != "000")
{
throw new Exception(...);
}
if (recordSp01.record == null)
{
throw new Exception(....);
}
_LogRepository.SalvaLogErrore(....);
Log.Information(.....);
_LogRepository.SalvaLogErrore(.....);
Pntbrb03 recordRb03 = _db2Context.Pntbrb03.Where(x => x.Rb03CodAbiColl == "99999" && x.Rb03CodAbiPag == "99999" && x.Rb03DtaPag == recordSp01.record.Sp01DtaPag && x.Rb03IdentFlusso == Rata.IdentFlusso).FirstOrDefault();
if (recordRb03 == null)
{
throw new Exception(......);
}
_LogRepository.SalvaLogErrore(.....);
if (Rata.NatMovimAgg == "PC")
{
recordRb03.Rb03ImportoRend = recordRb03.Rb03ImportoRend + Rata.ImportoNetto;
recordRb03.Rb03NumPagRend = ++recordRb03.Rb03NumPagRend;
}
else // "NC"
{
recordRb03.Rb03ImportoNp = recordRb03.Rb03ImportoNp + Rata.ImportoNetto;
recordRb03.Rb03NumPagNp = ++recordRb03.Rb03NumPagNp;
}
_db2Context.Pntbrb03.Update(recordRb03);
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(....);
_LogRepository.SalvaLogErrore(....);
bool result = _operationQuery.UpdateRCSS(Rata, recordSp01.record, _db2Context);
if (result == false)
throw new Exception($"Pensione: {Rata.Pensione} - Anno: {Rata.Anno} - Nrata {Rata.Rata} - Matricola: {matricola} -Errore presente nella RCCS");
_db2Context.SaveChanges();
#endregion
#region Lettura CRBS e CR04 e Aggiornamento CRBS
EsitoQuery<Pntbcrbs> recordCrbs = _operationQuery.LeggiRateCRBS(Rata, _db2Context);
if(recordCrbs.codErr != "000")
{
throw new Exception(....);
}
if (recordCrbs.record != null)
{
throw new Exception(...);
}
_LogRepository.SalvaLogErrore(....);
_LogRepository.SalvaLogErrore(....);
EsitoQuery<Pntbcr04> recordCr04 = _operationQuery.LeggiRateCR04(Rata, _db2Context);
recordCrbs.record = _operationQuery.AddOrUpdateCRBS(Rata ,matricola, intMatricola, recordCr04.record, recordCrbs.record, recordSp01.record, out type);
if (type == OperationQuery.OperationType.Inserted)
{
_db2Context.Pntbcrbs.Add(recordCrbs.record);
}
else
_db2Context.Pntbcrbs.Update(recordCrbs.record);
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(....);
#endregion
#region Aggiornamento RRES e aggiornamento RRBT
if (Rata.NatMovimAgg == "NC")
{
if (recordCr04.record != null)
{
_db2Context.Pntbcr04.Remove(recordCr04.record);
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(.....);
_LogRepository.SalvaLogErrore(......);
EsitoQuery<Pntbrres> recordRres = _operationQuery.LeggiRateRRES(Rata, _db2Context);
if (recordRres.codErr != "000")
{
throw new Exception(....);
}
if (recordRres.record != null)
{
_db2Context.Pntbrres.Remove(recordRres.record);
recordRres.record = null;
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(....);
}
_LogRepository.SalvaLogErrore(.....);
recordRres.record = _operationQuery.AddOrUpdateRRESWithCR04(Rata, intMatricola, recordCr04.record, recordRres.record, out type);
if (type == OperationQuery.OperationType.Inserted)
{
_db2Context.Pntbrres.Add(recordRres.record);
}
else
_db2Context.Pntbrres.Update(recordRres.record);
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(....);
}
else
{
_LogRepository.SalvaLogErrore(....);
EsitoQuery<Pntbrrbt> recordRrbt = _operationQuery.LeggiRateRRBT(Rata, _db2Context);
if (recordRrbt.record != null)
{
_LogRepository.SalvaLogErrore(....);
_db2Context.Pntbrrbt.Remove(recordRrbt.record);
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(....);
EsitoQuery<Pntbrres> recordRres = _operationQuery.LeggiRateRRES(Rata, _db2Context);
if (recordRres.codErr != "000")
{
throw new Exception( .... );
}
if (recordRres.record != null)
{
_LogRepository.SalvaLogErrore(....);
_db2Context.Pntbrres.Remove(recordRres.record);
recordRres.record = null; // svuoto il record sennò nell addorupdate me lo passa come update
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(....);
}
recordRres.record = _operationQuery.AddOrUpdateRRESWithRRBT(Rata, intMatricola, recordRrbt.record, recordRres.record, out type);
if (type == OperationQuery.OperationType.Inserted)
{
_db2Context.Pntbrres.Add(recordRres.record);
}
else
_db2Context.Pntbrres.Update(recordRres.record);
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(.....);
}
}
#region Aggiornamento RN00
_LogRepository.SalvaLogErrore(......);
EsitoQuery<Pnvorn00> recordRn00 = _operationQuery.LeggiRateRN00(Rata, _db2Context);
recordRn00.record = _operationQuery.AddOrUpdateRN00(Rata, recordSp01.record, recordRn00.record, out type);
if (type == OperationQuery.OperationType.Inserted)
{
_db2Context.Pnvorn00.Add(recordRn00.record);
}
else
_db2Context.Pnvorn00.Update(recordRn00.record);
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(......);
#endregion
}
#endregion
#region Inserimento/Aggiornamento RI02
if (Rata.NatMovim == "DR" && Rata.NatMovimAgg == "PC")
{
_LogRepository.SalvaLogErrore(....);
Pntbri02 recordRi02 = _db2Context.Pntbri02.Where(x => x.Ri02CodAbi == recordSp01.record.Sp01CodAbiEmiss && x.Ri02DtaPag == recordSp01.record.Sp01DtaPag
&& x.Ri02DtaPreFile == recordSp01.record.Sp01DtaPreFile && x.Ri02OraPreFile == recordSp01.record.Sp01OraPreFile && x.Ri02IdentFlusso == recordSp01.record.Sp01IdentFlusso).FirstOrDefault();
recordRi02 = _operationQuery.AddOrUpdateRI02(Rata, recordSp01.record, recordRi02, out type);
if (type == OperationQuery.OperationType.Inserted)
{
_db2Context.Pntbri02.Add(recordRi02);
}
else
_db2Context.Pntbri02.Update(recordRi02);
_db2Context.SaveChanges();
_LogRepository.SalvaLogErrore(....);
}
#endregion
//_db2Context.SaveChanges();
transcation.Commit();
_LogRepository.SalvaLogErrore(.....);
}
catch (Exception ex)
{
codErr = "003";
codErrRata = "021";
try
{
transcation.Rollback();
}
catch (Exception roll)
{
Log.Error(roll.ToString(), .....);
_LogRepository.SalvaLogErrore(.....);
throw;
}
Log.Error(ex.ToString(), ex);
if(ex.InnerException != null)
_LogRepository.SalvaLogErrore(......);
else
_LogRepository.SalvaLogErrore(......);
return false;
}
}
Thank you in advance
------------------------------
Leo Del Ciello
------------------------------