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

This article mainly shows you how to use MySQL transactions, the content is easy to understand, I hope you can learn, after learning, there will be a harvest, the following let the editor to take a look at it.

The concept of transaction

A MySQL transaction is one or more database operations that either succeed or fail to roll back.

Transactions are implemented through transaction logs, which include: redo log and undo log.

Active of the transaction when the database operation corresponding to the transaction is in the process of being executed, we say that the transaction is in the active state. Partially committed (partially committed) when the last operation in a transaction is completed, but because the operation is performed in memory, the impact is not flushed to disk, we say that the transaction is in a partially committed state. Failed) when a transaction is in an active or partially committed state, it may encounter some errors (database errors, operating system errors, or direct power outage, etc.) and can not continue execution, or artificially stop the execution of the current transaction, we say that the transaction is in a failed state. Aborted (aborted) if the transaction is half executed and becomes a failed state, undo the impact of the failed transaction on the current database, we call this undo process as rollback.

When the rollback operation completes, that is, the database is restored to the state it was before the transaction was executed, we say that the transaction is in an aborted state.

Committed when a transaction in a partially committed state synchronizes the modified data to disk, we can say that the transaction is in a committed state.

As you can see from the figure, the life cycle of a transaction ends only when the transaction is committed or aborted. For transactions that have been committed, the changes made by the transaction to the database will take effect permanently, and for transactions in the aborted state, all changes made by the transaction to the database will be rolled back to the state before the transaction was executed. The main purpose of transaction is to ensure the consistency of complex database operation data, especially when accessing data concurrently.

MySQL transactions are mainly used to deal with data with large amount of operations and high complexity. Transaction characteristics atomicity (Atomicity, also known as indivisibility) the data operation of a transaction either succeeds or fails to roll back to the state before execution, as if the transaction had never been executed. Isolation (also known as independence) multiple transactions are isolated from each other and do not affect each other. The ability of database to allow multiple concurrent transactions to read, write and modify its data at the same time, isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently.

Four isolation states:

1. Read unsubmitted (Read uncommitted)

two。 Read submit (Read committed)

3. Repeatable read (Repeatable read)

4. Serialization (Serializable)

Consistency (consistency) before and after a transaction operation, the data is kept in the same state and the integrity of the database is not compromised.

Atomicity and isolation have a vital impact on consistency. Persistence (Durability) when the transaction operation is completed, the data is flushed to disk for permanent storage, even if the system failure is not lost. Syntax data for transactions # create data tables: create table account (- > id int (10) auto_increment,-> name varchar (30),-> balance int (10),-> primary key (id)); # insert data: insert into account (name,balance) values (Lao Wang's wife, 10); mysql > select * from account +-+ | id | name | balance | +-+ | 1 | Lao Wang's wife | 100 | 2 | Lao Wang | 10 | +-+ Lao Wife Wang has 100 yuan in her Wechat account. Lao Wang is specially used to give pocket money every month. Lao Wang also has his own small treasury. Now he has saved up to 10 yuan of pocket money, . Begin transaction startup mode 1mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > transaction operation SQL.start transaction [modifier] modifier: 1. Read only / / read-only 2. Read write / / default for reading and writing 3. WITH CONSISTENT SNAPSHOT / / consistent read transaction startup mode 2mysql > start transaction read only Query OK, 0 rows affected (0.00 sec) mysql > transaction operation SQL.# if read only is set, an error will be reported if you modify the data: mysql > start transaction read only;Query OK, 0 rows affected (0.00 sec) mysql > update account set balance=banlance+30 where id = 2 error 1792 (25006): Cannot execute statement in a READ ONLY transaction.commit transaction execution commit, if the commit is successful, refresh to disk mysql > commit Query OK, 0 rows affected (0 sec) the rollback transaction performs a rollback, returning to the state before the transaction operation. Mysql > rollback;Query OK, 0 rows affected (0.00 sec) it should be emphasized here that the ROLLBACK statement is only used by our programmers to roll back the transaction manually. If the transaction encounters some errors during execution and cannot continue to execute, the transaction itself will be rolled back automatically. In January, Lao Wang's performance was very good. Lao Wang's wife rewarded him with 20 yuan pocket money.

Perform the steps:

1. Read the data from Lao Wang's wife's account.

two。 Deduct 20 yuan from Lao Wang's wife's account

3. Read data from Lao Wang's account

4. Add 20 yuan to Lao Wang's account

5. The execution was submitted successfully

6. At this time, Lao Wang's wife's account was only 80 yuan, while Lao Wang's account was 30 yuan. Lao Wang was overjoyed.

Mysql > begin;Query OK, 0 rows affected (0.01 sec) mysql > update account set balance=balance-20 where id = 1 Changed query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > update account set balance=balance+20 where id = 2 scape query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > commit;Query OK, 0 rows affected (0.01 sec) # account balance: mysql > select * from account +-+ | id | name | balance | +-+ | 1 | Lao Wang's wife | 80 | 2 | Lao Wang | 30 | +-+ complete rollback example in February Lao Wang was supposed to perform very well, insisting on doing housework and walking the dog. Lao Wang's wife wanted to give him 25 yuan of pocket money, but Lao Wang's wife was transferring pocket money to Lao Wang. Suddenly I saw that Lao Wang received a Wechat from a little girl on his mobile phone on the table: dear Brother Wang, Lao Wang's wife was so angry that she withdrew the transfer and cancelled this month's pocket money.

Perform the steps:

