April 01, 2005
Faster optimistic locking with DB2
Just saw this for the first time today. DB2 allows tables to have a hidden column that is basically a 'last updated timestamp'. This column can be used to do overqualified updates when doing optimistic locking. But, an issue with this was that the application needed to read the updated row to get the new timestamp. DB2 has new statements that allow the updated columns to be returned from the insert or update sql statement. This basically halves the number of statements sent to the database when using this kind of mechanism for optimistic locking.
Read about it here or google for data-change-table-reference
April 1, 2005 in J2EE | Permalink
This can be done in one statement on any database. You just compare expected value (row version or last timestamp) in your update statement (UPDATE ... WHERE ... AND rowversion='expected version') and then verify that statement updated expected number of rows.
Posted by: eu | Apr 1, 2005 2:55:49 PM
It's different. What you say tells you whether the data changed under you. What it doesn't do is tell you the values of any auto inc columns in the row after the update. Thats what this sql syntax in DB2 does.
Posted by: Billy | Apr 1, 2005 3:02:22 PM