MySQL (20): Transaction management

1. Introduction of the concept of transactions:

In real life, we often do transfer operations, transfer operations can be divided into two parts to complete, transfer and transfer out. Only these two parts are complete before you can consider the transfer to be successful. In the database, this process is done using two statements, if any of the statements in which an exception is not executed, it will cause the amount of two accounts is not synchronized, resulting in an error.

In order to prevent the possible situation above, MySQL introduced a transaction, the so-called transaction is a set of operations against the database, it can be composed of one or more SQL statements, the operation of the same transaction has the characteristics of synchronization, if one of the statements can not be executed, then all the statements will not be executed, that is, Statements in a transaction are either executed or not executed.

When you use a database, you need to use transactions, you must first open the transaction, the statement that opens the transaction is as follows:

Start transaction;

That

The above statement is used to open the transaction, after the transaction has been opened to execute the SQL statement, after the SQL statement executes successfully, the corresponding statement needs to commit the transaction, the statement commits the transaction as follows:

Commit

It should be noted that the SQL statements written directly in MySQL are automatically committed, and the action statements in the transaction need to be committed manually using the COMMIT statement, which will only take effect after the transaction commits.

If you do not want to commit a transaction, we can also cancel the transaction (also called rollback) using the related statement, as follows:

Rollback

It is important to note that the rollback statement can only be performed on uncommitted transactions, and transactions that have already been committed cannot be rolled back.

2. I believe that through the above explanation, we have a simple understanding of the transaction, and then we show how to use the transaction through a case of transfer.

(1) Preparation before presentation:

The specific SQL statements are as follows:

Create Databasechapter06; Usechapter06;Create TableAccount (IDint Primary Keyauto_increment, namevarchar( +),     Money float);Insert  intoAccount (Name, Money)Values('a', +);Insert  intoAccount (Name, Money)Values('b', +);  

The following results are performed:

(2) Next we use transactions to demonstrate how to implement the transfer function:

First, start a transaction, and then pass the UPDATE statement to the account a of $100 to the B account , and then finally commit the transaction, the following specific statement:

StartTransaction;UpdateAccountSet  Money= Money- - whereName='a';UpdateAccountSet  Money= Money+ - whereName='b';Commit;

The following results are performed:

A transactional operation has a strict definition, and it must meet 4 features:

1) atomicity

2) Consistency

3) Isolation

4) Durability

MySQL (20): Transaction management