MySQL transaction table and non-transaction table

MySQL transaction table and non-transaction table


MySQL transaction table and non-transaction table

When viewing the max_binlog_stmt_cache_size parameter explanation, there is such a sentence If nontransactional statements within a transaction require more than this has bytes of memory, the server generates an error.
So what is nontransactional statements?

In the http://dev.mysql.com/find the nontransactional keyword, the first one is Rollback Failure for Nontransactional Tables.

So what is Nontransactional Tables?

Nontransactional Tables is a non-transaction table that does not support transactions, that is, Tables that use the MyISAM storage engine.
Non-transaction tables do not support rollback.
> 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)


As you can see, non-transaction table rollback throws a warning that non-transaction tables do not support rollback.

Non-transaction table objects are transaction tables. For example, InnoDB tables support rollback.
> 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 indicates the statement used to operate non-transaction tables.

Max_binlog_stmt_cache_size this parameter affects non-transaction tables, such as MyISAM. If this parameter is not enough, more space is required.
Max_binlog_cache_size this parameter affects the transaction table, such as InnoDB. If this parameter is not enough, more space is required.

Reprinted Please note:
Cross screw
Http://blog.chinaunix.net/uid/23284114.html
QQ: 463725310
E-MAIL: houora # gmail.com (# Replace it @)