ACID – Is FileMaker Atomic? (Part 2)

The atomicity property of the ACID model mainly involves how changes to records are committed or discarded. Situations where a solution explicitly manages the FileMaker’s commit or revert, will often be described as transactions. Related FileMaker features include:

• Commit Records/Requests [ ] step
• Implicit commit (auto-commit) caused by context altering steps or a user driven events
• Open Record/Request [ ] explicit record lock
• Implicit record lock caused by editing steps like Set Field [ ] or users driven events
• Revert Record/Request [ ] step or menu command
• Various record creation and deletion steps
• Functions Get ( RecordOpenState ) and Get ( OpenRecordCount )
• Error handling functions Get ( LastError ) and steps Set Error Capture [ ] and Allow User Abort [ ]

A very simple example for an atomic transaction might look like your bank’s account transfer. If you initiate a transfer of $100 from your checking account into your savings account, it would be an important transaction to perform atomically. When reduced down to the most important actions, you want the record maintaining your savings balance decreased and checking balance to be increased. There is meaning in the word “transfer” that implies the operation on these two accounts is indivisible. Suppose these activities were done one at a time. What if the savings account was decreased and then something prevented the increase of the checking balance? You would be out $100. Ultimately the edit of both records need to be atomic. A problem with either account should prevent any portion of the transfer from occurring.

Some other venues also discuss crashes in the context of atomicity. This will be covered later in a durability post.

FileMaker provides very specific features targeting behaviors that we would describe as atomic.

Human GUI Interaction

Even before we look at scripting, end users are likely already operating with atomic transactions on your interactive layouts. Suppose you have a layout corresponding to your Product Catalog table. That layout likely has fields for a product title, style code, price, etc. When the user starts editing a field, their client machine takes out a lock on that record. The OpenRecordCount increases to 1, and their keystrokes appear the first field. The user may tab into any other catalog field, continuing edits to the same record. At this point, the user is editing a cached copy of the record that was recently retrieved from the server. Other than taking out the lock, there isn’t any additional transactional communication with the server. Finally the user has a decision to make about their edits: save or discard. Typically the user saves their edits by moving on to another task, we will describe this as an implicit commit. When this commit occurs, revised record information is delivered to the server, committed, record lock released, and available for communication to other clients. Suppose the user realizes they have been editing the wrong record, they may use the “Revert Record…” menu command to discard their changes. In this situation, draft record information is discarded, the server is notified to unlock the record without making any modifications to the server’s record.

FileMaker also supports transactions across multiple records. Suppose that Product layout also included related fields from another table. Additional foreign records follow the same pattern of locking. A user might tab or click into numerous fields, editing data as they go. A foreign field might be individually on the layout or in a portal. With each new foreign record that the user attempts to edit, a lock will be taken out and the OpenRecordCount will increase. The commit (whether implicit or explicate) will save or revert all the pending changes. It is conceivable you could have 10s or even 100s of locked records with pending modifications.

Further, when the database includes relationships that allow for the creation or deletion of foreign records, then it is possible that your pending transaction could include a mix of record creation, updates, and deletion.

It is also important to realize that FileMaker retains a separate transactions for each window. It is possible to start a transaction in Window A, then switch to Window B. While in Window B, you could start and finish (commit) an unrelated transaction. Finally you can return to Window A to complete the first transaction. The record locks are isolated to each window, so that you may not edit the same record in two different windows.

Scripted Transaction

The scripting model for FileMaker conforms to most of the conventions in the event driven form we discussed above, except it is often unnecessary to have any objects on the layout you utilize. In most scripted transactions, developers should plan to handle every conceivable error condition. Errors could easily occur with the filesystem (a locked file on the server), application level security (privilege sets), lock conflicts, schema level validation, and more.

Watch this 7 minute video for a walkthrough of a sample scripted transaction that interacts with multiple records from multiple tables.

The general flow of the script starts by suppressing any event interaction to or from the UI. We get into a habit of checking for errors after each step that interacts with the server. Next we get into context, using a global selector field to locate the record we wish to update.  Once the record is located, we attempt an edit which will take out a lock on the server’s record. Without committing, the process is repeated for additional record(s). Finally we request an explicate commit, which will update both records together. If at any point an error is detected, we abandon the transaction and revert (discard) all edits.

Conclusion

Does FileMaker demonstrate atomicity? YES. I think we can put this down as a solid characteristic that is a core component of the database engine.

Caveats

The split client-server processing model of FileMaker requires that records be committed before they are added to the server’s indexes. As such, you can not easily find/query records that have just been created and still pending commit. If not careful, deselecting a newly created record might make it inaccessible until after you commit. This isn’t really a flaw, just a design consideration.

Transactional deletion of record(s), if your solution requires, is currently dependent on the mechanics of portal layout object. This is unfortunate, but it works. Many solutions simply prohibit record deletes for various database design reasons.

Scripted error handling is laborious with the current toolset, to say the least. The effort required to trap for errors can be compounded if you also utilize schema level field validation.

There is only one kind of record lock and there is no concept of a transaction snapshot. If you need to perform math on data that comes from multiple records, and that data is volatile, then you really need to take out a lock on each of those records before you start reading values. This approach is a little heavy handed for read-only transactions, but it does satisfy the atomicity property.

Global fields, as used in the video, make an excellent fit with certain transactions because they work like client side pointers/selectors and will never write back or lock resources on the server. However, there are many other ways to structure transactions. Some solutions might not use globals at all. A deeper dive into different transactional patterns might be offered in a future post.

About the Author

Chris Irvine is a Senior Technology Consultant at Threeprong.com LLC, and a certified FileMaker Developer. He develops on multiple platforms, specializing in custom workgroup solutions, B2B integration, and performance tuning. Contact Threeprong for your next custom solution or for help taking your existing solution to the next level.

Previous: Is FileMaker ACID Compliant? (Part 1)

Up Next: ACID – Is FileMaker Consistent? (Part 3)