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

Let's start with what a transaction is:

A transaction is a logical set of operations, and the units that make up the set of operations either succeed or fail. A transaction is a continuous 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 the group is successful. If any operation in the transaction fails, the entire transaction will fail.

Mysql's transaction support is not tied to the mysql server itself, but to the storage engine. For example, MyISAM: does not support transactions for read-only programs to improve performance; InnoDB: supports ACID transactions, row-level offices, and concurrency; Berkeley DB: supports transactions.

Characteristics of the transaction (ACID):

1. 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 previous operations will be rolled back to the previous state. To put it simply, a set of transactions either succeed or withdraw.

two。 Consistency: a transaction that ensures that the database commits successfully after the database changes state correctly.

3. Isolation: make the operations of transactions independent and transparent, that is, transactions run independently, the result of one transaction affects other transactions, then other transactions will be withdrawn. 100% isolation of transactions requires the sacrifice of speed.

4. Durability: make sure that the system that commits the result or effect of the transaction fails.

There are two main ways to handle transactions in mysql:

1. Using begin,rollback,commit to realize

Begin: start a transaction

Rollback: transaction rollback

Commit: transaction confirmation

two。 Directly use set to change the auto-commit mode of mysql

Mysql is automatically submitted by default, that is, if you submit a query, it will be executed directly. We can set it through the following statement:

Set autocommit = 0 forbids automatic submission

Set autocommit = 1 to enable auto-commit

We should note that when we use set autocommit = 0, all your future sql will be processed as transactions until we confirm with commit or rollback. When we finished this transaction, we also started a new one. In the first way, only the current one is treated as a transaction. In mysql, only data tables of type INNODE and BDB can support transactions, and other types are not supported.

In mysql, transactions start and end with COMMIT or ROLLBACK statements. A large number of transactions are formed between the sql commands of the start and end statements.

COMMIT&&ROLLBACK:

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

The process of using mysql:

(1) before executing the sql statement, we need to start the transaction start transaction

(2) execute our sql statement normally

(3) when the sql statement is executed, there are two situations:

a. All successful, we want to submit the impact of the sql statement on the database to the database, commit

b. Some sql statements fail, and we execute rollback (rollback) to undo the operation on the database

Next, let's take a look at an example: (about bank deposits and withdrawals)

Create table bank (name varchar (20), money decimal (5pm 1)) engine=innodb default charset = utf8

Insert into bank values ('shaotuo',1000), (' laohu',5000)

Select * from bank

Perform a rollback rollback if it is not successful

Start transaction;// starts a 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'

Because of the above error, we want to perform a rollback rollback operation:

Rollback

Select * from bank

We can see that the watch has not changed.

Perform the commit operation after success:

Start transaction

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

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

Commit after successful execution of both

Commit

Select * from bank

We can see that we have succeeded in modifying the data.

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.