1. Read the data from Lao Wang's wife's account.

two。 Deduct 25 yuan from Lao Wang's wife's account.

3. Read data from Lao Wang's account

4. Add 25 yuan to Lao Wang's account

5. At this time, Lao Wang's wife withdrew her previous operation.

6. At this time, the account balance of Lao Wang and his wife is still the same as before the operation.

Mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > update account set balance=balance-25 where id = 1world query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > update account set balance=balance+25 where id = 2th query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > rollback;Query OK, 0 rows affected (0.00 sec) # account balance: mysql > select * from account +-+ | id | name | balance | +-+ | 1 | Lao Wang's wife | 80 | 2 | Lao Wang | 30 | +-+ transaction supported storage engine 1. InnoDB2. For storage engines that NDB does not support, such as operating transactions on MyISAM, transactions will not take effect, and SQL statements will automatically commit, so rollback is invalid for storage engines that do not support transactions. Create table tb1 (- > id int (10) auto_increment,-> name varchar (30),-> primary key (id)->) engine=myisam charset=utf8mb4; mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > insert into tb1 (name) values ('Tom'); Query OK, 1 row affected (0.01 sec) mysql > select * from tb1 +-+-+ | id | name | +-+-+ | 1 | Tom | +-+-+ 1 row in set (0.00 sec) mysql > rollback;// rollback invalid Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > select * from tb1 +-+-+ | id | name | +-+-+ | 1 | Tom | +-+-+ 1 row in set (0.00 sec) transaction setting and viewing # View transaction opening status: mysql > SHOW VARIABLES LIKE 'autocommit' +-+-+ | Variable_name | Value | +-+-+ | autocommit | ON | +-+-+ the transaction commits automatically by default, which is committed automatically every time a SQL is executed.

When you need to operate a transaction, you need to explicitly open (begin or start transaction) and commit (commit) or roll back (rollback).

If set to OFF, the transaction will not actually be executed until a commit (commit) or rollback (rollback) operation is required.

The first way to explicitly start a transaction using START TRANSACTION or BEGIN statements is to turn off autocommit. The second sets the value of the system variable autocommit to OFF. SET autocommit = OFF Implicit commit cases when we open a transaction using START TRANSACTION or BEGIN statements, or set the value of the system variable autocommit to OFF, the transaction will not commit automatically, but if we enter certain statements, we will commit them quietly, just as we entered the COMMIT statement This kind of transaction commit caused by some special statements is called implicit commit definition or modification of database objects in the data definition language (Data definition language, abbreviated as: DDL), which refers to databases, tables, views, stored procedures, and so on. When we use CREATE, ALTER, DROP and other statements to modify these so-called database objects, we implicitly commit the transaction to which the previous statement belongs. A statement in a BEGIN;SELECT... # transaction A statement in a transaction UPDATE. # other statements in a transaction CREATE TABLE. # this statement implicitly commits the transaction to which the preceding statement belongs implicitly using or modifying tables in the mysql database implicitly using or modifying tables in the mysql database.

When we use statements such as ALTER USER, CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD, etc., we also implicitly commit the transaction to which the previous statement belongs.

Transaction control or statements about locking.

When we use START TRANSACTION or BEGIN statements to open another transaction before it is committed or rolled back, the last transaction is implicitly committed.

A statement in a BEGIN;SELECT. # A statement in a transaction UPDATE. # other statements in a transaction BEGIN; # this statement implicitly commits the transaction to which the previous statement belongs or the value of the current autocommit system variable is OFF, and when we manually set it to ON, it will also implicitly commit the transaction to which the previous statement belongs.

Or using LOCK TABLES, UNLOCK TABLES and other statements about locking will implicitly commit the transaction to which the previous statement belongs.

Statements that load data, such as when we use the LOAD DATA statement to bulk import data into the database, also implicitly commit the transaction to which the previous statement belongs. Some statements about MySQL replication also implicitly commit the transaction to which the previous statement belongs when using START SLAVE, STOP SLAVE, RESET SLAVE, CHANGE MASTER TO, and so on. Other statements using ANALYZE TABLE, CACHE INDEX, CHECK TABLE, FLUSH, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE, RESET and other statements will also implicitly commit the transaction to which the previous statement belongs. The concept of SavePoint for a transaction hits a few points in the database statement corresponding to the transaction, and we can specify which point to roll to when we call the rollback statement instead of going back to the original origin.

With the SavePoint of transactions, when we conduct complex transaction operations, we do not have to worry about a mistake and roll back to the original state, just like going back to the pre-liberation overnight.

Use syntax

1. SAVEPOINT save point name; / / Mark save point

2. ROLLBACK TO [SAVEPOINT] saves the point name; / / rollback to a SavePoint

3. RELEASE SAVEPOINT save point name; / / delete

Mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > update account set balance=balance-20 where id = 1world query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > savepoint action1;Query OK, 0 rows affected (0.02 sec) mysql > select * from account +-+ | id | name | balance | +-+ | 1 | Lao Wang's wife | 60 | 2 | Lao Wang | 30 | +-+ mysql > update account set balance=balance+30 where id = 2 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > rollback to action1;// rollback to action1 SavePoint Query OK, 0 rows affected (0.00 sec) mysql > select * from account +-+ | id | name | balance | +-+ | 1 | Lao Wang's wife | 60 | 2 | Lao Wang | 30 | +-+ to Is about how to use MySQL transactions If you have learned knowledge or skills, you can share it for more people to see.

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.