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.
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.
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.
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.)
For more details on field behaviors, see "Field Behavior," p. 120.
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.
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.
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.
To help with multiuser 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 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.
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.
For a reference on error codes in FileMaker Pro, refer to FileMaker Pro's online help, or turn to our companion book, FileMaker 8 Functions and Scripts Desk Reference.
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.
Audit Trails in FileMaker Pro
Part I: Getting Started with FileMaker 8
Using FileMaker Pro
Defining and Working with Fields
Working with Layouts
Part II: Developing Solutions with FileMaker
Relational Database Design
Working with Multiple Tables
Working with Relationships
Getting Started with Calculations
Getting Started with Scripting
Getting Started with Reporting
Part III: Developer Techniques
Developing for Multiuser Deployment
Advanced Interface Techniques
Advanced Calculation Techniques
Advanced Scripting Techniques
Advanced Portal Techniques
Debugging and Troubleshooting
Converting Systems from Previous Versions of FileMaker Pro
Part IV: Data Integration and Publishing
Importing Data into FileMaker Pro
Exporting Data from FileMaker
Instant Web Publishing
FileMaker and Web Services
Custom Web Publishing
Part V: Deploying a FileMaker Solution
Deploying and Extending FileMaker
FileMaker Server and Server Advanced
Documenting Your FileMaker Solutions