Pdx 9 - Record Locking and Referntial Integrity

[normanb]

I'm about to start design of a new networked application form requiring multiple, concurrent user data entry sessions and have noted the warning in the manual about Paradox imposing full locks on the hierarchy of LINKED parent-child tables in the data model, when one child record is inserted/locked for editing.

I intend to get around this by filtering in relevant child records in tableframes on the form using setGenFilter and using unlinked tables in the data model, to avoid user-waits during data entry generated by automatic locking.

I also want to use Referential Integrity between my unlinked tables in the data model. My question is - will using RI impose any difficulties/constraints on this setGenFilter method of showing data subsets?

 

[footpad]

normanb,

First off, set aside the idea of using setGenFilter; use setRange instead. While this requires additional indexes, the performance gains are tremendous, for setGenFilter esentially runs separate queries in the back, which requires very expensive table scans. Ranges, on the other hand, work exclusively with the data in the index.

Second off, I would avoid mucking about with trying to optimize the locking process unless you're sure you understand why the current process works and how to improve upon it. In more than twelve years of working with Paradox, I can only think of a handful of people that I'd trust to do that well.

To answer your other question, yes, RI requires locks to accomplish its goals. Sometimes, these are more restrictive than necessary and a skilled developer can improve on that by replacing the built-in RI with code-based alternatives involving tCursors, ranges, and so on.

However, it is a code-intensive process...meaning expensive in time and development budget. If you're working under any sort of deadline pressure, I'd rather you started with the built in tools and then see if you need to improve on those onces you've determined if they are providing any unacceptable bottlenecks.

It is important, though, to start with a thorough understanding of ranges, though. The performance differences will be staggering.

Hope this helps...

-- Lance

 

[normanb]

lance,

Thank you for that info - I have already begun the data capture form so will replace setGenFilters with setRange using indexed fields and look forward to the performance gains.

I have resorted to unlinked tables in the data model because of the warning Paradox gives about linked master/detail tables in the data model being automatically locked. The warning says that the master is locked whilst a detail table is undergoing insert ot edit. As the basis of my application is to capture a new master record and immediately enter several detail records over say, four/five minutes, before releasing the master, I didn't want to prevent other users from entering other master records during this period.

But have I missed a more general point here? Is there a technique out there which allows data model linked master/detail tables to be used and for detail records to be inserted whilst not locking the master?

(I am using primary key sequences rather than autoinremental primary key fields.)



normanb

 

[footpad]

normanb,

Be aware of that warning, but not too worried. The Paradox format uses record-level locking, rather than or file level locking, so you can enter detail records over time while still allowing others to enter new master records and their details. Put another way, the warning sounds severe, but it's something that Paradox actually handles very closely to the way we (as programmers) would like it to, so it isn't something you normally have to worry too much about.

There is a lock placed against the master record, but it's not an exclusive lock that prevents others from adding (or working with) other master records.

Paradox uses a hierarchy of locking, each more restrictive. The lock I think you're worried about is more of a "in-use" lock rather than an exclusive lock. It prevents the master record from being deleted while your adding details to it, but it doesn't prevent work on other master records.

Truth be told, it's something that's never really surfaced in my applications and I've had as many as a few hundred simultaneous users working in my applications. I've pretty much trusted Paradox to do the right thing and, in most cases, it has.

The unlinked details can provide some performance benefits, however, you will need to remember to override the dataInsertRecord action (if you're using UIObjects) and to copy the foreign key values during that process (probably after the Default behavior) in the detail tables.

The best way (ime) to manage this type of process works something like this:

1. Enter new master record.
2. Verify, post, and then unlock it.
3. Enter details as needed.

And your choice of avoiding auto-increment fields is a good one, for the Paradox format doesn't assign an AI value until the record is actually posted (hence the steps listed above).

Oh, one other technique comes to mind that you might find useful. Instead of placing the detail records on the same form as the master record, you might consider breaking the process into separate forms. Use a primary form for navigating, adding, and selecting master records and then (once the master record is taken care of) open a form containing the detail record information. Let the user interact with that as needed and then use a third form/report display everything when it needs to be pulled together. I've found this approach to be very helpful.

(People tend to cram too much into a single form. It's nice and convenient, but it adds a lot of overhead and can make things more difficult in the long run. As a former employer used to say, "Obey the L(east) A(mount of) W(work principle)." IOW, do the simplest thing you can at first and add complexity only when it's needed.)

Hope this helps...

-- Lance

 

[normanb]

Thanks for that information which addresses my question completely.

It is so valuable to access a view founded on long personal experience.

I very pleased to have discovered this Paradox community. I love the program (despite its foibles!)


normanb
west yorkshire, uk