MySQL transactional and non-transactional tables

When looking at the max_binlog_stmt_cache_size parameter explanation, there is such a sentence. If nontransactional statements within a transaction require more than this many bytes of memory, the server generates an error.

So, what are nontransactional statements?

Look for the nontransactional keyword at http://dev.mysql.com/, and the first one that comes out is Rollback Failure for Nontransactional Tables.

So what are Nontransactional Tables?

Nontransactional Tables, non-transactional tables, tables that do not support transactions, that is, tables that use the MyISAM storage engine.

The characteristic of non-transactional tables is that they do not support rollback. Looking at the following example

>create table no_trans(id int) ENGINE=MyiSAM;
>start transaction;
>insert into no_trans values(1);
>select * from no_trans;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
>rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
>show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)
 
>select * from no_trans;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

, you can see that the rollback of non-transactional tables throws a warning, indicating that non-transactional tables do not support rollback.

Compared with non-transaction table objects, transaction tables, such as tables using InnoDB, support rollback operations.

>create table trans(id int);
>start transaction;
>insert into trans values(1);
>select * from trans;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
 
>rollback;
Query OK, 0 rows affected (0.00 sec)
 
 
>select * from trans;
Empty set (0.00 sec)

It can be concluded that nontransactional statements mean statements that operate non-transactional tables.

max_binlog_stmt_cache_size This parameter affects non-transaction tables, such as MyISAM. If this parameter is not enough, it will prompt that more space is needed.

max_binlog_cache_size This parameter affects transaction tables, such as InnoDB. If this parameter is not enough, it will prompt that more space is needed.

The above is the content of MySQL transaction tables and non-transaction tables. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!