MySQL (13) MySQL Business

Objective

During this time, I will summarize all the things I have learned and hope to be helpful in my future work. In fact, the status of every day is very tired, but I have to insist!

Go to our point today:

Why does MySQL have to have business? What is a transaction for? We use an example to illustrate:

Transactions are widely used in order systems, banking systems and many other scenarios. If you have one of the following scenarios: a user and B user are depositors of the bank. Now a is going to transfer 500 dollars to B. Here are a few things to do:

1) Check the account balance of a >500 yuan;
2) A account deduction of 500 yuan;
3) account increase of 500 yuan;

Normal process to go down, a account deducted 500,b account plus 500, happy. What if the system fails after the money is deducted from a account? A lost 500 in vain, and B did not receive 500 of his own.

In the above case, there is a precondition: a deduct money and B plus money, either succeed at the same time, or fail at the same time. The need for business is this!

I. MySQL TRANSACTION overview 1.1, MySQL Business introduction

A MySQL transaction is a program execution unit that accesses and updates various data items in the database. Operations in a transaction either perform modifications or do not execute, which is the purpose of the transaction and is one of the important features of the transaction model that distinguishes the file system.

MySQL transaction is mainly used to deal with large-scale and high-complexity data . For example, in an employee management system, deleting an employee requires deleting the employee's profile and removing other information related to that employee. These database operation statements form a transaction.

In MySQL, transactions are supported only by databases or tables that use the InnoDB database engine .

Transactions can be used to maintain the integrity of the database, to ensure that batches of SQL statements are either fully executed or not executed at all .

Transactions are used to manage insert,update,delete statements

1.2. Four characteristics of the transaction (ACID properties)

Strictly speaking, a transaction must satisfy 4 features at the same time, that is, the acid characteristics of a transaction that is commonly described . Although strict transaction requirements are defined theoretically, the database vendor does not strictly meet the acid standards of the transaction for various purposes.

For example, for MySQL's NDB cluster engine, although it supports transactions, it does not meet the requirements of D, that is, the requirements of persistence. For Oracle databases, the default transaction isolation level is read COMMITTED, which does not meet the requirements of I, the requirement of isolation.

For the InnoDB storage engine, the default transaction isolation level is read repratable, which fully adheres to and satisfies the acid characteristics of the transaction.

1) atomicity (atomicity): means that all operations contained in a transaction are either all successful or fail back , so the operation of a transaction must be fully applied to the database if it succeeds, and it cannot have any effect on the database if the operation fails.

2) Consistency (consistency): means that a transaction must transform a database from one consistent state to another , meaning that a transaction must be in a consistent state before and after execution.

(For example: Take a transfer, assuming that both user A and User B are combined with a total of 5000, then no matter how a and B transfer, transfer several times, after the end of the transaction, the sum of two users should be 5000, this is the consistency of the transaction.) ）

3) Isolation (isolation): When multiple users concurrently access the database operation of the same table, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

4) Persistence (durability): When a transaction is committed, a change to the data in the database is permanent , even if the database system encounters a failure, the commit transaction is not lost.

Note: the (ACID) nature of the transaction is implemented by a relational database management system (RDBMS, database System) . The database management system uses logs to ensure the atomicity, consistency and persistence of transactions. The log records the updates that the transaction made to the database.

If an error occurs during execution of a transaction, the transaction's updates to the database can be revoked based on the log, returning the database to its initial state before executing the transaction. The database management system adopts the lock mechanism to realize the isolation of the transaction.

When multiple transactions update the same data in the database at the same time, only transactions that hold the lock are allowed to update the data, and other transactions must wait until the previous transaction releases the lock, and other transactions have the opportunity to update the data.

1.3. Classification of transactions

Flat transactions: The simplest and most frequently used transactions. In a flat transaction, all operations are at one level, with the begin work beginning, with commit work or rollback working end. The operations that are in between are atomic, either all executed, or all rolled back.

