Best way to handle timestamp fields

[TheMouster]

Hi,

Is there a best-practice with Iridium for handling RowVersion or Timestamp fields in for e.g. SQL Server.

Thanks

[activa]

What's the use case you have in mind?

[TheMouster]

Checking for row-version timestamp changes upon saving records i.e. concurrency.

[activa]

Automatic handling of optimistic concurrency would go too far for this library. Iridium should remain a lightweight no-frills ORM for use in many environments.

What would be useful is to add a method to retrieve the rowversion of a row if the database provider supports it. For example, on SQL Server it would return the value of the first rowversion column, on Postrgres it would return the value of the xmin system column, etc. (sadly, Sqlite doesn't have anything like that so it would return 0 for Sqlite databases)

Once you have that method, it would be easy to create your own extension methods that take care of concurrency.

[bjorn-lundin]

Hi! We are migrating a largish db-heavy application (150+ tables) to dotnet core, from Ada - meaning there's going to be quite some rewriting/rethinking but keeping concepts. One of those is detecting conflicting updates. We do it through 2 fields, but the principle is the same. Read the fields and include in where-statement, 0 row affected shows conflict, raise exception. So - This would be a VERY nice feature. To have it is a must for us, so if it not within framework/ORM, we need to add it ourselves.

@activa , could you elaborate a bit on your suggestion? I saw that EF has decorators (bad name Timestamp for sqlserver, good name ConcurrencyCheck for postgres)

How would one implement including those in where-clauses of generated sqls?

We are also running on Oracle. The documentation states it easy to add, but does not say how. Are there any guides o what to think of? I saw string tableSchemaName = tableNameParts.Length == 1 ? "dbo" : tableNameParts[0]; in https://github.com/activa/iridium/blob/master/src/DataProviders/Iridium.DB.Postgres/PostgresDialect.cs which I wonder if 'dbo' is relevant to Postgresql. It is to sql-server but Postgresql? It looks a bit like a copy/paste error from https://github.com/activa/iridium/blob/master/src/DataProviders/Iridium.DB.SqlServer/SqlServerDialect.cs

[activa]

I would be happy to create a driver for Oracle, but I don't have an environment setup. I'll look into that.

For the concurrency handling, let me think about that. Can you explain how you've done that in the past? What kind of queries did you run to support concurrency checking?

[bjorn-lundin]

I'd start looking for a virtual machine with oracle pre-loaded. looks like https://www.oracle.com/technetwork/community/developer-vm/index.html is one place to go. Did not try though.

In the past ... I wrote an ORM-ish thing that generated packages and tagged types - in c# namespaces and classes - from xml files describing tables and fields. Tables are a collection of fileds, but also a collection of indexes.

so - for each table - there was a class with methods

• read_all - returning a list
• read_list - returning a list from a sql-statement (had to include all fields of class)
• read_a_b_c - returning a list reading on non-unique index (where a/b/c are fields in index)
• read - read by primary key.
• update_withcheck
• update
• delete_withcheck
• delete
• insert

The _withcheck are the interesting ones. If the table contained fields with name latest_updater and latest_updated (string and timestamp) the _withcheck methods would be generated. They contain a statement like

update T set F1=:F1, F2=:F2 , latest_updater=:LU, latest_updated=now ... 
where PK=:PK 
and latest_updater=:OLD_LU 
and latest_updated=OLD_TIME. 

LU would be the value of a env-var (PROCESS_NAME) or if not set, the pid. So, you could see who updated and when - great for looking for clues why something went wrong.

The idea is of course that the where clause will fail if the values are updated by someone else after they were read by current process, but before we tried to write down our value. If rows_affected = 0 we would raise/throw an exception - No_such_row. This would be a signal to rollback and retry - possibly now with a different resultset.

To make it a bit more general, one could look at hidden fields instead. sql-server seems to have one called timestamp, postgresql has xmax, and oracle have rowid (I think) These could be read along the rest of the columns and used in the same way, included in the where clause.

seem like 'Ask Tom' is into this solution as well https://asktom.oracle.com/pls/apex/asktom.search?tag=rowid-or-primary-key

As you've seen, updates and deletes are the ones we supported, with (I'd guess ) a 20/1 ratio This is a warehouse management system. Meaning 30-60 daemons and up to a couple of hundred concurrent users. They often fight over the same records. Having FIFO/FEFO on pallets means that there is only 1 pallet of a given partid that can be the oldest... Or having lots of pickers that pick to the same orderline...

We also generated update/delete without the concurrency check in order to be backwards compatible with deployed system that did not check this. That is (in my eyes) a mistake. fix the deployed system intstead, or wrap the call. maintenance becomes much easier.

However it means that the code needs to loop over the transaction.

for (;;) {
  using (t = conn.transaction_start() ) {
    Pallet pallet = new Pallet(1234); // read 1234 from db
    pallet.weight += 25.3; // add weigth of euro pallet (kg)
    try { 
       pallet.update_withcheck();
       t.commit;
       break;
    }
   catch (No_such_Row e ) {
    t.rollback(); 
  }
}

[bjorn-lundin]

I've also seen solutions where there is a trigger on each table, updating a field - say myrowversion - every time the row get updated. As above, the field is included in the where-statement, but you should not include that field in the update clause. Thats a +1 for automatic update, but a -2 for not seeing who or when that update was carried out, and for the need of triggers.

Still - it is a good solution too.