Questions about MySQL record non-transactional statements

MySQL version: Mariadb 5.5.30

OS version: CentOS 5.6 x86_64

Excerpt from the network: "Transactions are written in Binlog in the order in which they are submitted, MySQLServer for statements that differentiate threads, a transaction cache for each thread, each statement executed in the transaction is placed in the transaction cache, and the contents of the transaction cache are copied into the Binlog. and is emptied when the transaction is committed. ”

I agree with the above sentence, below we discuss how to record non-transactional statements under Binlog;

When there is no transaction activity, non-transactional statements are written directly into the binlog without the need for transaction caching;

If a transaction is active, it can be divided into the following cases:

1, the statement is marked as a transaction, it is written to the transaction cache (this sentence does not need to be validated)

2, the statement is not marked as transactional, and the transaction cache does not have a statement (that is, the statement as the beginning of the transaction), then the statement is written directly to the Binlog;

Verification process:

MyISAM table: Not_trans; Innodb table: trans;

commit; Verify that Binlog has insert into Not_trans record

This record is available for Binlog format:mixed and for statement; So the second case is correct.

3, the statement is not marked as transactional, but there are other statements of that transaction in the transaction cache, then the statement is written to the transaction cache

Validation process: A transactional table and a non-transactional table are placed in a transaction, inserted without a commit but rather rollback action (note: At this point, a non-transactional table cannot be rolled back according to the prompts warning), in the third case, Binlog there should be no Binlog records;

MyISAM table: Not_trans; Innodb table: trans;

To view binlog for verification:

For Binlog mixed and statement format storage, there will be records of non-transactional statements in Binlog, followed by the end of rollback;

I think the third kind of situation is wrong, (for this test have any questions you can discuss together)

Supplemental: The third case a non-transactional statement may have been written to the transaction cache. However, since the rollback failed in this experiment, the non-transactional statements are written directly into the Binlog, and perhaps the non-transactional statements are not written to the transaction cache at all, not excluding these two possibilities, do you have any method to verify?

This time we're going to mention one of MySQL's related parameters: Binlog_direct_non_transactional_updates

First of all, this is to let the non-transactional statement directly to the binlog inside, but this parameter is somewhat chicken. This parameter works when the Binlog format is statement and is not currently related to the version, but in the case of the MySQL 5.5.5 version, the parameter is not available for mixed if the binlog format is binlog or row mode What's the Impact! (Most people use the mixed mode.)

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1206939

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/