Flat transaction with SavePoint: In addition to operations supported by a flat transaction, it is possible to roll back to an earlier state in the same transaction during the execution of a transaction, because there may be errors in the execution of some transactions that do not invalidate any operations.

It is too expensive to give up the entire transaction. The savepoint is used to inform the system that the current state of the transaction should be remembered so that when an error occurs later, the transaction can return to that state.

Chain transactions: Visible as a variant of the save point pattern.

Nested transactions: A hierarchical framework.

Distributed transactions

Second, transaction control statements

At the default settings of the MySQL command line, transactions are automatically committed, that is, a commit is executed immediately after the SQL statement is executed.

Therefore, to open a transaction to be displayed, you must use command begin and start TRANSACTION, or execute command set autocommit = 0 To disable automatic submission for the current session .

1) START TRANSACTION | BEGIN

Opens a transaction that is displayed. In the stored procedure, the parser of the MySQL database automatically recognizes begin as begin ... END, so you can only use the START TRANSACTION statement to open a transaction in a stored procedure.

2) COMMIT

To use the simplest form of this statement, simply issue a commit. Commit commits a transaction and makes all modifications made to the database permanent . Commit and commit work statements are basically consistent and are used to commit a transaction.

The difference is that the commit work is used to control whether the behavior after the transaction ends is chain or release. If it is the chain way, then the transaction becomes a chain transaction. The user can control the parameter Completion_type by default, which is 0, which means there is no action.

In this setup, commit and commit work are completely equivalent. When the parameter value is 1 o'clock, commit work is equivalent to commit and CHAIN, which means that a transaction with the same isolation level is automatically opened immediately.

When the parameter value is 1 o'clock, the commit work is equivalent to commit and RELEASE. The connection to the server is automatically disconnected when the transaction is committed.

3) ROLLBACK

Rolling back ends the user's transaction and revokes any uncommitted modifications that are in progress.

4) SavePoint Identifiter

SavePoint allows a user to create a savepoint in a transaction, and a transaction can have many savepoint points .

5) RELEASE savepoint identifier

Deletes a savepoint for a transaction, and throws an exception when no savepoint executes the statement.

6) ROLLBACK to [savepoint] Identifier

This statement is used with the savepoint command . You can roll back a transaction to a marker point without rolling back any work before this marker point .

Note: Although there is a rollback, it does not actually end a transaction, so even if rollback to savepoint is executed, then the run commit or rollback command needs to be displayed.

7) SET TRANSACTION

This statement is used to set the isolation level of the transaction. The transaction isolation levels provided by the INNODB storage engine are READ UNCOMMITTED, read COMMITTED, Repeatable read, and Serializable.

Three, MySQL transaction processing two ways

1) Use Begin,rollback,commit to achieve

Begin a transaction

ROLLBACK transaction Rollback

Commit TRANSACTION Acknowledgement

2) Use set directly to change the MySQL auto-commit mode

SET autocommit=0 prohibit auto-commit

SET autocommit=1 turn on auto-commit

Iv. the isolation level of the transaction in MySQL 4.1, in MySQL if the isolation of the transaction is not considered, a few problems will occur

1) Dirty Reading

Refers to the data in another uncommitted transaction that is read in a single transaction process.

When a transaction is modifying a data multiple times, and the changes in the transaction are not committed, then a concurrent transaction accesses the data, resulting in inconsistent data from two transactions.

Example:

User A transfers 100 yuan to User B, corresponding to the SQL command as follows:

Update account set money=money+100 where name= ' B ';(at this time a notification B)

Update account set money=money-100 where name= ' A ';

When only the first SQL is executed, a informs B to view the account, B discovers that the money has been paid (that is, a dirty read occurs), and then regardless of whether the second SQL is executed, as long as the transaction is not committed, all operations will be rolled back, and when B sees the account again later, the money does not actually go.

2) Non-repeatable reading

In the case of a data in a database, multiple queries within a transaction scope return different data values because the query was modified and committed by another transaction.

Example:

