Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


RowStamp on 2 tables do not match

Posted on 2006-10-26
Medium Priority
Last Modified: 2008-01-09
I have a Maximo application that has a SQL backend and I restored a table that contained a rowstamp from a previous day. Now, none of the screens that reference that table will work correctly and say that another user is updating the table.

Here is what the makers of the software have to say. I ran update stats and rebuild indexes on all tables.

MAXIMO uses optimistic locking to ensure that two users cannot update the same record without being aware of each other's updates.  Each MAXIMO table has a column named ROWSTAMP which is set to a TIMESTAMP datatype on SQL Server or is populated with the next value of the MAXSEQ sequence by a trigger on Oracle.   This ensures that when a record is updated the value of ROWSTAMP changes.  
When MAXIMO updates a record, it references both the table's primary key(s) and the rowstamp value in the where clause.  Since the rowstamp value changes each time a record is updated, this mechanism ensures that an update will fail if the record has been modified since it was first queried.  If this occurs, MAXIMO sees that there were zero rows updated by the SQL statement and displays the "Record has been updated by another user" message.   The user must re-query the record in order for MAXIMO to get the updated value of rowstamp, as well as any other changes.

Since MAXIMO always interprets the status "Zero rows updated" to mean that the rowstamp has changed, you may see the "Record has been updated by another user" message when no other user is involved.
Question by:itascent
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
LVL 10

Expert Comment

ID: 17816406
OK, I think this is unclear but substantially correct. Optimistic locking is what SQL Server does. It is not good enough, so Maximo essentially forces pessimistic locking through this mechanism. But this mechanism as described only affects current operations.  

So first, see if you can get MAximo on the phone. they will have some idea of how to proceed.

Short of that, you can run the profiler to see what the application is actually doing. My guess is that you will see what it is doing and why it is failing.

I am not familiar with what happens when you restore a timestamp column from a backup. When you say restore, do you mean SQL Restore or some manual process? If the latter, you would probably end up creating new timestamps . It is possible that even a SQL restore does this. Not a lot of people in SQL Land use timestamp, which is not readily readable as a date.

I think the profiler is going to be your best bet to figure this out

Author Comment

ID: 17816653
Good tip. I came up with this statement

update poline set service = 'Y' , receiptreqd = 'N'  where orgid = 'ASCENT'  and polineid = 2067533353  and rowstamp = null

It has something to do with rowstamp. If I do the following query:

SELECT     *
FROM         poline
WHERE     (polineid = '2067533353')

It shows me the line

If I run this update statement, it says zero rows affected:

update poline set service = 'Y' , receiptreqd = 'N'  where orgid = 'ASCENT'  and polineid = 2067533353  and rowstamp = null

If I run this line:

update poline set rowstamp = null  where orgid = 'ASCENT'  and polineid = 2067533353  

It updates that to null, i guess it says 1 row affected

I run the second line again and it doesn’t update, it says zero rows affected.

If I take off the rowstamp = null on the selection criteria, it works

update poline set service = 'Y' , receiptreqd = 'N'  where orgid = 'ASCENT'  and polineid = 2067533353  

But the application is written that way, I just messed up the rowstamps somehow, but how do I fix it if I can't set the rowstamp to null and what is the perm fix?

LVL 10

Expert Comment

ID: 17816739
Hmm... my guess is that the application is getting a null where it should get a value, since i believe a timestamp is, by definition, never null.

this is really weird though - you should not have been able to update the rowstamp to null if it is actually a timestamp.  I get this message...

Msg 272, Level 16, State 1, Line 1
Cannot update a timestamp column.

are the values in the rowstamp column hex or datetimes?  

(I may head to bed shortly ... i will be back online by 8am tomorrow eastern US time)
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.


Author Comment

ID: 17817074
It says <binary>

Reading below, I'm not sure why the rowstamp even matters on what it is. And why does it see that its not null.

MAXIMO creates a column called ROWSTAMP for Oracle and Microsoft SQL Server databases.

In Microsoft SQL Server, the ROWSTAMP column is defined as a timestamp column.  Whenever a row is inserted or updated, the ROWSTAMP column is modified internally by SQL Server.  Note that the timestamp value is a unique value, but it is not a true date/timestamp.

The ROWSTAMP is used to ensure that a user does not write over a record which has been modified by another user.  This can happen if two users query a record, one user changes and saves the record, and the second user attempts to save the record.  It is important to stop the second user from writing over (losing) changes that the first user has made.  

MAXIMO updates the table based on the ROWSTAMP.  For example, when updating the companies table, the following SQL statement is generated by MAXIMO to ensure that the ROWSTAMP column has not changed since the time the record was first queried:

       update companies
       set company='test', name='newtest',
       changeby='MAXIMO', changedate='2005-02-09-'
       where company = 'test' and rowstamp= '000000000000D4C3'

If the ROWSTAMP has changed, the user will be informed that the record has changed and the update does not complete.

When inserting into MAXIMO tables using outside interfaces such as Excel or Access, the ROWSTAMP column will be populated automatically.  If you are inserting using  SQR or another SQL interface, the insert should ignore the ROWSTAMP column.  

For example, the altitem table has three columns: itemnum,  altitemnum, rowstamp.  When performing the insert, the statement should be coded as follows:

       insert into altitem (itemnum, altitemnum) values ('ITEM1', 'ALTITEM1');

The rowstamp column will be populated automatically by the server.
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17817085

Lets have a table which have col1 int and col2 as timestamp.

now for inserting data u have to write

insert into table values (1, null)

now if u select * from table, u will see col2 has a hexa value which is a time stamp put by the sql server.

so if u search for select * from table where col2 is null, u wont get any results.

LVL 29

Accepted Solution

Gautham Janardhan earned 2000 total points
ID: 17817095
Adding to the above points

In a CREATE TABLE or ALTER TABLE statement, you do not have to supply a column name for the timestamp data type:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp)

If you do not supply a column name, SQL Server generates a column name of timestamp. The rowversion data type synonym does not follow this behavior. You must supply a column name when you specify rowversion.

A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.


Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question