4

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.

  • 3
    It can be helpful when asking "what's the equivalent of (blah)" questions, to explain what the thing you want an equivalent of is and what purpose you need it to serve. At least find a good reference that explains it (not just defines it) and link to that. – Craig Ringer Aug 16 '13 at 12:10
  • 1
    @CraigRinger i suspect he's alluding to his previous question and related answer: dba.stackexchange.com/questions/48183/… – Philᵀᴹ Aug 16 '13 at 12:21
  • 1
    OK, you're looking for a built-in column for oplocking. No, there isn't one in PostgreSQL, you must add it to the schema. please Don't attempt to (ab)use oid etc for this. – Craig Ringer Aug 16 '13 at 12:24
  • 1
    Here is the MySQL equivalent. stackoverflow.com/questions/1364051/… – MacGyver Dec 13 '13 at 21:36
  • As mentioned earlier by @Phil, you should look at Example optimisic offline lock in SQL/PL?. There I show you that you should NOT use ora_rowscn for optimistic locking. – rvheddeg Nov 21 '14 at 12:29
2

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.

2

For universally unique PostgreSQL row revision numbers I use a sequence and a trigger that I add to the relevant tables.

The sequence looks like:

CREATE SEQUENCE seq_rev
  INCREMENT 1
  MINVALUE -9223372036854775807
  MAXVALUE 9223372036854775806
  START -9223372036854765807;

The function to update the revision ID is:

create or replace function update_rev_id_column()
returns trigger as $$
begin
  new.rev_id = nextval('public.seq_rev');
  return new;
end;
$$ language 'plpgsql';

And then when I create a table it looks something like:

create table web_user (
  web_user_id bigint primary key default nextval('public.seq_bigint'),
  user_name char(25),
  name_property character varying,
  rev_id bigint not null default nextval('public.seq_rev')
);

create trigger update_web_user_rev_id before update
  on web_user for each row execute procedure
  update_rev_id_column();

Now every time you insert or update a column it gets a universally unique rev_id.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.