Difference between a MySQL transaction table and a non-transaction table

MySQL transaction table is an important concept of MySQL database. The following describes the differences between MySQL transaction table and non-transaction table in detail. We hope you can have a better understanding of MySQL transaction table.

MySQL transaction tables support batch processing as a complete task for unified submission or rollback, that is, multiple statements contained in the transaction are either fully executed or not executed.
Non-transaction tables do not support this operation. If the statements in the batch processing encounter errors, the statements before the errors are successfully executed, and those before the errors are not executed.

For example, in common operations, you can import 100 pieces of data from Table A to table B and then delete the part of data from Table A. However, if an unexpected transaction occurs in the middle of the process
The operation is normal, but non-transaction tables may encounter data exceptions.

MySQL transaction tables have table locks and row locks.
Non-transaction tables only have table locks.

You can use one of the following statements to check the label type:

Show table status like 'tbl _ name ';
Show create table tbl_name;

The transaction security table (TST) has the following advantages over the non-Transaction Security table (ntst:

· More secure. Even if MySQL crashes or encounters hardware problems, you can recover data automatically or from the backup plus transaction log.

· You can merge multiple statements and use the commit statement to accept all of them at the same time (if autocommit is disabled ).

· You can execute rollback to ignore your changes (if autocommit is disabled ).

· If the update fails, all your changes will change back to the original one. (For non-transactional security tables, all changes are permanent ).

· The transaction security storage engine can provide better deployment for tables with many updates currently read.

Non-transaction security tables have several advantages, because there is no transaction expense, all advantages can appear:

· Faster

· Requires less disk space

· Requires less memory to execute updates

Use the following statement to check the storage engine supported by the mysqld Server:

Show engines;

You can also use the following statement to check the variable values related to the storage engine you are interested in:

Show variables like 'have _ % ';

For example, to determine whether the InnoDB Storage engine is available, check the value of the have_innodb variable.