Mysql transaction Summary

The features of database transactions ACID transactions are the basic unit of concurrency control. A transaction is a sequence of operations. These operations are either executed or not executed. It is an inseparable unit of work. For example, for bank transfers: either of these two operations is executed or not executed when fees are deducted from one account and the other account is added.

The features of database transactions ACID transactions are the basic unit of concurrency control. A transaction is a sequence of operations. These operations are either executed or not executed. It is an inseparable unit of work. For example, for bank transfers: either of these two operations is executed or not executed when fees are deducted from one account and the other account is added.

　 Database Transaction Features ACID

Transaction is the basic unit of concurrency control.

A transaction is a sequence of operations. These operations are either executed or not executed. It is an inseparable unit of work. For example, for bank transfer: If you deduct money from one account and add money to another account, either or both of these operations are executed.

Database transactions must have ACID properties. ACID is the abbreviation of Atomic (Atomicity), Consistency (Consistency), Isolation (Isolation), and Durability (Durability.

Atomicity: indicates that the entire database transaction is an inseparable unit of work. The transaction is successful only when all operations in the database are successfully executed. If any SQL statement in the transaction fails to be executed, the successfully executed SQL statement must also be revoked, the database status should be returned to the status before the transaction is executed.

Consistency: database transactions cannot undermine the integrity of relational data and business logic consistency. For example, for a bank transfer transaction, whether the transaction succeeds or fails, you should ensure that the total deposits of Tom and Jack in the ACCOUNTS table after the transaction ends is 2000 yuan.

Isolation: In a concurrent environment, when different transactions manipulate the same data at the same time, each transaction has its own complete data space. Modifications made by a concurrent firm must be isolated from those made by any other concurrent firm. When a transaction views data updates, the status of the data is either the status before another transaction modifies it, or the status after another transaction modifies it, transactions do not view data in the intermediate state.

Persistence: the update made to the database must be permanently saved as long as the transaction ends successfully. Even if the system crashes, after the database system is restarted, the database can be restored to the State at the end of the transaction.

The ACID feature of transactions is implemented by relational database management systems (RDBMS. The database management system uses logs to ensure the atomicity, consistency, and durability of transactions. The log records the updates made by the transaction to the database. If an error occurs during the execution of a transaction, you can cancel the updates made to the database by the transaction according to the log, returns the database to the initial state before the transaction is executed.

The database management system uses a locking mechanism to isolate transactions. When multiple transactions update the same data in the database at the same time, only the transaction holding the lock can update the data. Other transactions must wait until the lock is released for the previous transaction, other transactions have the opportunity to update the data.

MySql transaction Application

Transaction Features:

Atomicity (Atomicity)
Consistency (stability, Consistency)
Isolation (Isolation)
Durability)
Note: transactions are only valid for statements that affect data.

Show engines // view the Data Engine supported by mysql locks.

MyISAM does not support transactions. InnoDB supports transactions.

By default, MySQL runs in the automatic submission mode, which means that no small command is executed as a single command.

To enable mysql to support transactions, you only need to modify the Data Engine (alter table person type = INNODB ).

Use the start transaction or begin command to start a transaction, and use commit or rollback to end the transaction.

The end of a transaction: Except for commit, rollback ends and DDL or DCL statements ends.

Save point: you can use the savepoint name command in the transaction to set some save points. In the future, when you use rollback to savepoint name to end a transaction, data before name is saved, subsequent data is not saved.

Mysql uses transaction keywords

Begin // open a transaction.
Commit // submit it to the database.
Rollback // cancel the operation.
Savepoint // save, partially cancel, partially submit.
Alter table person type = INNODB // modify the Data Engine.

Example:

begin  update person set name='efgh' where id =10 select * from person  rollback  select * from person 

Example:

alter table person type=INNODB begin  update person set name='efgh' where id =10 select * from person  commit  select * from person  begin  delete from person where id=21 update person set name='efgh' where id =10 commit/rollback 

The Save point must be used for the preceding submission.

Note:

1. You can only cancel rollback to savepoint p1 from a storage point.

2. A commit to savepoint p2 file cannot be submitted. // The incorrect syntax is as follows.

3. Finally, commit does not submit uncanceled storage points to the data.

Example of transaction retention point:

begin;  update score set score=40 where scoreid=1;  savepoint s1;  update score set score=50 where scoreid=2;  select * from score;  rollback to savepoint s1;  select * from score;  commit； 

Here is an introduction to MySQL transaction operations. If you want to know