The isolation property of the ACID model mainly involves topics related to concurrency, locking, and in some cases, caching. Related FileMaker features include:
- Record Level Locking (various related script steps: Commit, etc.)
- Automatic Client Record Cache Refresh
- Explicate Flushing of Query Cache (Flush cached join results)
In the simplest terms, people often picture operations happening serially, one after the other. For example, I’ll subtract 5 units of this widget from inventory. Then, you can subtract 10 units of the widget from inventory. When we’re done, we will have subtracted 15 total units from inventory. In multi-user (or any concurrent operation) system, it is conceivable that two users might want to subtract from the same inventory record at the same time. Even if not precisely at the same time, user A might not have finalized his edit(s) when user B begins hers.
A database system that successfully implements isolation will either successfully and accurately allow these concurrent operations. Or, in an atomic way, delay or fail and rollback at least one of the conflicting transactions.
FileMaker maintains isolation through server-managed record level locking. Only one user can win the lock on a specific record. When understood and implemented correctly, a second user attempting to lock the same record will receive and respond to an error. That user will then likely revert and cancel their transaction, or they might wait a little while and and try again.
Because FileMaker is not SQL-based, some of the typical isolation discussions do not apply directly. We can however, highlight characteristics that that are similar to standard isolation levels: serializable and then repeatable reads are considered the two highest levels of isolation. Unfortunately, we don’t have the type of transactions or snapshots that would allow this style of isolation in FileMaker. Read committed is the next isolation level and it is very close to what we have in FileMaker. Basically any time you read data, you are typically reading the latest committed version of the record. However, the client-server model of FileMaker includes record caches that are stored on the client. There may be a brief period of time when the server has received and committed a new version of the record and not all clients have yet become aware that their cache is outdated. Read uncommitted is the lowest isolation level and can result in dirty reads. FileMaker does not behave this way, uncommitted edits to records that could be incomplete or invalid, are not transmitted to the server and do not impact other clients.
Client Server Locking Example
Record Level Locking and Client Caches
In this illustration, we can see a simplified representation of how locks work in a typical multi-user FileMaker solution (single-file). In this case, the server has granted locks for two different clients. It has two record locks out to Joe, and one out to Fred. The server still has the last committed version of these records, and those locked records are available for any other clients to read. The first client, logged in as Joe, has two windows open against the remote file. The first window, doesn’t have any records open. The second window, has two records open, labeled here as #2 and #5. Notice that record #2 has been revised and contains tomato instead of orange. Because this edit hasn’t been committed, the server is not aware of the proposed value. Record #5, has been opened for editing, but still happens to contain banana. Even though record #4 is locked by another user on the server, Joe is still allowed to cache, use, and display the last committed value: kiwi.
The second client, logged in as Fred, has a single window open against the remote file. Fred has locked and edited record #4, giving it a new value: grape. Since this edited record has not yet been committed, the server does not have the new value yet. Notice that the first client, Joe, still has the value kiwi in his record cache, and could be displaying it in a window. If Joe tried to take a lock out on record #4, the request would be denied him, by the server. Fred would not need a notification that Joe attempted to lock record #4.
Finally, the third client logged in as Jane, has a single window open, and is currently just an observer without any records locked. Jane also maintains a record cache so that repeated display of previously accessed record(s) do not require a download from the server.
Let’s suppose that Fred “commits” the changes that he currently has pending in his one window. When this occurs, Fred will upload his revised record containing grape to the server. The server will work to permanently save this modified record. Additionally, the server will notify Joe and Jane, that their version of record #4, previously downloaded, is now dirty. This proactive notification behavior is uncommon for client-server database solutions, and is popular among FileMaker users. Joe and Jane, don’t necessarily need to download record #4 right away. If they are not currently displaying record #4, then they can safely wait to download the modified version until the record is needed again.
Scripting with Isolation In Mind
It is important to understand record locks and isolation so that your scripts do not introduce data errors. The following code is BAD
1. Set Variable [ $old_stock ; Inventory::Stock ] 2. Open Record/Request 3. ... check for and handle errors ... 6. Set Field [ Inventory::Stock ; $old_stock + 10 ] 7. Commit Record
This script can wreck your stock level. This is because time passes between step 1 and step 2, this stock record was not locked and another user may have updated it. Meanwhile, this code ignores new data in this record, and performs arithmetic based on stale information. Yes, I know it is just fractions of a second between these two steps, but there is an even bigger issue at play here. If your machine has previously read or displayed this inventory record, then this record has been sitting in your client cache for some time. Meanwhile, if some other user updates this inventory record, the server has many tasks to complete: It needs to update it’s own RAM cache, it needs to safely store the changes into it’s fmp12 file, and it also needs to notify the 10s or 100s of other client machines that also have that record in their cache. That typically all happens very fast, but it’s still a significant window of time when we are comparing to computer processing speeds.
I would go on to say that this code is also BAD
1. Set Field [ Inventory::Stock ; Inventory::Stock + 10 // charge in, opening the record as we update the stock field ] 2. ... check for and handle errors ... 5. Commit Record
It is logical and ideal that FileMaker first evaluates the expression (Inventory::Stock + 10) and then second sets the referenced field. Even within script step 1, it is possible that you are reading stale data from your cache before formally opening the record.
Recommended code that works
1. Set Field [ Inventory::ModifiedTS ; "" // open the record by clearing this auto-enter field ] 2. ... check for and handle errors ... 5. Set Field [ Inventory::Stock ; Inventory::Stock + 10 // math based on the record I successfully locked ] 6. Commit Record
Some people might prefer to use Open Record for step 1, which would also be acceptable. The advantage of locking records by first setting some unimportant field, is that it works through relationships and with implicit record creation, allowing you to handle multi-record transactions. Whereas, the Open Record step targets the current record/table occurrence.
Conclusion
Does FileMaker support isolation? YES. I think we can put this down as a solid characteristic that is a core component of the database engine.
Unfortunately, FileMaker does not currently offer a snapshot/read-only lock, so it is necessary to use full record locks if you need to have isolated access to record(s). If you are selecting across a large range of records, like say a Find on an entire table, then it may be impractical to expect isolation.
Caveats
In rare situations, certain versions of FileMaker have exhibited isolation bugs. These appeared in version 12 and possibly earlier versions. These bugs appear to be fixed in version 13 and 14.
Client-side record caches seem to be updated in a best effort fashion. If you passively read data from a record, the data you are reading at a later time might be stale, even by a few milliseconds. If you must be sure of the absolute latest data, then you should take out a lock on the record before you read values from it. The successful locking of a record insures that there are no other clients with unsaved/pending edits and that your client’s cached copy of the record is current.
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: ACID – Is FileMaker Consistent? (Part 3)
Related Articles
0 Comments
Leave a reply
You must be logged in to post a comment.