Re: [Info-Ingres] Optimistic concurrency in a pesimmistic environment
- From: "Paul White" <shift7solutions@xxxxxxxxx>
- Date: Fri, 3 Jul 2009 00:51:32 +1000
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@xxxxxxxxxxxxxxxxxxxxxxxxx
[mailto:info-ingres-bounces@xxxxxxxxxxxxxxxxxxxxxxxxx] On Behalf Of nikosv
Sent: Thursday, 2 July 2009 6:03 PM
To: info-ingres@xxxxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres
.
- Follow-Ups:
- References:
- Optimistic concurrency in a pesimmistic environment
- From: nikosv
- Optimistic concurrency in a pesimmistic environment
- Prev by Date: Re: Optimistic concurrency in a pesimmistic environment
- Next by Date: [Info-Ingres] ODBC trace
- Previous by thread: Re: Optimistic concurrency in a pesimmistic environment
- Next by thread: Re: [Info-Ingres] Optimistic concurrency in a pesimmistic environment
- Index(es):
Relevant Pages
|
