Sql-server – Equivalents to `ora_rowscn` in other databases

data-versioningMySQLpostgresqlsql server

Do other databases other than Oracle have ora_rowscn (http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) equivalents, or do you have to implement them yourself in your fields, code and stored procedures?

ora_rowscn is a pseudo-column that is attached to all tables in Oracle created using some specific settings, which is used when doing an optimistic offline-lock.

Best Answer

As I understand the question, the scenario is one where users read values from the database outside a transaction, and later attempt updates. The requirement is that the database detect when a lost update might occur.

SQL Server does not provide a direct equivalent for the Oracle pseudo-column ora_rowscn because it does not exclusively use row versioning to implement transaction isolation levels (so row versioning information is not always present). Nevertheless, there are a number of ways to implement optimistic concurrency control with SQL Server.

Using Row Version

One option is to add a rowversion column to the table. Often, this column will be given the same name in all tables (e.g. RV). The general strategy is to read the rowversion value when reading data, and to check that the value is unchanged when updating the row later on:

CREATE TABLE dbo.Example
(
    SomeKey     integer PRIMARY KEY,
    SomeValue   integer NOT NULL,
    OtherValue  integer NOT NULL,
    RV          rowversion NOT NULL
);

INSERT dbo.Example
    (SomeKey, SomeValue, OtherValue)
VALUES
    (1, 100, 1000);

-- Read data
SELECT SomeValue, RV
FROM dbo.Example AS E
WHERE SomeKey = 1;

-- Time passes

-- Attempt update
UPDATE dbo.Example
SET SomeValue = 123
WHERE SomeKey = 1
AND RV = 0x0000000000273711;

If the row has been modified, the UPDATE statement makes no changes and no error or warning is returned. It is up to the developer to check the number of rows affected by the statement, and take appropriate action (such as re-reading the source row, and alerting the user to the conflict).

Using an optimistic cursor

Server-side cursors are often used in the scenario we are discussing. SQL Server contains native support for OPTIMISTIC server-side cursors via a number of interfaces (e.g. API, ODBC, Native Client). If a rowversion column is present, SQL Server uses that to implement optimistic concurrency, without the developer needing to retrieve and check the rowversion value explicitly. Using a Transact-SQL cursor to demonstrate:

DECLARE UIcursor
    CURSOR GLOBAL
    SCROLL DYNAMIC
    OPTIMISTIC
FOR
    SELECT SomeValue
    FROM dbo.Example
    WHERE SomeKey = 1
    FOR UPDATE OF SomeValue;

OPEN UIcursor;

-- Read data
FETCH FIRST FROM UIcursor;

-- Time passes

-- Attempt update (note no rowversion reference needed)
UPDATE dbo.Example
SET SomeValue = 321
WHERE CURRENT OF UIcursor;

If the row has been changed since data was read, a warning and an error is returned:

Optimistic concurrency check failed. The row was modified outside of this cursor.
Msg 16947, Level 16, State 1, Line 1
No rows were updated or deleted.

If the table does not contain a rowversion, SQL Server transparently uses a reliable checksum to detect row changes. The same warning and error is generated if the checksum comparison fails. Using rowversion is more efficient than using the internal checksums, but both are equally reliable.

Note that a real implementation would be quite unlikely to use a global Transact-SQL cursor as shown above, but the concepts are the same when using whatever server-side cursor support is available within your development framework.

Related Question