Shulou(Shulou.com)06/01 Report--

Mysql transaction: a separate unit of work transaction must pass the ACID test: atomicity (Atomicity): all operations of a transaction are either performed successfully or roll back consistency after all failures (Consistency): the database always transitions from one consistency state to another state isolation (Isolation): the operation made by one transaction is not visible before it is committed. Isolation has multiple levels of persistence (Durability): once the transaction is committed The operation it does will permanently save the execution process of the transaction in the database: start the transaction-- > execute the SQL statement-- > commit the transaction-- > save the data to start the transaction-- > execute the SQL statement-- > rollback-- > the isolation level of the transaction that the data does not change: Read Uncommitted (reading uncommitted content) has a problem: 1, 2. 3 # it is not recommended to use this isolation level Read Committed (read commit) there is a problem: 2, 3 # isolation level used by most databases Good performance Repeatable Read (non-repeatable read) problems: 3 # mysql default isolation level Serializable (serialization) # data security is the best, concurrent access capability is the weakest, deadlocks may occur, problems occur: 4 unless in the case of strict data requirements Otherwise, it is not recommended to use problems: 1. Dirty reading: you can read data that is not committed by other transactions. 2. Non-repeatable reading: no changes have been made in the current transaction, but different numbers may be seen when using select to view the data. 3. Illusory reading: before the current transaction commits, you can only see the data when you started the transaction and the data that was modified in the current transaction. Changes made by other transactions will not be visible. 4. Lock read: if the table is modified in this transaction, the database will add a table lock to the modified table, and operations made by other transactions on this table will block mysql > SHOW VARIABLES LIKE'% read before the transaction commits. # View the current isolation level SET tx_isolation=' parameter'# modify the isolation level corresponding parameter (case-insensitive): Read-Uncommitted # 1 Read-Committed # 2 Repeatable-Read # 3 (default) Serializable # 4 mysql > SET GLOBAL autocommit=0 # disable auto commit transaction mysql > START TRANSACTION # start transaction mysql > COMMIT # commit transaction mysql > ROLLBACK # rollback mysql transaction log: with transaction log and secure recovery after crash, data can be well saved. Security transaction log includes: redo log redo and rollback log undo Transaction logs are usually relatively small. Redo records all completed transactions, that is, the transaction log group that executed COMMIT is ib_logfile0, and ib_logfile1 log group can be more necessary to adjust the number of files. Undo records incomplete transactions that have been partially completed and written to the hard disk after mysq restart. Innodb synchronizes all completed and written to disk transactions and writes data to disk through the transaction log to safely recover mysql > SHOW VARIABLES LIKE 'innodb_log%' after a database crash # View the relevant configuration of transaction 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 # set transaction log location

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.