RowStamp on 2 tables do not match

Posted on 2006-10-26
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
  • 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)
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  


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 500 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2012 AOG and SQL2014 AOG 76 61
Copy data to New Year 9 33
What Is an Error? 2 27
Inserting LocalDB Table to SQL Server C# 3 19
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

733 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