Not so long ago, the word "atom" referred to a thing that could not be split any further. Despite having discovered that atoms themselves are made up of even smaller particles, the term continues to retain its original meaning. With respect to relational databases, Atomicity means that operations (DMLs/DDLs, etc.) executed by the database will be atomic. The unit of atomicity usually provided by relational databases is a transaction. Why is this important? A guarantee of atomicity prevents updates to the database occurring only partially, which can cause greater problems than rejecting the whole series of operations outright. In today's blog, we'll learn what Atomicity is and how to enforce it within your database instances.
You Can't Spell ACID without Atomicity
You've probably heard the term "ACID" thrown about with respect to relational databases. It stands for "Atomicity Consistency Isolation Durability". It's a concept in database management systems (DBMS) that identifies a set of standard properties used to guarantee the reliability of a database. ACID properties ensure that all database transactions remain accurate and consistent, and support the recovery from failures that might occur during processing operations. As such, it is implemented by nearly all Relational Databases.
Here's where Atomicity comes in:
Say that you were performing a database UPDATE that will take 10 seconds to process all the rows in the table. As the updates proceed, the power suddenly goes out! Once power is restored, you go to read the data, and discover that some of the rows were updated according to your SQL statement, and the rest of the rows were not. You've now got yourself a bit of a mess!
Luckily, this can't happen with today's modern databases, right? Wrong.
Know Your Storage Engine
In many cases, the type of database you use is not as important as the storage engine that's being employed. The storage engine is the underlying software component that DBMS use to create, read, update and delete (CRUD) data. Most databases support several different types of storage engines. For example, MySQL currently offers the following out of the box:
- InnoDB
- MyISAM
- Memory
- CSV
- Archive
- Blackhole
- NDB
- Merge
- Federated
- Example
You are not restricted to using the same storage engine for an entire server or schema. You can specify the storage engine at the table level.
There exists a wide variety of storage engines because certain storage engines are effective in certain operations and environments yet very ineffective in others. This is important to take into consideration and pick which storage engines will work best for your usage patterns.
Back to our example, if you were using the MyISAM engine, you could be in trouble, because MyISAM does not enforce atomicity. Hence, a single change can be partially applied, whereby some rows in the intended set are affected, but the rest of the set are not. On the other hand, the InnoDB storage engine DOES ensure that any UPDATE will be applied to the complete set of rows you intended, or else it will apply to none of the rows if an error occurs or if the transaction is interrupted for some reason.
Selecting a Storage Engine in Navicat
Navicat makes selecting a storage engine for each table in your database easy via a drop-down on the Options tab within the Table Designer. Here's what you'll find for MySQL in Navicat Premium:
Conclusion
In today's blog, we learned what database Atomicity is and how to enforce it within your database instances.
Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial organizations. You can hire Rob by emailing him at rgconsulting(AT)robgravelle(DOT)com. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.
