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

MySQL,ODBC database transaction

Simultaneous execution of multiple transactions: parallel in a way that does not affect each other; transactions interact with each other through datasets.

The START TRANSACTION; start transaction command database allows operations such as rollback undo only if the transaction is started.

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

ROLLBACK rolls back the transaction, and mysql can roll back as long as the transaction is opened without committing.

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

Therefore, it is 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. Each statement written will be submitted to persistent storage, which is a waste of resources.

The characteristics of the transaction:

Atomicity: atomicity, database operations caused by transactions are either completed or not executed

Consistency: consistency

Isolation: isolation

Transaction scheduling: minimum impact between transactions

MVCC: multi-version concurrency control

Durability: persistence. Once the transaction is completed 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

Status of the transaction:

Active: active

Partially submitted: after the last statement is executed

Failed:

Terminated:

Submitted:

And the transition process between states:

Advantages of concurrent transaction execution: 1, improve throughput and resource utilization 2, reduce waiting time

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

Example:

Mysql > SELECT @ @ AUTOCOMMIT; # query auto-submission status 1: on, 0: 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)

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; # which 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)

SavePoint: restore to the defined SavePoint SAVEPOINT. The SavePoint name cannot start with a 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

Usage:

Mysql > START TRANSACTION; # start transaction

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 > 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 share # that is the point where all the 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)

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.