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

This article mainly gives you a brief talk about the characteristics and application of Mysql database transactions. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that the characteristics and application of Mysql database transactions can bring you some practical help.

II. MySQL,ODBC database transactions

2.1. Simultaneous execution of multiple transactions:

Parallel in a way that does not affect each other; transactions interact with each other through datasets.

START TRANSACTION: start transaction commands the database allows rollback undo and other operations only if the transaction is started.

And the engine engine of the data must be innodb to support transactions

ROLLBACK: rollback transactions. Mysql can roll back transactions as long as they are not committed and the transaction is opened.

COMMIT: the transaction commits, and the rollback operation cannot be performed after the transaction is committed.

If the transaction is not explicitly started: autocommit can automatically commit, and each operation commits directly

It is strongly recommended that transactions be used explicitly, otherwise all operations are treated as one transaction and autocommit is turned off.

Otherwise, a lot of IO operations in mysql will be wasted, and each statement written will be submitted to persistent storage, which is a waste of resources.

2.2. The characteristics of the transaction:

2.2.1.Atomicity: atomicity

The database operations caused by the transaction are either completed or not executed

2.2.2.Consistency: consistency

2.2.3.Isolation: isolation

Transaction scheduling: minimum impact between transactions

MVCC: multi-version concurrency control

2.2.4.Durability: persistence

Once the transaction completes successfully, the system must ensure that any failure will not cause the transaction to show inconsistency

1. Data has been written out to persistent storage before the transaction is committed

2. Complete with transaction log

Transaction logs: sequential IO

Data files: random IO

2.3. Status of the transaction:

Active: active

Partially submitted: after the last statement is executed

Failed:

Terminated:

Submitted:

The process of transition between states

2.4. Advantages of concurrent transaction execution:

1. Improve throughput and resource utilization 2. Reduce waiting time.

2.5. Transaction scheduling: 1, recoverable scheduling 2, non-polar scheduling

3. Examples:

3.1. Set the auto submit switch

Mysql > SELECT @ @ AUTOCOMMIT; # autocommit status 1 is on, 0 is off

+-+

| | @ @ AUTOCOMMIT |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

Mysql > SET AUTOCOMMIT=0; # set autocommit off

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT @ @ AUTOCOMMIT; # query auto-submission status 1: on, 0: off

+-+

| | @ @ AUTOCOMMIT |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

3.2. Roll back the transaction

Mysql > SET AUTOCOMMIT=0; # set autocommit off

Query OK, 0 rows affected (0.00 sec)

Mysql > commit # commit all previous transactions

Mysql > DELETE FROM student WHERE Name LIKE 'Li%'; # Delete the line that contains Li in the Name field

Query OK, 1 row affected (0.03 sec)

Mysql > SELECT * FROM student; # Li which line has been deleted

+-+

| | SID | Name | Age | CID | |

+-+

| | 2 | Cheng Long | 0 | 2 |

| | 3 | Yang Guo | 0 | 3 |

| | 4 | Guo Jing | 0 | 4 |

+-+

3 rows in set (0.00 sec)

Mysql > ROLLBACK; # rollback the transaction. When auto commit is turned off, the transaction is enabled by default, and operations such as rollback can be implemented.

Query OK, 0 rows affected (0.01 sec)

Mysql > SELECT * FROM student; # deleted lines have been restored

+-+

| | SID | Name | Age | CID | |

+-+

| | 1 | Li Lianjie | 0 | 1 | |

| | 2 | Cheng Long | 0 | 2 |

| | 3 | Yang Guo | 0 | 3 |

| | 4 | Guo Jing | 0 | 4 |

+-+

4 rows in set (0.00 sec)

3.2. SavePoint: restore to the defined SavePoint SAVEPOINT. The SavePoint name cannot be a pure number.

SavePoint: SAVEPOINT savepoint_name; saves the above operation as the SavePoint name

Rollback SavePoint: ROLLBACK TO savepoint_name; rolls back to the state before the SavePoint

3.2. 1. Save point experiment 1

Mysql > START TRANSACTION; # start transaction

Query OK, 0 rows affected (0.00 sec)

Mysql > SAVEPOINT a; # all the student table data of this SavePoint exists

Query OK, 0 rows affected (0.00 sec)

Mysql > select * FROM student

+-+

| | SID | Name | Age | CID | |

+-+

| | 1 | Li Lianjie | 0 | 1 | |

| | 2 | Cheng Long | 0 | 2 |

| | 3 | Yang Guo | 26 | 3 |

| | 4 | Guo Jing | 53 | 4 | |

+-+

4 rows in set (0.00 sec)

Mysql > DELETE FROM student WHERE SID=4; # Delete the row of student table with SID 4

Query OK, 1 row affected (0.00 sec)

Mysql > SELECT * FROM student

+-+

| | SID | Name | Age | CID | |

+-+

| | 1 | Li Lianjie | 0 | 1 | |

| | 2 | Cheng Long | 0 | 2 |

| | 3 | Yang Guo | 26 | 3 |

+-+

3 rows in set (0.00 sec)

