Newsletter - About Opportunistic Locking

[Ray Fernandez]

Although I've rarely use Alpha products nowadays, I do enjoy reading the monthly newsletters to see what's going on.

I need to comment on Cal Locklin's article on Turning Off Opportunistic Locking.

If you go back about four years, you will see that I've posted this same topic on the newsgroup.

Turning off Opportunistic is not a good idea, in fact, it's like cutting off the arm because your finger hurts. By turning off opportunistic locking your server is running at a disadvantage, all programs written that supports the Windows logo will be seriously reduced in performance. This is also true not only for programs which are administered by the server but by any clients which are constantly making request to the same server.

The problem here is the old database format that is still being used by Alpha, glad to see this will change in version 6. It just doesn't support cache edits or insert updates.

The Borland database engine, which by the way it's FREE, also uses DBase and Paradox as a native format, however this problem does not exist within the Borland database engine using the same DBase format.

Why? You may be asking.

Because you have the ability after a record post to issue the following command:

procedure TForm1.Table1AfterPost(DataSet: TDataset);
begin
DbiSaveChanges(Table1.Handle);
end;

This will force a Flushing of the caches, your indexes will always be up-to-date and extremely stable.

The good news is if Alpha version 6 lives up to all the hype, you can substitute the existing codebase DBase backend with the Borland database engine backend (FREE) , still keeping your application in a Dbase format without all the index problems, because you can issue the above command after record post in Alpha. Of course this is if version 6 lives up to all the hype.

A better solution TODAY, if for the developers at Alpha to create a function in Alpha the flushes the caches on any record update. This way you can do the same thing that people using the Borland database engine with DBase have been doing for the last 6 years. You guys need to put the pressure to get this done.

One last thing about turning off opportunistic locking (again not recommend). It needs to be turned off at the server and every single client, all you need is one computer running opportunistic locking on - for this to fail.

Very glad to see that Alpha might have a chance to compete with the big boys. Sinces other Alpha users are now entering problems that I've encountered many years ago especially with client-server I will be more than happy to advise you with proven solutions.

Still around,
RF-ARS-Motorola

[Bill Parker]

RE: Newsletter - About Opportunistic Locking

Ray,

Thanks for your input on this. It would be wonderful if Alpha could find a way to implement this for dbfs.

Cal's experience with this "fix" has been positive so far, and I have a couple clients that are plagued with the problem intermitently. In my case, Alpha is the primary application they run. So the cure (and any downside) seems to be much better than the affliction.

Any thoughts about forcing a buffer flush from xbasic in the OnSave event?

Bill.

[Ray Fernandez]

RE: Newsletter - About Opportunistic Locking

Bill,

