Concurrency

You might have heard of the term concurrency as it relates to databases. It refers to the logic and behavior of database systems when two (or more) users attempt to interact with the same information. A simple metaphor might be two people trying to use a phone book or dictionary at once. Every multi- user database platform has to address this issue.

Certainly it'd be easiest to simply restrict using the database to one user or function at a time, but clearly that's an unrealistic solution.

The ACID Test

To address issues of concurrency and transaction integrity, database engineers have developed what has come to be known as "the ACID test." Database software needs to pass this test to completely manage concurrency issues. ACID stands for atomicity, consistency, isolation , and durability ; these four terms describe the features and requirements for processing transactions in a database system. If a system does not meet these requirements, the integrity of the database ”and its data ”cannot be perfectly guaranteed .

In the context of databases, the term transaction relates to a single logical operation comprising one or more steps that results in data being posted to the system. Examples might include committing a new record to the database, performing a script that calculates summary information, or in real-world terms, completing the multiple steps of debiting one financial account and crediting another. The ACID test exists to ensure such transactions are reliable.

FileMaker Pro databases, unfortunately , do not fully meet ACID compliance, nor is it realistic to develop a solution in FileMaker that perfectly does. FileMaker Pro scripts can be interrupted (a machine crash or a force-quit of the application) and as such it is possible to leave a transaction half completed.

We're including this section not to point out a shortcoming of FileMaker, but rather to illustrate some important guidelines on how you should consider building solutions for critical business systems for large workgroups. It is possible to go a long way toward ACID compliance in a FileMaker Pro database ”if it's properly engineered. It's also quite possible to build a FileMaker Pro database that leaves wide opportunity for data integrity problems to crop up.

As consultants , we're pragmatists. Often the craftsman in all of us yearns to build the world's most perfect system, but in reality there are trade-offs in complexity, time, and flexibility to consider. We use the guidelines that follow as just that ”guidelines. By identifying the criticality of certain data and using sensible safeguards to ensure its integrity to the degree possible, we are able to cover all but the most extreme cases of database failures.

• Atomicity ” Atomicity requires that transactions be completed either in their entirety or not at all. In other words, a logical routine (say, crediting one account and debiting another) cannot be left half done. In FileMaker Pro terms, data is either committed or not committed to your database, a script needs to reach its logical conclusion, and a calculation function stores and indexes its results properly. Although a script can be interrupted, it is important to approach atomicity by writing scripts that conclude whatever routines they're designed for.

• Consistency ” Consistency ensures that your database is left in a "legal state" at the beginning and end of any given transaction. This means that the transaction won't break any of the rules, or integrity constraints, of the system. This often can encompass business logic: An example might be that all financial credit transactions be positive numbers .

• Isolation ” Transactions in mid-process are never exposed to other processes or users. In the credit/debit example, a user should never see a credit appear on one account before the debit has been posted. Likewise, an account balance report should not be allowed to run when a credit or debit is in the midst of being added.

• Durability ” After a transaction has been performed and completed, the information resulting from that process needs to be persistent. It should be saved with the database, and if someone pulls that computer's plug, the information is then still present in the file.

ACID is a goal of development to ensure data integrity. We encourage you, especially when writing scripts, to focus on delivering on these guidelines, especially in a multi-user environment.

Script Log

One technique we use for verifying processes and debugging is a script log . By building one, you approach better atomicity and are able to identify cases where it fails.

In large, complex solutions where transaction integrity is vital , it may be warranted to create a process that causes all scripts to write log records to a separate table (often in a separate file as well) when they start and again when they are successfully completed. It's possible to track other data as well: who initiated the script, on what layout the user was, which instance of a window was in use, timestamp data for start and end (for performance and troubleshooting purposes), and potentially any data the script manipulates. This is not to be confused with an audit trail, covered later in the chapter. Audit trails enable you to record all data transactions in a database. A script log is a means of confirming that your functional routines are completed properly.

By adding a script log to your system and periodically checking it for incomplete conclusions, you can identify cases where scripts failed and manually address such issues when necessary. By definition, if a script log "start" entry doesn't have a corresponding "close" entry, it failed ACID's atomicity test and possibly the consistency test as well.

TIP

One final note on script logs: We encourage you to create a single flag in your database that when "turned off" disables all script logging in your system.

Commit Versus Create and Serial IDs

In FileMaker Pro 7, data is committed (saved) after a user exits the record, either by clicking outside a field or by performing a range of other actions such as running a script, changing modes, changing layouts, or hitting a "record-entry" key (the default is the Enter key, but field behaviors can be changed to allow the Return or Tab keys as well).

For more details on field behaviors, see "Field Behavior," p. 116 .

It is possible to use the R ecords, R evert Records option to undo the creation of a record. Until a record has been committed, it exists in a temporary state. Relying on a transaction remaining unsaved until expressly committed helps ensure better ACID compliance. This is important to remember in a multi-user environment where you may be operating on assumptions established with prior versions of FileMaker Pro. For example, if you're attempting to serialize some form of data and two users create two records at the same time, it is possible that one will commit the record in an order different from that in which the records were initially created. It is also possible that a user will undo his or her changes with a "Revert Record" command and leave you with a gap in your serialization.