Mysql > SAVEPOINT b; # the row with a SID of 4 for the SavePoint student table data no longer exists

Query OK, 0 rows affected (0.00 sec)

Mysql > DELETE FROM student WHERE SID=3

Query OK, 1 row affected (0.00 sec)

Mysql > SELECT * FROM student

+-+

| | SID | Name | Age | CID | |

+-+

| | 1 | Li Lianjie | 0 | 1 | |

| | 2 | Cheng Long | 0 | 2 |

+-+

2 rows in set (0.00 sec)

Mysql > SAVEPOINT c; # rows with SID 3 and 4 of the student table at this SavePoint do not exist

Query OK, 0 rows affected (0.00 sec)

Mysql > ROLLBACK TO b; # rollback to SavePoint b, that is, the student table, the row with a SID of 4 does not exist

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT * FROM student

+-+

| | SID | Name | Age | CID | |

+-+

| | 1 | Li Lianjie | 0 | 1 | |

| | 2 | Cheng Long | 0 | 2 |

| | 3 | Yang Guo | 26 | 3 |

+-+

3 rows in set (0.00 sec)

Mysql > ROLLBACK TO a; # is the point where all data exists

Query OK, 0 rows affected (0.00 sec)

Mysql > SELECT * FROM student

+-+

| | SID | Name | Age | CID | |

+-+

| | 1 | Li Lianjie | 0 | 1 | |

| | 2 | Cheng Long | 0 | 2 |

| | 3 | Yang Guo | 26 | 3 |

| | 4 | Guo Jing | 53 | 4 | |

+-+

4 rows in set (0.00 sec)

3.2.2. Save point experiment 2

3.2.2.1. Set up four save points: a2, A4, A6 and A8

Mysql > select * from tutors

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 2 | HuangYaoshi | M | 63 | |

| | 3 | Miejueshitai | F | 72 | |

| | 4 | OuYangfeng | M | 76 | |

| | 5 | YiDeng | M | 90 | |

| | 6 | YuCanghai | M | 56 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 8 | HuYidao | M | 42 | |

| | 9 | NingZhongze | F | 49 | |

+-+

9 rows in set (0.00 sec)

Mysql > delete from tutors where TID = 2

Query OK, 1 row affected (0.00 sec)

Mysql > savepoint a2

Query OK, 0 rows affected (0.00 sec)

Mysql > delete from tutors where TID = 4

Query OK, 1 row affected (0.00 sec)

Mysql > savepoint A4

Query OK, 0 rows affected (0.00 sec)

Mysql > delete from tutors where TID = 6

Query OK, 1 row affected (0.00 sec)

Mysql > savepoint A6

Query OK, 0 rows affected (0.00 sec)

Mysql > delete from tutors where TID = 8

Query OK, 1 row affected (0.00 sec)

Mysql > savepoint A8

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tutors

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 3 | Miejueshitai | F | 72 | |

| | 5 | YiDeng | M | 90 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 9 | NingZhongze | F | 49 | |

+-+

5 rows in set (0.00 sec)

3.2.2. Roll back to A4 SavePoint OK

Mysql > rollback to A4

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tutors

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 3 | Miejueshitai | F | 72 | |

| | 5 | YiDeng | M | 90 | |

| | 6 | YuCanghai | M | 56 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 8 | HuYidao | M | 42 | |

| | 9 | NingZhongze | F | 49 | |

+-+

7 rows in set (0.00 sec)

3.2.3. Failed to roll back to A6 SavePoint, which no longer exists

Mysql > rollback to A6

ERROR 1305 (42000): SAVEPOINT A6 does not exist

3.2.4. But roll back to a2 SavePoint OK

Mysql > rollback to a2

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tutors

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 3 | Miejueshitai | F | 72 | |

| | 4 | OuYangfeng | M | 76 | |

| | 5 | YiDeng | M | 90 | |

| | 6 | YuCanghai | M | 56 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 8 | HuYidao | M | 42 | |

| | 9 | NingZhongze | F | 49 | |

+-+

8 rows in set (0.00 sec)

3.2.5. But then roll back to the original save point OK

Mysql > rollback

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tutors

+-+

| | TID | Tname | Gender | Age | |

+-+

| | 1 | HongQigong | M | 93 | |

| | 2 | HuangYaoshi | M | 63 | |

| | 3 | Miejueshitai | F | 72 | |

| | 4 | OuYangfeng | M | 76 | |

| | 5 | YiDeng | M | 90 | |

| | 6 | YuCanghai | M | 56 | |

| | 7 | Jinlunfawang | M | 67 | |

| | 8 | HuYidao | M | 42 | |

| | 9 | NingZhongze | F | 49 | |

+-+

9 rows in set (0.00 sec)

3.2.6. Rollback to the original save point, but failed to roll back A8 and A2R

Mysql > rollback to A8

ERROR 1305 (42000): SAVEPOINT A8 does not exist

Mysql > rollback to a2

ERROR 1305 (42000): SAVEPOINT a2 does not exist

The characteristics and application of Mysql database transactions will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.