MySQL Basics (vi) MySQL transaction

MySQL transaction: is a standalone work unit transaction must pass acid test:     atomicity (atomicity): All operations of a transaction are either all successfully executed, or all failed and rolled back       Consistency (consistency): A database is always converted from one consistency state to another State      isolation (Isolation): Before a transaction is committed, Other transactions are not visible, isolation has multiple levels      persistence (durability): Once a transaction commits, its actions are persisted in the execution process of the transaction in the database:      Start Transaction--Execute SQL statement--COMMIT TRANSACTION--save data      start Transaction---Execute SQL statement----back to data without any changes Isolation level for              transactions:     read  uncommitted (Read UNCOMMITTED content) Problems: 1, 2, 3# do not recommend this isolation level     read committed (read commit) Problems: 2, 3# Most databases use an isolation level that performs better     repeatable read (non-repeatable read)           problem: 3#mysql The isolation level used by default     serializable (serialized)           #数据安全性最好, with the weakest concurrent access capability, there may be a deadlock,         problem: 4 Unless the data requirements are more stringent, Otherwise, it is not recommended to use   problems:    &Nbsp;1, dirty read: Can read other transactions do not commit data     2, non-repeatable read: No changes in the current transaction, but use Select to view the data can be viewed in different numbers      3, Phantom read: Only the data when the transaction is started and the data that is modified in the current transaction are visible until the current transaction is committed,               the changes made by other firms will not be seen     4, locking read: If the table is modified in this transaction, the database will add a table lock to the table that made the modification,                 actions made by other transactions on this table will block until this transaction commits     mysql> SHOW VARIABLES LIKE  '%iso% '; #查看当前的隔离级别      set tx_isolation= ' parameters ' #修改隔离级别     corresponding parameters (case-insensitive): read-uncommitted#1read-committed# 2repeatable-read#3 (default) serializable#4          mysql>  SET GLOBAL autocommit=0; #关闭自动提交事务     mysql> start transaction #启动事务     mysql> commit# COMMIT TRANSACTION     mysql> rollback# rollback       &Nbsp;     mysql transaction log:     with transaction logs and post-crash security recovery can be good for saving data security           transaction log includes: Redo log redo and rollback log undo, transaction log usually smaller         redo records all the transactions that have been completed, that is, the transaction log group that executed the commit is IB_LOGFILE0, Ib_logfile1 logging group can more need to adjust the number of files      Undo records unfinished transactions that have been partially completed and written to the hard disk          after a mysq restart, InnoDB to synchronize all completed and write-to-disk and outstanding transactions through the transaction log      and write data to disk after a database crash, secure recovery          mysql> SHOW VARIABLES LIKE  ' innodb_log% '; #查看事务日志的相关配置          innodb_log_file_size# SET transaction log size innodb_log_files_in_group # Set number of transaction log group files innodb_log_group_home_dir   #设置事务日志存放位置

This article is from the "Automated Operations" blog, please be sure to keep this source http://hongchen99.blog.51cto.com/12534281/1932978

MySQL Basics (vi) MySQL transaction