MySQL transaction isolation level and logging Mode Selection

URC and MIXED configuration combination transaction security: combined with the MVCC function provided by InnoDB, you can only view the data after the committed transaction is modified, but cannot ensure the same transaction

U RC and MIXED configuration combination transaction security: combined with the MVCC function provided by InnoDB, you can only view the data after the committed transaction is modified, but cannot ensure the same transaction

Guidance:

MySQL has four transaction isolation levels: Read-uncommitted, Read-committed, Repeatable-read, and Seriailizable. I believe everyone knows their similarities and differences, if you are not clear, you can refer to another technical article: transaction and lock details of MySQL_InnoDB. However, the performance differences and application scenarios between the second and third isolation levels are prone to some misunderstandings, especially those who are familiar with Oracle, for this reason, I wrote a special technical article to guide you to reasonably choose the two transaction isolation levels.

Test environment and glossary:

Operating System: CentOS release 5.5 (Final)

MySQL version: 5.1.40-community-log

InnoDB version: build-in

Transaction isolation level: Read-committed (RC) and Repeatable-read (RR)

Log registration options (LBO): STATEMENT-based logging (LBS ),

ROW-based format (LBR)

Log replication mode (RBO): STATEMENT, ROW, and MIXED

Analysis and Summary of the combination of transaction isolation level and log mode:

N transaction isolation level: Read-committed (RC)

Transaction Security: the InnoDB Engine table is not supported for DML (DML refers to INSERT, UPDATE, DELETE), but all operations on data in non-transaction engine tables are allowed;

Transaction performance: the InnoDB table of the transaction engine cannot be operated;

U RC and STATEMENT configuration combinations

Log record format: All change operations register binary logs (LBS) based on commands );

Replication security: for SQL statements, if uncertain functions exist, data replication is consistent;

IO volume: no increase;