Row/Table Buffering 2

[Scott24x7]

I decided to start a new thread as this spun off from one of my other discussions.
As most of you know I'm kind of "returning from the dead" in VFP, having not really been a serious developer in over 15 years now, but due to a decision to leverage every advantage I can for a start-up I've come out of retirement.

To that end, some years ago (and I don't really remember why, so this is part of why I'm bringing this up now), I shifted away from SCATTER/GATHER (with a few exceptions though even those may have been due to not understanding a way to accomplish the same thing with buffering), to buffering.

I have adopted Optimistic Table Buffering "5" in the DE as a strategy throughout my application (with a few exceptions where they are "Read Only" and then I set them to "0 - NONE").
Now I know I picked this for some reason, but maybe not for the right one and years later I've no memory of why I went this route. In my applications it does work... but that said, I also don't have a large multi-user environment, only a few simultaneous users.

So I'm curious to hear other philosophies and points about why you might choose one over another, and is there a "best practice" in this regard? (Or is that another religious debate?)


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Mike Lewis]

Scott,

As you know, buffer mode 5 means optimistic table buffering.

Choosing table buffering rather than row buffering is usually a good choice. It lets you buffer updates to multiple records, and gives you the ability to save or revert them all in one hit. This is particularly useful if you are editing a grid. There's nothing you can do with record buffering that you can't do with table buffering, but the reverse is not the case.

As for the choice between optimistic and pessimistic, this is really a business decision rather than a programming one. You've got to think about the implications of a collision, that is, when two (or more) users try to update the same record at the same time. And, in particular, what would happen if one of the updates overwrites the other.

To give an example. If your application is selling seats on a flight, and two users both try to sell the last seat at the same time. Should you build in a check for that, and decline to accept one of the bookings even though the user has gone through all the steps of entering the itinerary and confirming the payment method? Or should you accept both bookings, and rely the airline's over-booking procedure to get rid of one of the passengers?

You see what I mean about it being a business decision.

With pessimistic buffering, you don't have to make that sort of decision. You can fix it so that it is physically impossible for two users to update the same record at the same time. But that brings its own problems. It means that a passenger might phone the airline to book a seat, only to be told that he can't do that just now because another agent is dealing with a booking for the same flight. Clearly, that would be unacceptable.

On the other hand, for simple updates like entering a change of address, that sort of thing is not likely to be an issue.

My advise would be to go for optimistic if, and only if, you know the business rules for dealing with a collision. If you can't formulate a satisfactory rule, you will have to fall back on pessimistic.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Scott24x7]

Mike,
That is really a brilliant explanation. Thanks.
It does make me wonder, is it possible (logical/reasonable) to change the buffering method during the edit? For example, if one field is changed, then optimistic is ok, but for another field change pessimistic is preferred. (Could set at the Lost Focus or Valid or even When on the fear that entering the field means it COULD be changed)? I try to avoid "flaky logic", but I'm wondering if that's possible given the right situation?


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Olaf Doschke]

That's quite special needs, but since optimistic locking simply means no locks until you save, you can use the LOCK/RLOCK command, which is what pessimistic locking does automatically in the moment you edit some field. So you can bind the locking to certain controls, but there is no such thing as field by field locking, the minimum lock you set is per record. So if you need finer granularity think about splitting the table.

Also take note: Buffering has a total time span from reading data to saving buffer changes, The buffer itself from modifying the first field of a record to saving it, the optimisitc locking only blocks shortly, but still causes conflict with other users edit going back to initially read in OLDVAL(), unless you use tableupdate with force option. So even without pessimistiic and manual locks you can detect conflicts, you even can detect them before TABLEUPDATE, as you can check if OLDVAL and CURVAL differ, so there already is a change in the DBF aside of the local modification. Note once more CURVAL is not the current value in your local buffer, it's what's currently saved in DBF or remote table.

This is more valuable than locking is, see why in the following not unusual situation - meaning this is no corner case of very sepcial timings, it's possible easily:

user (A) begins editing in buffermode 5
(A) edits a record #8 in field1 (not crucial for parallel editing, so you don't RLOCK)
user (B) begins editing in buffermode 5, too.
(B) edits the same record #8 in field2 (crucial, so you RLOCK)
Now that doesn't mean changes in user A's buffer are reverted. His foxpro app is not trying to lock until he saves, the buffer continues to work.
(B) saves changes in record #8 and UNOCKSs the record.
(A) continues editing and finally saves, too. Since there was a temporary lock, but it's already unlocked, user A did not get a locking problem.

The problem here is, user (A) has no idea of User (B)s changes, his editing session began with the same initial values user (B) saw. The good thing about buffering is, that still does lead to a save conflict, as the tableupdate sees the curval is differing from what was read in from user (A), so there was a concurrent change. You can now present to user (A), what user (B) saved, as you can see the changes in the DBF. That only is possible because the starting of the buffering means remembering the oldval.

The thing to learn from this is buffering gives you the better info than locks. It detects problems, even though there is no concurrent lock. If you want locking to solve your concurrency problem, you have to lock for each edit in the first place, so any edit, no matter if it begins with crucial field or not. If you only start locking when crucial fields are modified, you don't reach out to users already having started editing, they are not blocked off. So preventing concurrency with locks means each edit must cause a lock and block other users, even if some edits would be possible concurrent.

Allowing concurrent editing via optimistic locking - meaning no locks until save - you can detect problems when saving and also for that matter it's better than locks, it takes more special handling, though. What you never can, though is detect a user already edits while you save a change, the conflict does not reveal then, but at the moment the other user saves later. That's because you can't read from all other buffers. This is a reason against buffering, but only when looking shortsighted at it, non buffering causes too much changes in a dbf and all cpu/ram/hdd/network caches to be invalidated. You have the problem of more single changes needing to propagate all the time being a network load. Buffering does solve that and you still see problematic concurrency soon enough. For exampe going back to the booking problem Mike suggests, if this works in the internet you can't really lock anyway, as you can't reach out to the seat plan already loaded into anyone elses browser. You can only confirm a binding reservation at the time any user choose a seat and gave all payment infos and himself confirmed and check if there is no other seat reservation, which might came in meanwhile somewhere between now and the time the empty seat was presented. It's a problem you can only resolve later anyway. The way DBF locks work at best works in a LAN, not world wide.

Bye, Olaf.

 

[Mike Lewis]

I'd be very cautious about changing the buffering mode in mid-edit. It's not something that is normally done, and it could cause unexpected side-effects. In particular, the act of changing the buffer mode itself causes the buffers to flush, which is almost certainly not what you want.

In the scenario you described, I would stick with optimistic throughout the process.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Only being ablet to change buffer ode, when buffers are either reverted or flushed by saving is the main argument to never switch buffer mode.

But all I said means it's not even making anything better to manually lock already partially edited records, you don't block other already buffered changes that way, only an inital lock guarantuees no one else can do a change before you commit, so even if only some fields are reason to lock, you better use pessimisitc locking from beginning, in that case. And overall the save time is where you profit from the possibe tri state of a field with oldval, curval and bufferval.

Bye, Olaf.

 

[Scott24x7]

Ah, thanks, that is a great point (about changing modes flushes buffers), and no that would not be the result I wanted.
Really appreciate this discussion and the explanations provided.

I think you're right Mike, I'll stick with Optimistic table buffering for the most part. Our application doesn't have intensive arm wrestling for data, it would be extremely rare that 2 people would try to edit the same record at the same time. And if they did, an optimistic approach would be the best method.



Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."