Concurrency

You might have heard 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 oncethey're likely to trip over each other a bit. Every multiuser 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 databaseand its datacannot 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 or large workgroups. It is possible to go a long way toward ACID compliance in a FileMaker Pro databaseif 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 with any other database tool).

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 thatguidelines. 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 compliance is a goal of development to ensure data integrity. We encourage you, especially when writing scripts, to focus on delivering on these guidelines to an appropriate degree, especially in a multiuser environment.

Script Log

One technique we use for verifying processes and debugging is a script log. By building one, you better approach 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 fail 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 8, 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 pressing 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.)

It is possible to use the Records, Revert Records option to undo the creation of a record. Until a record has been committed, it exists in a temporary state, not yet visible to other users of the system. Relying on a transaction remaining unsaved until expressly committed helps ensure better ACID compliance. This is important to remember in a multiuser environment where you may be operating on assumptions established with prior versions of FileMaker. For example, if you're attempting to serially number certain records 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 values, FileMaker enables you to specify when the serial number is incrementedon 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 committedmaintaining an isolated state while you create new recordsFileMaker does not allow editing of a record 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 a field. That behavior changed in FileMaker Pro 7.

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 via a portal or related field from the context of a parent record. If you are simply editing the child record on its own table-specific layout (within its own context), just that single child record is locked.

Also keep in mind that record locking applies only to editing. You can still find locked 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 another 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 kick him off by using the Server Administration Tool, shut down the file, restart the server, or address the issue at the user's local computer.

Trapping for Record Locking in Scripts

A subtle way your database might prove error prone is in always making the assumption in scripts that the routine in question has access to all the records in the current found set. Some of the records your script needs to work with may, in fact, be locked.

A script can explicitly open a record for editing with the Open Record/Request script step. After it has issued that script command, the record is reserved for that routine, and other users who try to edit the record get a record lock error until the script (or the user running the script) releases the record. Because any attempt to modify a record results in the same condition, explicitly using an Open Record/Request script step might not be technically necessary, but we find it helpful to turn to for clarity within scripts. The more important step is deliberately checking to see whether a given record is open for editing or if some other user (or routine) has it locked.

To capture the error that results in cases where either one's 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 a record can be opened. If that doesn't work, 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 Variable [$$error; Get (LastError)] If[$$error <> 0] Show Custom Dialog ["Error"; fnErrorMessage ( "recordLock" )] // or write an error handler process here... 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 nonedit mode and unlock it for other users.

Note

Consider building error utility tables, or perhaps using custom functions, for error handling. This enables you to easily tailor error messages in a central, easy-to-edit location based on whatever value is held in $$error. The Custom Dialog step in the preceding code snippet references a custom function that presumably returns error handling text to the user.

Instead of checking simply for a nonzero 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 is a wide range of possible errors.

Multiwindow Locking

Multiwindow locking is closely related to multiuser record locking. It is possible to open a new window, via the Window, New Window menu command, begin editing a record there, and in so doing, lock yourself out of editing the same record in 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 that 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 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.

Given the fact that window locking so closely resembles multiuser record locking, testing a solution with multiple windows is an effective and efficient way to ensure that your scripts manage record-locking checks properly, without having to resort to using two computers.

Multicontext Locking Within Scripts

This problem is related to the problem of multiwindow locking. We've seen it most often in systems converted from previous versions of FileMaker Pro to FileMaker 7 or later. Suppose that you have a script that's working with a series of related tables, each in its own file (as is typical of converted systems). Your script makes some edits to an invoice record, via the Set Field script step, and then calls a script in the Invoice Line Item table to do some more work at the line item level. When that script finishes, it needs to write a little more data back to the invoice record. When it attempts to do so, you see the message that This record cannot be modified in this window because it is already being modified in a different window. Because the script on the invoice side never explicitly committed the invoice record before calling the subscript, you are indeed still editing the record in another window. Again, this error is common in converted FileMaker solutions that use cross-file scripting.

The solution is simple, if tedious: If you have made edits to a record within a script, be sure to commit those changes before the flow of control leaves your script. This means you should perform an explicit Commit Records/Requests before calling a Perform Script step to invoke a subscript, or at the end of the script.

Again, this is an issue only where scripting across multiple windows is involved, and this again is most typical of systems converted from FileMaker 6 and before.