Storage engines (InnoDB and MyRocks)

MySQL storage engines are software modules that MySQL server uses to write, read, and update data in the database. There are two types of storage engines – transactional and non-transactional:

• Transactional storage engines permit write operations to be rolled back if it fails; thus, the original data remains unchanged. A transaction may encompass several write operations. Imagine the transfer of funds from one account to another in the company accounting system; debiting funds from one account and crediting them to another is a single transaction. If the failure happens near the end of the transaction, all preceding operations will be rolled back, and nothing in the transaction will be committed. If all write tasks were successful, the transaction would be committed, and all changes will be made permanent. Most storage engines are transactional, like InnoDB.
• Non-Transactional storage engines commit the data immediately on execution. If a write operation fails toward the end of a series of write operations, the preceding operations will need to be rolled back manually by code. To do so, the user will likely need to have recorded the old values elsewhere to know what they were. With the accounting example, imagine that the funds were debited from the first account but failed to be credited to the second, and the initial debit was not reversed. In this case, the funds would simply disappear. An example of this type of engine is MyISAM.

Another consideration when selecting a storage engine is if it is ACID-compliant.

ACID compliance

ACID compliance ensures data integrity in case of intermittent failures on different layers, such as broken connectivity, storage failure, and server process crash:

• Atomicity ensures all distinct operations within a transaction are treated as a single unit, meaning that if one fails, they all fail. This ensures no transaction is left partially done. If the transaction is successful, the changes are committed to the storage layer, and data is guaranteed to be correct.
• Consistency ensures a transaction cannot bring the database to an invalid state. Any data written must comply with all defined rules in the database, including constraints, cascades, triggers, and the referential integrity of the primary and foreign keys. This will prevent the corruption of data caused by an illegal transaction.
• Isolation ensures that no part of the transaction is visible to other users or processes until the entire transaction is completed.
• Durability ensures that once the transaction is committed, it will remain committed even in the event of a system failure, or power failure. The transaction is recorded in a logon store that is non-volatile.

The default storage engine of MySQL is InnoDB, and it is ACID-compliant. There are other types of storage engines as well that store and manipulate the data differently. If you are interested in learning more about what type of storage engines are available for MySQL, you can refer to the following link: https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html.

In the next section, you will take a look at how different applications can connect to your database through the application layer