Solved

RowStamp on 2 tables do not match

Posted on 2006-10-26
9
2,048 Views
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.
0
Comment
Question by:itascent
[X]
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
9 Comments
 
LVL 10

Expert Comment

by:AaronAbend
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
0
 

Author Comment

by:itascent
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?


0
 
LVL 10

Expert Comment

by:AaronAbend
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)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:itascent
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-09.38.00.000000'
       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.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17817085
Hello,

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.

0
 
LVL 29

Accepted Solution

by:
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.

0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 shrink a transaction log file down to a reasonable size.

628 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