"""Any thoughts about forcing a buffer flush from xbasic in the OnSave event?"

First let me say that the underlying database engine must support this. I would call CodeBase tech support and see if this is supported through an API call. I'm pretty sure it is because for the longest time they were competing against the Borland database engine as an alternative to Delphi users.

If there is an API call to the underling database engine just issue a call from within Alpha.

When I first encountered this issue (still working with file servers at the time, about 6 years ago ) this was a major issue in the paradox community.

I'm meeting with a another programmer over the weekend, will bounce it off his head.

PS: If you still want to disable it at the registry like Cal recommend, Act2000 has a utility that would do all the registry entries for you. Both on and off.

Sincerely,
RF-ARS-Motorola

[CALocklin]

RE: Newsletter - About Opportunistic Locking

I just saw this for the first time wanted to add my 2 cents and ask for comments from others.

I was unaware of the issue Ray brought up about this being a disadvantage to all other Windows programs (I didn't see any mention of it in the MicroSoft documents but that may not mean much - after all, it is MicroSoft.) so I'm glad he pointed it out. It was clear that it could be a disadvantage if you were also running a client server database but (a) most of my clients aren't running client server databases and (b) a performance hit on the occasional client server database might not be as bad as constant index problems on the A5 database.

Also, I think something like Word could take a huge performance hit before anyone would notice the issue. Most programs don't use anywhere near the resources that a database does.

Combining the above with the fact that it has virtually eliminated index issues every place I've used it so far and nobody has yet complained about any other performance issues, I'll take the chance on some performance hits on other programs. But I will watch for indications of other performance issues now.

What I'd really like is to hear from others who have tried turning off Opportunistic Locking. Anyone? Successes? Problems?

(By the way, the level of problems also seems to be associated with the network itself. The worst I've seen so far was a user who had a Win98 machine as the 'server' and an XP machine as the workstation. This resulted in very slow performance on the XP and index issues 3-5 times a day even with only 1 user on the XP machine until we turned off the Op Locks. Now the XP is still slow but she hasn't updated indexes since just before the Alpha Conference. Another system running the same app with 4 users working most of the day would only encounter index issues about once every day or two. They, too, are no longer updating indexes.)

Cal Locklin
www.aimsdc.net

[SwissCharles]

RE: Newsletter - About Opportunistic Locking

Cal,

Yes, I've made this 'workaround' many times since a looong time. My experience in this regard mainly comes from the DataEase world - I am only about now getting my feet wet , testing A5 in our networked environment. Like you, I've never had a user complain about performance after I pulled this trick. My 'biggest' System is used by 10 users concurrently in a Win2K environment.

This does by no means speak against Ray's suggestions though. Because it's like he says: One WS not being modified accordingly can rain on your whole parade! And we've had at least one occasion, where applying one of the ever soooo many security patches of Micro$ofts did change some of those keys back to default!!

HTH

Charles
Basel, Switzerland

[jeffemery]

RE: Newsletter - About Opportunistic Locking

Cal, and all -

For years I have groused about index corruption and the associated problems it created for us on an on-going basis. After the last conference I returned home and turned off Opportunistic Record Locking and immediately our index corruption problems dropped. We almost never experience index corruption now. Of course, even 1 occurance of a blown index should be unacceptable. Across our 20 workstations there has been no "noticeable" degradation of performance of other applications, although we do not run any client/server DB applications either.

That being said, this is a problem that should have been fixed a long time ago and I am looking forward to the day when we don't have to resort to performance degrading workarounds. I would certainly be interested if there is an api_ solution to flushing cache.

Thanks to all who have focused on this problem.

[Elke]

RE: Newsletter - About Opportunistic Locking

Taken from their site at http://www.bdesupport.com/stability.htm

Tips for making the Borland Database Engine more stable in the field:

Disable "Opportunistic Locking"
If you are using the BDE on Windows NT or Windows 2000, you may experience problems with "opportunistic locking". Windows 2000 and Windows NT have special locking and caching properties that differ from Windows 95/98 that can result in "Permission Denied", "Network Initialization Failed", or other errors that refer to PDOXUSRS.NET, as well as corrupt tables. The following articles from the Microsoft Knowledgebase discuss this issue and how this situation can be corrected by making several registry entries, allowing multiple users to access the shared database files and network control files used by the database engine:

[Ray Fernandez]

RE: Newsletter - About Opportunistic Locking

Elke - let me know if you need more info on this. I can send you a lot more.

http://bdn.borland.com/article/0,1410,20563,00.html
http://bdn.borland.com/article/0,1410,17801,00.html
http://bdn.borland.com/article/0,1410,17913,00.html


Preventing Data Corruption
As you can see from the preceding example, managing all of the access to the BDE is a lot of work. It is much easier if you permit data-aware components to do some of this work for you. For example, if you place a Table component onto a form, and then open that table, the BDE will already be initialized, a database handle will be established, and a cursor handle will also exist. (The database handle can be obtained from the DBHandle property of the table, and the cursor handle can be obtained from the Handle property.) Furthermore, when the application no longer needs the BDE, the data-aware components take responsibility for releasing the handles and deactivating the BDE.

While a number of BDE calls require a database handle, a table handler, or both, you do not need to acquire these using BDE calls directly. Instead, you can use the DBHandle and/or the Handle properties of your Database and/or DataSet components. This is done in the following project, which demonstrates a technique that is very valuable in high transaction environments. Specifically, it can all but eliminate index out of date errors when Paradox tables are used in multi-user applications where there are many simultaneous postings to the tables. This project is named DBISAVE.

In order to improve performance, the BDE often caches edits to local tables. Consequently, a write operation may be delayed a short while after a record has been explicitly posted. The one drawback to this is that if there is a system failure following the record post, but prior to the BDE writing the cached edits, the local may become corrupt.

Using BDE API calls, you can instruct the BDE to immediately write posted changes to tables, reducing the likelihood that a system failure will damage tables.

There used to be two techniques that you could use for this purpose, but only one of these is considered acceptable with the current versions of the BDE. The acceptable version is to create an AfterPost event handler for each of your DataSet components. From within this event handler you call DbiSaveChanges. This function has the following syntax:

function DbiSaveChanges(hCursor): DBIResult;
The hCursor argument corresponds to the Handle property of a DataSet. The following AfterPost event handler demonstrates this technique:

procedure TForm1.Table1AfterPost(DataSet: TDataset);
begin
DbiSaveChanges(Table1.Handle);
end;

[Robert Picard]

RE: Newsletter - About Opportunistic Locking

Hi Cal

I tried looking for the Newsletter containing the article with no success.

Could you provide the link ???

Thanks

Robert