Transactions and ACID Properties in MySQL

MySQL and the ACID Constants

As shown below, MySQL meets all of the ACID criteria for a transaction-safe RDBMS.

• Atomicity is achieved by keeping the results of transactional statements (the updated rows) in a memory buffer and sending these results to the disk and the binary log from the buffer only after the transaction has been committed. This assures that the statements in a transaction function as an indivisible entity and that their consequences are perceived collectively, or not at all.
• MySQL’s logging capabilities, which record all changes to the database and provide an audit trail for transaction recovery, are largely responsible for consistency. In addition to the logging process, MySQL includes locking methods to guarantee that the starting process locks all of the tables, rows, and indexes that comprise the transaction long enough to commit or roll back the transaction.
• Message passing variables and locking mechanisms on the server serve as traffic managers, assisting applications in managing their own isolation methods. MySQL’s InnoDB engine, for example, employs fine-grained row-level locking for this purpose.
• MySQL achieves persistence by keeping a binary transaction log file that monitors system changes during a transaction. In the case of a hardware breakdown or an unexpected system shutdown, restoring lost data is a reasonably simple operation if the last backup is used in conjunction with the log when the system resumes. By default, InnoDB tables are 100 percent durable (all transactions committed to the system before the crash are subject to rollback during the recovery process), whereas MyISAM tables are only partially durable.

MySQL Storage Engines

MySQL storage engines are components that manage SQL operations for various table types. Except for particular use situations, Oracle recommends adopting InnoDB as the default and most general-purpose storage engine for tables. (By default, the CREATE TABLE command in MySQL 8.0 produces InnoDB tables.)

Storage engine list

MySQL supports the following storage engines:

• InnoDB: The most popular storage engine with transaction support is InnoDB. It is a storage engine that complies with ACID standards. Row-level locking, crash recovery, and multi-version concurrency control are all supported. It is the only engine that enforces the foreign key referential integrity requirement. Except for particular use scenarios, Oracle prefers InnoDB for tables.
• MyISAM: The original storage engine is MyISAM. It is a powerful storage engine. Transactions are not supported. Table-level locking is provided by MyISAM. It is widely utilized in web development and data warehouses.
• Memory storage engine: The memory storage engine produces tables in memory. It is the most powerful engine. It supports table-level locking. Transactions are not supported. The memory storage engine is great for making temporary tables or performing rapid lookups. When the database is restarted, the data is lost.
• CSV: CSV files are used to store data. It offers a high level of versatility since data in this format may be readily merged into other applications.
• Merge: Merge performs operations on the underlying MyISAM tables. Merge tables make it easier to manage enormous amounts of data. It logically aggregates a collection of identical MyISAM tables and refers to them as a single object. Excellent for data warehouse situations.
• Archive: The archive storage engine is designed for fast insertion. As data is added, it is compressed. Transactions are not supported. It is suitable for archiving and preserving massive volumes of seldom accessed historical data.
• Federated: A federated storage engine allows you to divide MySQL servers to build a single logical database from several physical servers. Queries on the local server are conducted automatically on the distant (federated) tables. The local tables contain no data. It works well in dispersed contexts.
• The Blackhole storage engine receives data but does not save it. The result of retrieval is always an empty set. The feature can be employed in a distributed database architecture where data is automatically duplicated but not locally stored. This storage engine may be used for performance testing as well as other types of testing.

Selecting the Best Engine

No storage engine is perfect in every situation. Some people do well in particular settings and badly in others. There are sacrifices that must be made. A more secure solution requires more resources; it may be slower, use more CPU time, and require more disk space. MySQL is particularly adaptable since it offers a variety of storage engines. Some, such as the Archive engine, are designed to be utilized in certain scenarios.

In certain circumstances, the solution is obvious. When interacting with certain payment methods, we are required to utilize the most secure approach. We can’t afford to lose such important information. The InnoDB database is the way to go. If we want a full-text search, we may use MyISAM or InnoDB. Only InnoDB supports the foreign key referential integrity requirement, and if we want to utilize it, the option is apparent.