3

I have an Azure function (Iot hub trigger) that:

  1. selects a top 1 record ordered by time in descending order
  2. compares with a new record that comes
  3. writes the coming record only if it differs from the selected one (some fields are different)

The issue pops up when records come into the azure function very rapidly - I end up with duplicates in the database. I guess this is because SQL Server doesn't have enough time to make changes in the database by the time the next record comes and Azure function selects, and when the Azure function selects the latest record, it actually receives an outdated one.

I use EF Core.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
amplifier
  • 1,793
  • 1
  • 21
  • 55

1 Answers1

1

I do believe that there is no issue with function but with the transactional nature of the operation you described. To solve your issue trivially, you can try using transaction with the highest isolation level:

        using (var transaction = new TransactionScope(
            TransactionScopeOption.Required,
            new TransactionOptions
            {
                // With this isolation level all data modifications are sequential
                IsolationLevel = IsolationLevel.Serializable
            }))
        {
            using (var connection = new SqlConnection("YOUR CONNECTION"))
            {
                connection.Open();

                try
                {
                    // Run raw ADO.NET command in the transaction
                    var command = connection.CreateCommand();
                    // Your reading query (just for example sake)
                    command.CommandText = "SELECT TOP 1 FROM dbo.Whatever";
                    var result = command.ExecuteScalar();

                    // Run an EF Core command in the transaction
                    var options = new DbContextOptionsBuilder<TestContext>()
                        .UseSqlServer(connection)
                        .Options;

                    using (var context = new TestContext(options))
                    {
                        context.Items.Add(result);
                        context.SaveChanges();
                    }

                    // Commit transaction if all commands succeed, transaction will auto-rollback
                    // when disposed if either commands fails
                    transaction.Complete();
                }
                catch (System.Exception)
                {
                    // TODO: Handle failure
                }
            }
        }

You should adjust the code for your need, but you have an idea.

Although, I would rather avoid the problem entirely and not modify any records, but rather insert them and select the latests afterwards. Transactions are tricky in application, they may cause performance degradation and deadlocks being applied in the wrong place and in the wrong way.

Nicklaus Brain
  • 884
  • 6
  • 15
  • I'm not modifying records. I'm selecting and inserting. Does your code lock any selects until inserting finishes? – amplifier Sep 21 '19 at 14:03
  • Exactly, IsolationLevel.Serializable is preventing other users from updating or inserting rows into the table until the transaction is complete. Read more information on serializable isolation level here: https://learn.microsoft.com/en-us/dotnet/api/system.data.isolationlevel And here: https://stackoverflow.com/questions/3467613/isolation-level-serializable-when-should-i-use-this – Nicklaus Brain Sep 21 '19 at 15:03
  • I've checked your suggestion but get consecutive duplicates anyway. I've updated my question with a code example. Could you look at it, please? – amplifier Oct 15 '19 at 15:43