SQL Server memory optimized tables detecting concurrency violation

Question

In SQL Server memory optimized tables, the rowversion data type is not allowed.

In my application I still want to be able to detect a concurrency violation where user A opens an item for edit, User B opens the same item, User A saves and User B saves without seeing user A's changes. I've used a rowversion column for this for years. What's the recommendation for managing my own fake rowversion column?

Answer 1

You didn't mention what frontend language you use, but here's a tip from my experience with c#'s datasets and the "use optimistic concurrency" option

When you download a row into a DataTable, the dt tracks the original values it downloaded:

SELECT id, name, age, address FROM person

--> 1, Jon, 33, null

You make an update:

dt[0].Name = "Paul"

The row now tracks two values, original (Jon) and current (Paul)

Sending the update back to the database, it runs a query like this:

UPDATE person 
SET name = @currentName 
WHERE id = @originalID and 
 (Name = @originalName or (@originalName is null AND name is null)) AND 
 ...

So effectively:

UPDATE person 
SET name = 'Paul'
WHERE id = 1 and 
 (Name = 'John' or ('John' is null AND name is null)) AND...

This way data that has been changed/nulled/nonnulled by a foreign client can be detected

It may be simplest to use an ORM for your language, that has this facility

-

ps: Perhaps a simpler notion for this is to add an int column and a trigger to increment it. It probably doesn't sit well with most people though, to add a column that is purely for information control in this manner, rather than modelling some real world data aspect of the stored entity

Answer 2

The Memory-Optimized table subsystem has built-in support for optimistic concurrency.

Transactions with Memory-Optimized Tables

Error Code: 41302

Description: Attempted to update a row that was updated in a different transaction since the start of the present transaction.

Cause: This error condition occurs if two concurrent transactions attempt to update or delete the same row at the same time. One of the two transactions receives this error message and will need to be retried.

For other scenarios, such as the "Open customer account in CRM" situation, you can still use the old-school ways, like having a manually-applied row version number, that you stick into your predicates.

For instance:

UPDATE
 [MyTable]
SET
 [Col001] = 'SomeVal'
WHERE
 PK = @primaryKey
 AND [RowVersionColumn] = @rowVersionStashedFromSelect