MySQL's business

Let's start by saying what is business:

A transaction is a logical set of actions that make up the units of this set of operations, either all succeed or fail. A transaction is a contiguous set of database operations, as if it were a single unit of work. In other words, it will never be a complete transaction unless each individual operation within that group is successful. If any operation on the transaction fails, the entire transaction fails.

MySQL's transactional support is not tied to the MySQL server itself, but is related to the storage engine. For example, MyISAM: Transactions are not supported for read-only programs to improve performance; InnoDB: Supports acid transactions, row-level, concurrency, Berkeley DB: Supports transactions.

Characteristics of the transaction (ACID):

1. atomicity (atomicity): Ensure that all operations within the unit of work are completed successfully, otherwise the transaction will be terminated at the point of failure, and the previous operation will be rolled back to the previous state. Simply put, a set of transactions, either successful or withdrawn;

2. Consistency (consistency): Ensure that the database is successfully committed after the state is changed correctly.

3. Isolation (Isolation): Make transactions independent and transparent to each other, that is, the transaction runs independently, the result of one transaction affects other transactions, then other transactions are recalled. The 100% isolation of a transaction requires a sacrifice of speed.

4. Persistence (Durability): Ensure that a system that commits the result or effect of the transaction is present without a failure.

There are two main ways in which MySQL transactions are handled:

1. Using Begin,rollback,commit to achieve

Begin: Start a transaction

Rollback: Transaction rollback

Commit: Transaction Acknowledgement

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

MySQL default is automatically submitted, that is, you submit a query, it is directly executed, we can be set by the following statement:

Set autocommit = 0 Disables auto-commit

Set autocommit = 1 turn on auto commit

We should note that when we use set autocommit = 0, all of your future SQL will be transacted until we end with commit confirmation or rollback. When we end this transaction, we also open a new transaction. Press the first method to only make the current as a transaction. Only Innode and BDB types of data tables in MySQL can support transactions, and other types are not supported.

In MySQL, the transaction begins working and ending with a commit or ROLLBACK statement. A large number of transactions are formed between the start and end statements of the SQL command.

Commit&&rollback:

These two keywords are used for commit and rollback, primarily for MySQL transactions. When a successful transaction is completed, issuing a commit command should make changes to all participating tables effective. If a failure occurs, a rollback command should be issued to return each table referenced in the returned transaction back to its previous state.

The process of using MySQL:

(1) Before executing the SQL statement we want to open the transaction start transaction

(2) Normal execution of our SQL statements

(3) When the SQL statement is executed, there are two scenarios:

A. All successful, we want to commit the SQL statement to the database, commit

B. Some SQL statements fail, we execute rollback (rollback), we will quickly revoke the operation of the database

Let's look at an example: (About bank access to money)

CREATE table Bank (name varchar), Money Decimal (5,1)) engine=innodb Default charset = UTF8

INSERT into bank values (' Shaotuo ', +), (' Laohu ', 5000)

SELECT * FROM Bank

650) this.width=650; "Title=" 7E (0_[dx1c1a$7je{tq}v0j.png "src=" http://s4.51cto.com/wyfs02/M02/82/A3/ Wkiom1ddxwajm5yuaaamiqfb4o8056.png "alt=" Wkiom1ddxwajm5yuaaamiqfb4o8056.png "/>

Execute rollback ROLLBACK without success

Start transaction;//Open Transaction

Update bank set money =money+500 WHERE name = ' Shaotuo '//Modify data

Update bank set Moey=money-500 where name = ' Laohu '

ERROR 1054 (42522): Unknown column ' Moey ' in ' filed list '

Since there is an error above, we are going to perform a rollback rollback operation:

Rollback

SELECT * from bank;

650) this.width=650; "Title=" 7E (0_[dx1c1a$7je{tq}v0j.png "src=" http://s1.51cto.com/wyfs02/M00/82/A1/ Wkiol1ddynicyx6oaaamiqfb4o8936.png "alt=" Wkiol1ddynicyx6oaaamiqfb4o8936.png "/>

We can see that the table hasn't changed.

After the successful commit operation:

Start transaction;

Update bank set money=money+500 where name = ' Shaotuo '

Update bank set money=money-500 where name = ' Laohu '

Two commits after successful execution

Commit

SELECT * from bank;

650) this.width=650; "Title=" VQZQJAT6 (1ru_pbp}b9[i9x.png "src=" http://s5.51cto.com/wyfs02/M00/82/A1/ Wkiol1ddy2-wd1lnaaanjtxsrxk473.png "alt=" Wkiol1ddy2-wd1lnaaanjtxsrxk473.png "/>

We can see that we have modified the data successfully.

MySQL's business