Our infrastructure consists of ABF applications which use pessimistic
concurrency by means of table locking etc
In the attempt of upgrading those applications some colleagues have
opted for Openroad which makes the transition much easier but they
reuse the same logic.
I have opted for .NET and a complete redesign and what puzzles me is
if in the same environment can abf/openroad applications which employ
pessimistic concurrency co-exist with the disconnected model of
ADO.net. I could, but want to avoid using ado.net with pessimistic
concurrency.

Most clients use old Ingres versions like II 2.0 or 2.6 and I don't
know if there will be unpleasant surprises or problems by trying to
use the .net provider on them plus I guess that will I have to built a
bridge between the provider and ingres net vnode

What is the group's opinion on it?
thanks in advance

nikosv wrote:

It doesn't look like anyone who actually knows anything about .NET is
going to jump in, so until they do, here's an opinion.

The ABF/OpenROAD applications are written on the assumption that
consistency control can safely be delegated to the server. That is a
fundamental expectation of a DBMS server; it's the C in ACID.

As long as the .NET applications don't subvert the server by prematurely
committing (and hence revealing) incomplete transactions then the
ABF/OpenROAD won't care what they do.

If I understand it right, Microsoft's "optimistic concurrency" control
relies on read committed isolation and provides a way to detect (but
not recover from) unreapeatable reads. I am pretty sure it doesn't
commit prematurely when doing updates.

So my guess is that you aren't obviously doomed to failure if you mix
..NET "optimistic concurrency" and ABF/OpenROAD apps that rely
pessimistic locking. It is certainly worth a try to find out what
happens.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

You can also run an "optimistic concurrency" strategy using OpenROAD,
you can choose the isolation level (and lockmode) as it suits you.


--
Bodo

Judging by Roy's reply I suspect that neither of you are talking about
logical / application level locking. I was thinking in a different
direction.

My client uses c#.net and VB.net linked to MSSQL stored procedures for
internet customer activity with OpenROAD linked via EA/MSSQL for the
Financial / Banking / CRM back end. We implement a combination of locking
strategies depending on the business requirements. This application has its
roots in ABF. We haven't experienced any real conflicts other than during
some bulk updates which run for over an hour and really shouldn't be running
in a transaction environment.

For a simple table update linked to a maintenance screen, say customer
master update, "no locking" is viable. Typically there will only be the one
department updating customer details and it is unlikely for two staff
members to be updating the same customer at the same time. If it does
happen we choose to ignore the conflict and overwrite the changed details
with the most recent update.

The customer web portal provides a second place to update customer details.
Here too, we choose to ignore collisions and the final update takes
precedence. I suppose we could log the event and put it into someone's
email tray for review.

By contrast, when using the transaction screen (credit card deposits,
adjustments etc), we set a lock indicator on the customer record to prevent
other financial transactions being processed at the same time. Then reset
the lock when the transaction is completed. Yes, occasionally if there is a
screen crash, the customer account is left "locked" and there is a
maintenance screen for clearing the locks if required used only by the
supervisor.

I personally dislike the optimistic locking model in which you use a
timestamp or version mechanism. Users get really annoyed when you popup a
message to inform them they just wasted 5 minutes updating the details on
screen which are now stale because some other process has beaten them to the
punch. And you cannot reject a customer web update in the same way.

Also the developers need to be aware that the version mechanism is not
necessarily honoured for all fields in the table. For example, if the
version mechanism were implemented in our Web/CRM I would use it when
updating CustomerAddress to check for accidental collisions. But not when
the web application updates CustomerLastLoginTime - where it doesn't really
matter if the previous value is being overwritten. We certainly don't want
to lock the entire customer record during maintenance because that would
prevent customer logins. Similarly we don't want a customer login to prevent
an update to CustomerAddress. (you could argue that these two bits of data
should reside on separate tables)

For multi table transactions, say in the inventory management module when
filling an order, reserving items, updating stock counts and picked items, I
think pessimistic locking is mandatory. You can't afford to pop up a
message to say "sorry try again" every time the application detects the
stock levels change. In that case, the application cares only if the stock
count is greater than zero before it places the item into the forklift
picking queue. The transactions last only a fraction of a second.

Financial reporting is one of my favourites. With the wrong sort of locking
strategy, a GL journal update can run at the same time as a GL Trial Balance
which provides all sorts fun for the accountants. You need to decide if you
want to lock out the entire GL balances table when updating a journal or
running a report - or train the users to wait to run their reports after the
updates are completed.

Cheers

Paul

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of nikosv
Sent: Thursday, 2 July 2009 6:03 PM
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] Optimistic concurrency in a pesimmistic environment

Our infrastructure consists of ABF applications which use pessimistic
concurrency by means of table locking etc
In the attempt of upgrading those applications some colleagues have
opted for Openroad which makes the transition much easier but they
reuse the same logic.
I have opted for .NET and a complete redesign and what puzzles me is
if in the same environment can abf/openroad applications which employ
pessimistic concurrency co-exist with the disconnected model of
ADO.net. I could, but want to avoid using ado.net with pessimistic
concurrency.

Most clients use old Ingres versions like II 2.0 or 2.6 and I don't
know if there will be unpleasant surprises or problems by trying to
use the .net provider on them plus I guess that will I have to built a
bridge between the provider and ingres net vnode

What is the group's opinion on it?
thanks in advance

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

Paul White wrote:

I can assure you I definitely wasn't. That is something entirely
different and as it is part of the business process it should be coded
explicitly.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

by using events like RowUpdating which is fired for each row before
the update is send to the server which allows you to check for
violations of row based business rules and subsequently throw an
exception. Furthermore for each row that does not get updated hence
the optimistic concurrency is violated, it throws a
DBConcurrencyException which you can handle at that moment by aborting
the transaction or letting processing continue with the rest of the
rows; afterwards you collect all erroneous rows where you can decide
upon a course of action which usually is to inform the users that the
update has failed and maybe allow them to do data merging. So as you
point out, it does not commit prematurely, does detect unrepeatable
reads and provides some sort of recovery but I am not sure if that is
the kind of the recovery you have in mind.

If I am going to use a version column for implementing optimistic
concurrency then I will have to re-structure the tables for adding
that column and the other application might not be so forgiving if I
do; or hide the column but will have to write a trigger which would be
fired by the abf application for updating the column, but a trigger is
synonymous to overhead!!
As I see the situation currently, I’ll go with the SqlCommandBuilder
concurrency model which compares the values of every column of the
table with those in the disconnected cache; safe but not efficient

nikosv wrote:
[OT] Just to be clear, the above is all client-side. The server doesn't
send any events or throw exceptions. It just receives an UPDATE from the
client WHERE the timestamp is T, and responds with "N rows affected". If
N is not the intended value, the client deals with it.

--jkl

On 9 Éïýë, 12:50, "James K. Lowden" <jklow... (AT) schemamania (DOT) org> wrote:
yes I'm sorry if it did not make it clear