Transaction T1 reads a certain data, and the transaction T2 immediately modifies the data and commits the transaction to the database, and the transaction T1 reads the data again to obtain different results, sending a non-repeatable read.

The difference between non-repeatable reads and dirty reads is that dirty reads are dirty data that a transaction reads from another transaction, and non-repeatable reads the data submitted by the previous transaction.

In some cases, non-repeatable reading is not a problem, for example, we query a data multiple times and of course the results are the result of the final query. In other cases, however, problems can occur, such as querying the same data A and B in turn may be different, and A and B may be hit.

3) Virtual reading (phantom reading)

Phantom reading is a phenomenon that occurs when a transaction is not executed independently.

Example:

The transaction T1 a data item from "1" to "2" for all rows in a table, and the transaction T2 inserts a row of data items into the table, and the value of this data item is "1" and is submitted to the database.

The user of the operation transaction T1, if you look at the data you just modified, will find that there is another line that has not been modified, in fact, this line is added from the transaction T2, as if the illusion, this is the occurrence of Phantom read.

Both Phantom and non-repeatable reads read another transaction that has already been committed (this is different from the dirty Read), and the difference is that the non-repeatable read query is the same data item, and the Phantom Read is for a whole group of data (such as the number of data).

4.2. Four types of isolation boundaries for MySQL database

The InnoDB storage engine provides transaction isolation levels of READ UNCOMMITTED, read COMMITTED, Repeatable read, and Serializable.

1) Serializable (serialization): Can avoid dirty reading, non-repeatable reading, the occurrence of phantom reading.

It is executed serially by forcing the transaction to avoid the above-mentioned phantom-reading problem.
2) Repeatable READ (REPEATABLE Read): Can avoid dirty read, non-repeatable read occurrence.

The dirty reads are resolved, and the results of reading the same records multiple times in the same transaction are consistent. In theory, however, it is not possible to reread the isolation level to solve another phantom-reading problem,

When a transaction reads a range of records, another transaction inserts a new record within that range, and a phantom row is generated when the previous transaction reads the record in that range again.
3) Read Committed (Read Committed): Can avoid the occurrence of dirty reading.

Any modifications made to a transaction from the beginning until the commit are not visible to other transactions.

This level is sometimes called non-repeatable, because two executions of the same query may result in different results. Because there may be other transactions between these 2 reads that change this data, each read is already committed.
4) Read UNCOMMITTED (READ UNCOMMITTED): lowest level, no case guaranteed

Modifications in the transaction, even if they are not committed, are visible in other transactions. Transactions can read uncommitted data, which is also known as dirty reads.

Analysis:

1) The above four isolation levels are the highest of the serializable level, the lowest is the read uncommitted level, the higher the level, the lower the efficiency of execution. A level like Serializable,

is to lock the table in the way (similar to the Java Multi-threading lock) so that other threads can only wait outside the lock, so the usual choice of what isolation level should be based on the actual situation. The default isolation level in the MySQL database is repeatable read (repeatable read).

2) in MySQL database, the above four isolation levels are supported, the default is repeatable read (repeatable read).

In the Oracle database, only the serializable (serialization) level and Read Committed (Read Committed) levels are supported, with the default Read committed level.

3) Repeatable Read is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same data row when concurrently reading the data.

In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reading refers to when a user reads a range of data rows and another transaction inserts a new row within that range.

When the user reads the data row for the range again, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control) mechanism.

4) View the isolation level of the current transaction in the MySQL database: select @ @tx_isolation;

5) Set the isolation level of the transaction in the MySQL database:

Set [Glogal|session] Transaction ISOLATION level isolation class name;
Set tx_isolation= ' Isolation level name ';

If you want to set the default isolation level of the transaction at startup, modify the MySQL configuration file and add the following line in [Mysqld]:

　　　　　　[Mysqld]

transaction-isolation = read-committed

Note: Setting the isolation level of the database must be before opening the transaction!

MySQL (13) MySQL Business