A detailed explanation of MySQL transactions

1, the concept of business

2. Which storage engines (table types) in MySQL support transactions which do not support

3. Four properties of a transaction

4. Creation and existence cycle of MySQL transaction

5. mysql Behavior

6, the isolation of transactions and performance

7, MySQL pseudo-transaction

First, the concept of business

A transaction consists of one or more SQL statements of a single cell, in which each MySQL statement is interdependent. As an integral whole, the entire unit will be rolled back if an SQL statement in the cell fails or produces an error. All affected data is returned to the state before the start of the thing, and if all the SQL statements in the cell succeed, the thing is executed smoothly.

Second, the storage engine in MySQL and the support transaction, and the storage engine that does not support transaction

1. Storage Engine concept: Data in MySQL is stored in files (or memory) in a variety of different technologies.

Each of these technologies uses different storage mechanisms, indexing techniques, and ultimately provides a wide range of different capabilities and capabilities. You can improve the overall functionality of your application by selecting different technologies to get additional speed or functionality.

2. These different technologies and associated functions are called storage engines (also known as table types) in MySQL.

3. Check the MySQL supported storage engine through show engines.

4. The most storage engines used in MySQL are: innodb,bdb,myisam, memory, etc. where InnoDB and BDB support transactions and MyISAM and so on do not support transactions.

Iii. four properties of a transaction:

1. Atomicity: A transaction consists of one or a set of interrelated SQL statements that are considered an indivisible unit.

2, Consistency: For the database modification is consistent, that is, multiple users to check the data is the same. Consistency is primarily handled by the MySQL logging mechanism, which records changes in data and provides tracking records for transaction recovery.

3. Isolation (Isolation): Each transaction has its own space, isolated from other transactions that occur in the system, and the result of the transaction is only visible when he is fully executed.

4, Persistence: But after committing this transaction, the modification and update of the data is permanent. When a transaction is completed and the log of the database has been updated, persistence can perform its unique function, in MySQL, if the system crashes or the data storage media is destroyed, through the log, the system can recover the last successful update before the reboot, which can reflect the change of things that are in the process of execution when the system crashes.

Iv. creation of transactions and life cycle

For a transaction-enabled storage engine, a transaction cycle:

1, in the process of creating a transaction, the user needs to create a InnoDB or BDB type of data table, the basic command structure is as follows:

CREATE TABLE table_name (file defintions) type=innodb/bdb;

2. Modify the table type

Alert table table-name type =INNODB/BDB;

3. The whole process of affairs

use databases; Use a database

Start transaction; Start a transaction, you can also use Begin, Beginwork

INSERT into STU1 values (",); To do the related operations

Commit//Commit things

Rollback//Undo Transaction (Transaction rollback)

V: MySQL Behavior

1, we in the use of MySQL is if closed, open in the query will find that the data has been saved, but we know that in the process is not displayed or implicit submission, ask what is this? Because we set up automatic submissions in MySQL, we can also submit them manually instead.

2, set autocommit = 0; Turn off auto-commit

3, set autocommit = 1; Turn on auto-commit

Vi. Isolation of transactions (isolation)

1. It is important to use the isolation level when multiuser, so that these transactions do not affect each other and ensure that database performance is not affected.

2. There are four types of orphaned levels available in MySQL:

1) SERIALIZABLE (serialization)

Transactions are processed as a sequence, which is only visible to the user after the transaction commits, but this level of isolation affects the performance of MySQL because it takes a lot of resources to ensure that a large number of transactions are not visible to the user at any time.

(2) Repeatable READ (can be reread)

There is a partial compromise on the security of the application compared to the level of serialization.

3) Read COMMITTED (post-submission)

Post-commit reads are less secure than reread. At this level of transactions, users can see new records added by other transactions, and in the case of transactions, the result set used by the same transaction to use a select query at different times may be different if there are other users modifying the corresponding table of the transaction at the same time.

(4) Read UNCOMMITTED (UNCOMMITTED)

Security is less readable than post-commit, and it is also the smallest interval between transactions (the degree of isolation), which is prone to unreal read operations. Other users can see uncommitted transactions at that orphaned level.

3, transaction isolation level of view and modification:

View: SELECT @ @tx_isolation;

Modified: Set global transaction isolation level for orphaned levels;

Vii. pseudo Transaction (locked)

1, in MySQL according to different needs, provide a lot of storage engine, but some storage engine does not support transactions, in this case, you can use table locking instead of transactions.

2, for non-transactional storage engine MyISAM type data table, when users insert, modify, delete, these operations are immediately saved to disk, when a multi-user operation of a table, you can use table locking to avoid multiple users at the same time in the database specified table operation, This avoids interference during user operation of the data table. Only those modified data tables can be accessed by other users after the user frees the table for the operation to be locked.

The lock here and the Java Multi-threaded lock in the role of the same, personal feel can be understood.

3. The process of locking operations on a specified table:

(1) Lock table table-name lock type; LockType has read and write two kinds

To add locks to multiple tables:

Lock table table-name1 lock type, table-name2 lock type, Table-name3 lock type;

(2) perform the corresponding operation in the specified table

(3) When the user completes the operation on the locked data table, it is unlocked.

unlock tables;//Release all lock table locks.

, dirty reading, non-repeatable reading, Phantom reading concept

• Dirty Read : When a transaction is accessing the data and the data has been modified, and the modification has not yet been committed to the database, another transaction accesses the data and then uses that data.

• non-repeatable read : is to read the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction, the data that the first transaction two reads may be different because of the modification of the second transaction. This occurs when the data that is read two times within a transaction is not the same and is therefore called non-repeatable read.

• Phantom reads : A phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table that involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then, the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.

(4), dirty read, non-repeatable reading, magic reading difference

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/78/65/wKiom1Z7fd2henmAAABFrKcOH6I669.png "title=" Screenshot.png "alt=" Wkiom1z7fd2henmaaabfrkcoh6i669.png "width=" 650 "style=" padding:0px;margin:0px;vertical-align : Top;border:none; "/>

Dirty reads are one transaction read to another transaction uncommitted update data, non-repeatable read is one transaction read to another transaction committed update data, Phantom read is a transaction read to another transaction has committed the newly inserted data

This article is from the "Dream to Reality" blog, please be sure to keep this source http://lookingdream.blog.51cto.com/5177800/1905789

A detailed explanation of MySQL transactions