MySQL transactions and atomic operations

MySQL servers (3.23 to the highest version of the series, all 4.0 versions, and later) support transactions using the InnoDB and BDB transaction storage engines. InnoDB provides comprehensive acid compatibility.

Other non-transactional storage engines in the MySQL server, such as MyISAM, follow different examples of data integrity, called atomic operations. According to transactional terminology, MyISAM tables can always work efficiently in autocommit=1 mode. Atomic operations can often provide comparable integrity and better performance.

Because the MySQL server supports two paradigms, you can decide whether to use the speed of atomic operations to better serve your application, or to use transactional attributes. The selection is available as a table.

As stated, the trade-off between transactional and non-transactional table types depends primarily on performance. Transactional tables require more memory and disk space, and have a higher CPU overhead. On the other hand, multiple transactional table types, such as InnoDB, can also provide many notable features. The modular design of the MySQL server allows for the simultaneous use of different storage engines to meet different requirements and provide optimal performance in all cases.

But even with a non-transactional MyISAM table, how would you use the features of the MySQL server to maintain strict integrity? What about these attributes compared to the transactional table types?

1. A transactional type is more convenient if the application is written in a specific way and relies on the ability to invoke rollback rather than commit in critical cases. Using transactions also ensures that unfinished updates or crashed activities are not committed to the database, provides an opportunity for the server to roll back automatically, and saves your database.

If you use a non-transactional table, the MySQL server allows you to solve potential problems in almost all cases by simply checking before updating and running a simple script that checks for database consistency, which can be automatically repaired or given an alarm if inconsistencies occur. Note that using only the MySQL log or adding additional logs is usually a perfect way to correct the table without causing data integrity loss.

2. In many cases, it is possible to override critical transaction updates to make them "atomic" types. In general, all integrity issues resolved by a transaction can be resolved with lock tables or atomic updates, ensuring that the server is not automatically interrupted, which is a common problem with transactional database systems.

3. To secure the use of the MySQL server, you only need to enable backup and binary logging, regardless of whether you use a transactional table. In this way, you can resolve any problems that you encounter when using other transactional database systems. Regardless of the database system you are using, it's always a good idea to enable backup.

The business paradigm has its own advantages and disadvantages. Many users and application developers like this kind of simplicity, solving problems when they occur, or when necessary, through code. However, even if you are a novice to the atomic operation paradigm, or more familiar with the transaction, consider the speed benefits of a non-transactional table, which is faster 3~5 times than the fastest-tuned transactional table.

In cases where integrity is of paramount importance, MySQL can provide transactional-level reliability and security even for non-transactional tables. If you lock the table with lock tables, all updates are suspended until the integrity check is complete. If you obtain a read local lock on a table (as opposed to a write lock), the table allows parallel inserts to be performed at the end of the table, allowing read operations to be performed when other clients perform the insert operation. The newly inserted record is not seen by the client with the read lock property until the lock is released. With insert delayed, the insertion item can be placed in the local queue until the lock is lifted and the client is not waiting for the insert to complete.

"Atom" is by no means inconceivable in the sense that we assign it to its name. It only means that you can be sure that each feature update runs at the same time that other users cannot interfere with it, and that there is no automatic rollback (this may happen for transactional tables If you are not careful). The MySQL server also guarantees that no dirty reads exist.

Some techniques for using non-transactional tables are listed below:

· For loops that require a transaction, it is usually possible to encode using lock tables without the need for the cursor to update the record being processed.

· To avoid using rollback, you can take the following strategies:

1. Use lock tables to lock all tables that you want to access.

2. Before performing the update, the test must be a true condition.

3. If everything is OK, perform the update.

4. Unlock using unlock tables.

It is usually faster, though not always, compared to a transactional table with a rollback possibility. The only scenario that the solution cannot handle is to kill the thread in the middle of the update. In this case, all locks are freed, but some updates may not have been performed.

You can also use functions to update records in a single operation. Use the following technologies to obtain highly efficient applications.

Changes the column according to its current value.

Updates only the columns that actually change.