MySQL's business

MySQL's business

1. Transaction: A transaction is a logical execution unit that consists of a sequence of one-or several-step database operations, which either executes all or discards execution.

2. Four characteristics of a transaction (for short, acid):

(1) atomicity (atomicity):

The transaction is the smallest execution unit in the application, has the non-fractal characteristic, the transaction is the smallest logical execution body in the application;

(2) Consistency (consistency):

The result of the transaction execution must shift the database from one consistent state to another consistent state. When the database contains only the results of a successful transaction submission, the database is in a consistent state. If a system outage occurs, a transaction is not completed and is forced to break, and the unfinished transaction changes to the database have been written to the database, the data is in an incorrect state, the incorrect state is in an inconsistent state. Consistency is guaranteed by atomicity.

(3) Isolation (isolation):

The execution of each transaction does not interfere with each other, and the internal operations of any one transaction are isolated for other concurrent transactions. The concurrent execution of transactions does not affect the middle state of the other party.

(4) Persistence (durability):

Also becomes persistent, refers to the transaction one but commits, any changes made to the data will be recorded in the permanent memory, that is, in the physical database.

3. Transaction composition:

(1) A set of DML statements: The language that is recorded in the Operation database table;

(2) A DDL statement: The language of manipulating database objects;

Note: Database objects refer to tables (table), Data dictionaries, constraints (constraint), Views (view), index (s), functions (function), stored Procedures (procedure), triggers (trigger), etc. Where the data dictionary is actually a special table that stores information about the database and is usually not manipulated.

(3) A DCL statement: Data control statements.

Where the DDL statement and the DCL statement can only have one, the DDL statement can cause the DCL statement to commit the transaction immediately.

4. How to commit a transaction:

(1) Explicit commit: use commit keyword;

(2) Autocommit: Executes DDL or DCL statements.

5. Transaction rollback:

　　when any of the database operations contained in the transaction fails to execute, it should be rolled back (RO llback) transaction so that all modifications made in the transaction are invalidated. There are two ways to roll back a transaction:

(1) Explicit rollback: use rollback keywords;

(2) Implicit rollback: System error or forced exit.

Transaction support in 6.MySQL:

(1) MySQL closes the transaction by default (that is, turn on autocommit transactions), and by default, enters a DML statement in the MySQL console, which is immediately saved to the database. You can use the following statement to open a transaction (that is, to turn off autocommit transactions):

Set autocommit = 0; (Note: Set autoacommit = Close Transaction by default)

After this command is called, all DML statements after the end of the previous transaction are in the same transaction, unless a transaction is committed with commit, or if a DDL statement or a DCL statement implicitly commits the transaction, all DML statements are in the same transaction. You can also use rollback rollback to end a transaction, but using rollback will invalidate the DML statements in this transaction.

Note: Using Set autocommit = 0 causes the entire session (which can simply be understood as a command-line window) to open the transaction.

If you do not want the entire session to open a transaction, you can use the start transaction or BEGIN command to temporarily open the transaction, and then use commit to commit the transaction explicitly after the DML statement group ends, or implicitly commit the transaction by using a DDL statement or a DCL statement.

(2) Normal rollback and commit will cause the end of the transaction, and if you use SavePoint to set the middle point of the transaction, the transaction will not end when rollback to the middle point.

7. Examples of transaction commits and rollbacks:

(1) Example 1:

#创建一张表Create TableStudents (s_idint  not NULL, S_namevarchar(255) not NULL); #插入一条记录Insert  intoStudentsValues(1,'YWL'); #插入另一条记录Insert  intoStudentsValues(2,'LJ'); #由于在MySQL中事务是默认关闭的, so when the above two DML statements are executed one at a time # will be automatically committed # Execute the query: The result is that there are two records in the students tableSelect *  fromstudents; #在此处临时开启MySQL的事务begin; #修改表中的数据UpdateStudentSetS_name='QQ' wheres_id=1, #注意, did not commit the transaction, #查询一次: The table was modified, but notice that the transaction is not committed here, the results of the query is an illusion # hahaSelect *  fromstudents; #进行回滚事务rollback; #事务结束, because rollback, DML statements that modify table records do not execute # Query the table and find that the table is still in its original stateSelect *  fromstudents; #使用start transaction is the same as begin, try it with the same example # Open the transaction for the connected sessionSetAutocommit= 0;UpdateStudentSetS_name='QQ' wheres_id=1;UpdateStudentSetS_name='BB' wheres_id=2; #执行到这里其实并未提交事务, the # data table changes only after committing a transaction using commit # Commit a transactionCommit;

(2) Example 2, set the middle point for rollback:

#打开事务SetAutocommit= 0; #创建表Create TableTeacher (t_idint  not NULL, T_namevarchar(255) not NULL#注意, although there is no explicit commit transaction using Commit, the # Build table statement is a DDL statement, so this transaction has been implicitly committed # The following is a new transactionInsert  intoTeacherValues(1,'a');Insert  intoTeacherValues(2,'b'); #查询一下 # There is content in the teacher tableSelect *  fromteacher; #直接回滚事务rollback; #查询teacher表, no records in the tableSelect *  fromteacher; #此时的事务已经结束, end result, no operation on teacher table # New transaction StartInsert  intoTeacherValues(1,'a'); #设置rollback的中间点savepoint A;Insert  intoTeacherValues(2,'b'); #查询下teacher表Select *  fromteacher; #回滚到中间点rollback  toA; #再次查询下teacher表Select *  fromteacher; #由于回滚到中间点的rollback未关闭此事务 so that transactions can be committedCommit; #查询teacher表 observation ResultsSelect *  fromTeacher

　　

MySQL's business