MySQL Advanced learning Note nine: application of MySQL transaction! (Video serial Number: Advanced _21-22)

Knowledge Point 10: Application of MySQL Transaction (21-22)why to introduce a transaction:

Why introduce transactions to this technology? Now many of the software are multi-user, multi-program, multi-threaded. The same table may be used by many people at the same time, in order to maintain the consistency of data, so the concept of a transaction. This is an abstraction, for example:

A to B to draw money, A's account is-1000 yuan, B's account is + 1000 yuan, these two update statements must be executed as a whole, otherwise a deducted money, B No money this situation is difficult to deal with.

what type of stored procedure supports transactions:

1. Review the supported transactions under the database (InnoDB support):

SHOW ENGINES;

2. View the current default storage engine for MySQL:

 like '%storage_engine%';

3. View the storage engine for a table:

CREATE TABLE tbl_name;

4. Modifications to the storage structure of the table:

CREATE  Table ... type=InnoDB; Modify the storage engine for tables:ALTER table tbl_name Type=InnoDB ;

How to create a transaction:

1 CREATE DATABASEBank;2  UseBank;3 CREATE TABLEAccount (4Aidint  not NULL,5AccnameVARCHAR( -) not NULL,6AccmoneyDECIMAL( -,2) not NULL,7 PRIMARY KEY(AID)8) ENGINE=InnoDBDEFAULTCHARSET=GBK;9 Ten INSERT  intoAccountVALUES(1,'A',4000), One(2,'B',4000);

Default Datasyntax for creating a transaction:

1. Open transaction start transaction, can be abbreviated to begin (Note: Start transaction will cause an implicit unlock tables execution)
2. Then record a set of SQL that needs to be executed after
3. Commit a Commit
4. If all SQL executes successfully, commit and persist the results of SQL execution into the data table.
5. Rollback rollback
6. If there is a failed SQL, rollback is required to return the results of the SQL execution to the beginning of the transaction
7. Regardless of rollback or commit, the transaction will be closed! You need to turn it on again to use it.
8. Another point to note is that the transaction is only for the current connection.

commit a transaction:Fundamentals:

Commits, the result is persisted, not committed.

If we do not open the transaction and execute only one SQL, the data will be persisted immediately , and it can be seen that the normal execution is to commit immediately.
This is because MySQL default execution of SQL statements is automatically committed .

That is, to open the transaction, is actually turned off the auto-commit function, changed to commit the manual commit!

We can do this by simply setting it up for automatic submission.
The auto-commit feature is stored within a autocommit variable of the service. Can be modified:

SET autocommit=0;

　　　　　　　　Description

The transaction behavior that can be controlled is called a autocommit set session variable.　　　　　　　　　　If Autocommit is set to 1 (the default), then each SQL statement (either in the transaction or not) is considered to be a complete transaction and is committed by default when it is complete. When Autocommit is set to 0 o'clock, the set autocommit = 0 command is issued, and the subsequent sequence of statements acts like a transaction until an explicit commit statement is made, and there is no active commit. 

　　　　　　　　It is also important to note that transactions are similar to foreign KEY constraints and are supported only by the InnoDB engine.　　　　　　　

Add:

Commit  and chain: Indicates that a new transaction was reopened after the transaction was committed.  rollback and release: Indicates that the connection to the client is broken after the transaction is rolled back.

1 --Start a transaction2StartTransaction;3 --Commit a transaction4 Commit;5 6 --start two processes to view a situation7 SETAutocommit=0;8StartTransaction;9 UPDATEAccountSETAccmoney=Accmoney- + WHEREAid=1;Ten UPDATEAccountSETAccmoney=Accmoney+ + WHEREAid=2; One COMMIT; A  - SETAutocommit=1; - UPDATEAccountSETAccmoney=Accmoney- + WHEREAid=1;

commit tests for transactionsRollback of a transaction:

Returns the state of the data before the transaction occurs. usually rollback.

To restore a test point:

1StartTransaction;2 UPDATEAccountSETAccmoney= - WHEREAid=1;3 rollback;4 5StartTransaction;6 UPDATEAccountSETAccmoney=7000 WHEREAid=1;7 Commit  andchain;8 9 Ten --restore point Test One SETAutocommit=0; A INSERT  intoAccountVALUES(3,'test1', the); - savepoint S1; - INSERT  intoAccountVALUES(4,'test2',6000); the savepoint S2; - INSERT  intoAccountVALUES(5,'test3',7000); - savepoint S3; - --After executing three INSERT statements, SELECT * From account can see these three data, if you want to roll back to the original + --rollback is the first state of nothing. If you want to go back to the state of SavePoint S1 (that is, insert a test1 there) - --rollback to savepoint s1, the same thing can do anything. + rollback  toSavePoint S2;

Rollback and restore point tests for transactions　　Summary transaction:

A transaction should have 4 attributes, atomicity, consistency, isolation, and persistence. These four properties are often called ACID properties:

Atomicity (atomicity): A transaction is an inseparable unit of work, and the operations included in the transaction are either done or not.

Consistency (consistency): A transaction must be to make the database from one consistent state to another consistency state. Consistency is closely related to atomicity.

Isolation (Isolation): The execution of one transaction cannot be disturbed by other transactions. That is, the operations inside a transaction and the data used are isolated from other transactions that are concurrently executing, and cannot interfere with each other concurrently.

Persistence (Durability): Persistence is also called permanence (permanence), which, once a transaction is committed, changes the data in the database to be permanent. The next operation or failure should not have any effect on it.

MySQL Advanced learning Note nine: application of MySQL transaction! (Video serial Number: Advanced _21-22)