Strange unique PK errors on cloud platforms

Posts   
 
    
JSobell
User
Posts: 145
Joined: 07-Jan-2006
# Posted on: 27-Mar-2022 05:56:19   

I'm not sure if I've missed something, but on both Azure SQL Server and AWS PostGres I get a random issue where I do an 'Exists' query for an Update/Insert, then get an exception saying the PK value already exists. I've broken into the code, seen the 'false' returned from the query, then done a manual query and found the value is present. I've tried adding a 'retry' 10 times every 100ms, but the problem persists. Should I be flushing the DataAdapter in some way to avoid this? The source table is a pretty flaky API, so it's possible I have the same key twice, but I don't understand why I get false when retrieving something that exists, and it's tricky to reproduce, but not infrequent.

    public bool ReplaceOrInsert(IEntity2 entity)
    {
        var factory = entity.GetEntityFactory();
        var newEntity = factory.Create();
        entity.PrimaryKeyFields.ForEach(pkField => newEntity.SetNewFieldValue(pkField.FieldIndex, pkField.CurrentValue));
        var exists = AdapterToUse.FetchEntity(newEntity);
        entity.IsNew = !exists;
        int attempts = 10;
        do
        {
            try
            {
                return AdapterToUse.SaveEntity(entity);
            }
            catch (Exception sql_ex)
            {
                Console.Error.WriteLine($"** Error in ReplaceOrInsert : ({sql_ex.GetType().Name}): {sql_ex.Message}");
                Thread.Sleep(100);
                if (attempts-- < 0)
                    throw;
                Console.Error.WriteLine($"** Error in ReplaceOrInsert - Retrying");
            }
        } while (true);
    }

Any suggestions?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 27-Mar-2022 10:30:51   

Try to wrap the insert in a transaction (so StartTransaction() + Commit()) and commit it afterwards. There's nothing to flush in ADO.NET, the statements are run on the database server. The only thing I can think of is if you start a transaction, then read the rows and insert if needed, the insert isn't happening till the transaction is committed.

Frans Bouma | Lead developer LLBLGen Pro