In the case of auto-entry serial IDs, FileMaker enables you to specify when the serial number is incremented ”on creation or on commit. This enables you to control auto-enter serialization; however, it does not protect you from other assumptions. For example, if you're relying on GetSummary() calculation fields to keep track of an incremented total, remember that the calculations that control this are evaluated and displayed only after a record is committed.

Record Locking

Just as a record is not saved to your database until it is committed ”maintaining an isolated state while you create new records ”so too will FileMaker not allow editing by more than one person at a time. In this way, FileMaker Pro meets the isolation test of ACID for posting data. Record locking exists to ensure that no two edits collide with each other (such as when multiple users attempt to edit the same record simultaneously ).

After a user begins editing a record, FileMaker locks that record from other users and script processes, and (when not captured and suppressed by a script) presents users with an error message if they attempt to enter or change any data in that record.

It's possible to place your cursor in a field and still leave the record unlocked (safe for other users to enter data into the same record), but at the point at which you actively begin typing, that record essentially becomes "yours" until you either commit or revert it.

NOTE

For those of you familiar with prior versions of FileMaker Pro, remember that those versions locked records as soon as a user clicked in to a field. That feature has changed in this version.

Locking applies to related records in portals as well. If you are modifying a record in a portal row, that record's "parent" is also locked. This behavior occurs only when the related child record is edited in a portal; if you are simply editing the child record on its own table-specific layout, it is just that single child record that is locked.

Also keep in mind that record locking applies only to editing. You can still find those records, view reports with them included, change sort orders with locked records in your found set, and even export data. Only editing is protected.

If a user has a record locked and you get an error message, you can ask that user to release the record to you. The error dialog appears in Figure 11.1, along with a resultant message that user might see if you send one.

Figure 11.1. You see this message if you try to edit a record someone else is modifying. If need be, use the Send Message command to ask for control.

The one downside to record locking is that you cannot force a user out of a record remotely through FileMaker Pro. If someone begins editing a record and then decides to fly to Tahiti, you need to shut down the file, kick him off by using the Server Administration Tool, restart the server, or address the issue at the user's local computer.

To help with multi-user account testing, refer to "Use Re-Login for Testing Access and Sessions" in the Troubleshooting section at the end of this chapter.

Trapping for Record Locking in Scripts

A subtle way your database might prove error prone is in always making the assumption in scripts that you have access to all the records in your found set.

You can explicitly open a record for editing with the Open Record/Request script step. After you've issued that script command, the record becomes "yours," and other users who try to edit the record get a record lock error until you release the record. Because any attempt to modify a record results in the same condition, explicitly using an Open Record/Request script step may not be technically necessary, but we find it helpful to turn to nonetheless. Admittedly, the more important step is checking to see whether you are locked out.

To capture the error that results in cases where either your current privileges don't allow editing of the record in question or the record is locked by another user, we recommend testing first to see whether you can open a record. If that doesn't work, then deal with the result prior to attempting an edit. Use the Open Record/Request script step followed by a Get(LastError) check. Here's how it might look:

 Set Error Capture [On] Open Record/Request Set Field [MyDataTable::gError; Get (LastError)] If[MyDataTable::gError <> 0]    Show Custom Dialog ["Error"; errorCodes::Message] End If //Execute your "real" script here... and don't forget to commit your record at the end. 

Use a Commit Record/Request script step at the end of your script to release the record back into non-edit mode and unlock it for other users.

NOTE

Consider building error utility tables into your systems; this enables you to easily tailor error messages based on whatever value is held in gError , including those you create for yourself. The Custom Dialog step could just as easily be "Sorry, you don't currently have access to that record. It could be locked or you may not have sufficient access privileges."

Instead of checking simply for a non-zero error, you could also write a series of If -> Else If script steps checking for errors such as 301 (Record is in use by another user), 303 (Database schema is in use by another user), and so on. There are a wide range of possible errors.

For a reference on error codes in FileMaker Pro, refer to FileMaker Pros online help.

Multi-window Locking

Multi-window locking is closely related to multi-user record locking. It is possible to open a new window, via the W indow, N ew Window menu command, and lock yourself out of editing a record from your original window. If you are actively editing a record that has yet to be committed and you try to edit the same record in another window, you'll see an error message that says, "This record cannot be modified in this window because it is already being modified in a different window." FileMaker tries to ensure you're not losing data or edits you're in the midst of creating.

The point here is that a user can lock himself out of a record. Someone might not realize he's left a record in an edit state before moving on to a new window. The simple answer here is simply not to try to edit a record in two places at once. A user would have to go a bit out of his way to encounter this problem. If you've scripted routines for creating new windows with a script, you may want to include a Commit Record/Request step before opening the new window.