Optimistic Concurrency

Optimistic Concurrency

By default, Autoprocs generate simple SQL updates.

CREATE TABLE Beer (
    ID [int] IDENTITY,
    Name [varchar](256)
)
GO

-- AUTOPROC All Beer
GO

This generates:

CREATE PROCEDURE UpdateBeer (
    @ID [int],
    @Name [varchar](256)
)
AS
    UPDATE Beer SET Name=@Name
        WHERE ID=@ID
GO

You can turn on optimistic concurrency for the table by adding a timestamp/rowversion column, and adding the Optimistic autoproc flag:

CREATE TABLE Beer (
    ID [int] IDENTITY,
    Name [varchar](256),
    RowVersion [rowversion]
)
GO

-- AUTOPROC All,Optimistic Beer
GO

This generates:

CREATE PROCEDURE UpdateBeer (
    @ID [int],
    @Name [varchar](256),
    @RowVersion [rowversion]
)
AS
    UPDATE Beer SET Name=@Name 
        WHERE ID=@ID
            AND (RowVersion=@RowVersion OR @RowVersion IS NULL)
    IF @@ROWCOUNT <> 1
        RAISERROR('At least one record has changed or does not exist. (CONCURRENCY CHECK)', 16, 1)
GO

This can be used to implement concurrency checks. If the RowVersion doesn't match the version that you already have, then someone else must have changed the record when you aren't looking.

When optimistic concurrency is on, the following methods check for concurrency:

• UpdateX
• UpdateXs (multiple)
• UpsertX
• UpsertXs (multiple)
• DeleteX
• DeleteXs (multiple)

The procs that take multiple records are wrapped in a transaction, and all of the updates will succeed or all of them will fail.

In addition, Inserts, Updates and Upserts will all return the ID and rowversion columns, so the change to a rowversion can automatically be reflected in your objects.