This series is comprised of six posts. If you’d rather jump right to the conclusion, you are welcome to do so, but you’ll miss out on the deeper explanations and test results. Enjoy!
Wether your are selecting a database platform for a new project, or looking to bolster the durability of an existing project, understanding the ACID model is an important foundation. As a developer on numerous platforms, including FileMaker, I was surprised that no one seems to have really tackled this topic yet. This is a pretty weighty matter, so I plan to break it up over a few posts. Watch this space, for about one post each week.
I’ll editorialize a bit during the intro and ending, but will strive to be concise and objective in the technical middle portions.
What is ACID?
ACID is an acronym that stands for atomicity, consistency, isolation, and durability. These properties are desirable in any database system, and discussion topics are often aligned with transactions and reliability. This isn’t a topic that beginner developers often consider, but it is certainly in the mind of software engineers who author the code that operates under the hood of your database. The acronym has been around since the 80’s and the concepts are credited to computer scientists Jim Gray and Andreas Reuter.
Who cares? Does it really matter if FileMaker is ACID compliant?
Think of it like examining the ingredients of your favorite frozen entrée. You can skip this step if you want, I’m sure they would only put good stuff in there… right… Ok, maybe we should check the label.
Spoiler alert… I probably won’t answer the question with a strict YES or NO answer. I wish it were that simple. If you search the web for articles about ACID compliance and some of most popular commercial and open source databases, you will find plenty arguments in support and against. Some hugely successful and popular databases are openly not compliant. Even if a product purports compliance, you may later discover that it so only under a narrow range of configuration. In reality, font-end software is often mistakenly crafted in a way that inhibits one or more ACID back-end properties. Or, there might even be situations where systems are intentionally ‘tuned’ for better performance as a trade-off for some ACID behavior.
So, assuming there are areas of any database that don’t exhibit perfection, we need to have accurate expectations and consider best practices for both solution design and hardware configuration. We need to understand and calculate acceptable risks. We also should keep pressure on our database vendors to deliver a quality product.
FileMaker is not “SQL Based.” Can it really be assessed for ACID characteristics?
Yes! FileMaker is a unique platform that provides a complete development stack: It incorporates: multiple run times, client-server processing model, graphical interface, relational database, enterprise authentication, IDE, and even static analysis. The integrated database component, just one of many pieces that makeup the product, is vital to the FileMaker platform. Like any other database, it would be desirable if FileMaker behaved in an ACID compliant way. We can certainly explore and test the capabilities of the database engine in these areas.
There are several deployment models for a FileMaker database including client-server deployments, local runtimes, iOS offline solutions, data-less reporting front ends, and more. While these would also be interesting scenarios, this article is most interested in the FileMaker Server hosted configuration with various connected on-line clients. This type of client-server deployment is typical for FileMaker in many institutions. Testing was performed on the FileMaker 14 family of products.
Do No Harm
This article isn’t authorized, sponsored, or otherwise affiliated with any vendor. FileMaker is one many excellent products and platforms that I use when developing solutions for my own clients. My aim is to educate and provide reference documentation to developers that will steer them towards best practices. Every attempt will be made to accurately test and convey results. Please provide feedback if errors are found.
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.
Up Next: ACID – Is FileMaker Atomic? (Part 2)