Fixing a Persistent "Write Conflict" Error

After a recent SQL Server migration from SQL Server 2012 to SQL Server 2019, my users started receiving the following error when editing one of our tables:

This "Write Conflict" error had never appeared prior to the migration.  

Context

After some troubleshooting, I found that I could reliably reproduce the issue when running the 32-bit Access 2013 Runtime on my client's network, but not when debugging on my development machine (running Access 365 Version 2111 Build 16.0.14701.20206 64-bit).  

Both machines were using the same ODBC driver: "ODBC Driver 17 for SQL Server."  

As part of the migration, I changed the SQL Server Compatibility Level of the database from 90 (SQL Server 2005) to 150 (SQL Server 2019).

Here's the table definition for the AcctDocs table (note, I use singular nouns for my table names for new development):

Debugging with XEvent Profiler

To figure out what was going on, I created a couple of XEvent Profiler sessions.

If you've never used XEvent Profiler or SQL Server Profiler to peak under the hood, you really should.  You'll be amazed at what's actually going on behind the scenes.

What the above screenshot shows are two different sp_executesql calls that Access auto-generated when the user tried to save changes in a bound form.  

Comparing the Two T-SQL Statements

There were two differences between the statements above:

I extracted the T-SQL string from the broken machine and executed it in SSMS:

I changed the DECLARE @P5 line to...

DECLARE @P5 datetime='2018-11-07 13:57:42.197'

...then tried executing the UPDATE statement again.  This time it succeeded, displaying, "(1 row affected)."

In other words, the reason Access returned the error is because it appeared that some other process had changed the value of the ScannedAt field.

In reality, it was Access itself that converted the datetime value to a datetime2 value before doing the comparison.  Thus, it always looked like the data had changed.

Working Around the Error

I never did figure out why the two versions of Access had different behavior, but I did come up with a reliable fix: adding a rowversion field to the table.

Office Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table. This is more efficient than verifying that all the other columns still have the same values they had when the dynaset was last refreshed.

I ran the following T-SQL statement to add a new column named "odbc_id" to my table:

-- improve ODBC linked table concurrency
--  see: https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx
ALTER TABLE dbo.AcctDocs ADD odbc_id rowversion;  

Here's the refreshed column listing for the table:

How the Workaround Works

Now, when I save data changes to the table in my bound form, this is the T-SQL that gets generated (I added the whitespace for readability):

exec sp_executesql N'
UPDATE "dbo"."AcctDocs" 
SET "isFlagged"=@P1 
WHERE "AcctDocID" = @P2
  AND "odbc_id" = @P3
',N'
@P1 smallint,
@P2 int,
@P3 binary(8)
',
-1,
9122,
0x000000000000C48E

In the original screenshot, there were 11 clauses in the WHERE condition–one for each field in the table at the time.  That's because Access had to be sure that none of the other values had changed from the time the record was loaded until I attempted to save my changes.

In the new version, there are only two clauses in the WHERE condition:

1. One to identify the record based on its unique key (AcctDocID)
2. The other to see if the record had been UPDATEd by another process since it was loaded in the form (by making sure the rowversion-typed field had not changed)

This is both more efficient than checking every field, AND it avoids the datetime2 problem.  Win-win.  

Of course, I'd still love to know what's going on with the datetime2 bug....

Referenced articles

Playing Telephone with SQL Server

You won’t believe how Access and SQL Server actually talk to each other. You’re going to need to see this for yourself.

No Longer SetMike Wolfe

External references

Optimizing Microsoft Office Access Applications Linked to SQL Server

Microsoft DocsArchiveddocs

Image by kimostrupler2002 from Pixabay