A detailed explanation of the things in MySQL

1.1. Definition and characteristics of things

A transaction is a unit of work that consists of a set of SQL statements that manipulate a database, in which all operations succeed at the same time or fail at the same time. Things have the following four characteristics, acid is referred to as "acidic."

1) atomicity: All operations in the work cell are either successful or unsuccessful, and there is no case of partial success.

2) Consistency: the completion of the work should be consistent with the expected results, such as the transfer of a account to the B account, if the successful implementation of the thing must ensure that a account to transfer the amount of money, B account to transfer the corresponding amount of money; if the thing fails, the transfer will fail. The other three properties of things are to ensure that consistency.

3) Isolation: Isolation can also be called concurrency control, serializable, locks and so on. The data that is being manipulated in things is isolated to prevent inconsistencies in the data that other users are accessing.

4) Persistence: Once a transaction is committed, its modifications are persisted to the database, and should not have any effect even if the database fails.

The persistence of a transaction cannot be 100% durable and can only be guaranteed to be permanent from the perspective of the transaction itself, while some external causes cause the database to fail, such as a hard disk corruption, and all committed data may be lost.

1.2.1.2.1 The method of opening a transaction in MySQL. Method One

Use begin or start transaction to open a thing, commit, or rollback to end the thing.

2. --Save Point SavePoint
4. begin
6. declare is_error int default false; #是否出错的标志
8. Declare continue Handler for SqlException
10. Set is_error=true#声明异常处理程序, if SQL exception, sets the flag to true
12. Start TRANSACTION; #开启事务 will fail at the same time and succeed
14. SavePoint S1; #创建保存点
16. Insert into employee (ID,name, salary) values(146, ' CQ ', 9000);
18. SavePoint S2;
20. Insert into employee (ID,name, salary) values(101, ' CQ ', 9000);
22. Insert into employee (ID,name, salary) values(102, ' CQ ', 9000);
24. If Is_error Then
26. Rollback to SavePoint s1;--revert to S1
28. Insert into employee (ID,name, salary) values(151, ' CQ ', 9000);
30. Insert into employee (ID,name, salary) values("CQ", 9000);
32. commit;
34. end if;
36. End;

1.2.2. Method Two

Turn off autocommit, set autocommit = 0, all actions after the statement become things, and when auto-commit is turned off, every thing that ends its subsequent operation opens a new thing.

1. set autocommit=0; #关闭自动提交
2. #因为关闭了自动提交事务, adding data is not saved to the database
3. Insert into employee (ID,name, salary) values(143, ' CQ ', 9000);
4. commit; #手动提交所有未执行的数据
5. #由于是关闭自动提交的方式开启的事务, so every thing that ends up doing something automatically opens something new.
6. Insert  into employee (ID,name, salary) values(258, ' wd ', 8000); #该操作属于新启的事物

1.3. Implicit submission of things

Since things cannot be nested, the old things in front of them are implicitly submitted when the new thing is opened. The following situations can cause things to be implicitly committed:

1) The opening of new things will lead to the implicit submission of old things

START TRANSACTION;

INSERT into ' dm_ Gender ' (gender name) VALUES (' Unlimited '); #该操作会被隐式提交

START TRANSACTION;

INSERT into ' dm_ Gender ' (gender name) VALUES (' Men and women ');

ROLLBACK;

2) All DDL or DCL operations in InnoDB will open a new thing, so DDL or DCL statements can lead to the implicit submission of old things

SET autocommit = 0; #利用法二关闭自动提交来开启事务

BEGIN;

INSERT into T1 VALUES (1);

#该DDL语句会导致其前面的插入操作隐式提交, and open a single thing

CREATE TABLE T2 (PK int primary key);

INSERT into T2 VALUES (2); #自动开启新的事物

ROLLBACK; #插入表t1的数据已提交, you can only roll back the Insert Table T2 operation

3) The execution area of the process ends with an implicit commit before end end

BEGIN

START TRANSACTION;

INSERT into ' dm_ Gender ' (gender name) VALUES (' unlimited ');

INSERT into ' dm_ Gender ' (gender name) VALUES (' Men and women ');

END #在此之前会导致事物的隐式提交

1.4. Precautions concerning the operation of the thing

① the execution area of a stored procedure begins a thing, and the end of the execution area is implicitly committed once

BEGIN

INSERT into ' dm_ Gender ' (gender name) VALUES (' unlimited ');

INSERT into ' dm_ Gender ' (gender name) VALUES (' Men and women ');

# # Commit implicitly commits the operation of the execution area

END

② do not commit in the middle of things, on the one hand will destroy the atomic nature of things, on the other hand, this thing will come to an end

CREATE TABLE TestProc (ID int (4) primary key, name varchar (100));

#测试过程

CREATE PROCEDURE Test_proc_ins (

In i_id INT,

In I_name VARCHAR (100)

)

BEGIN

Start transaction; #本意是将两次插入操作捆绑成一个事物

INSERT into TestProc VALUES (i_id, i_name);

COMMIT, #由于中途提交导致该事物提交前结束, the subsequent operation is no longer a matter of action

INSERT into TestProc VALUES (i_id, i_name); #这里故意违反主键约束

ROLLBACK, #由于第一条插入数据的操作已提交, so the ROLLBACK here is invalid.

END;

③ because DDL or DCL operations Create new things, which causes their previous operations to be implicitly committed, thus destroying the atomicity of things, so try not to use DDL or DCL statements in the process. and the syntax for using DDL or DCL statements in the process is more complex, so it is not recommended to use DDL or DCL statements in procedures.

SET autocommit = 0;

BEGIN;

INSERT into T1 VALUES (1);

#该DDL语句开启新事物会隐式提交其前的事物

CREATE TABLE T2 (PK int primary key); INSERT into T2 VALUES (2);

ROLLBACK;

SHOW TABLES

④start Transaction (Begin) differs from closed autocommit open transaction: Start Transaction only opens a current thing, the action after which the end of the thing is no longer a thing operation, but the way to turn off autocommit is to open the transaction. The actions that follow each thing end automatically default to the new thing action.

⑤mysql transaction support is not tied to the MySQL server itself, but is related to the storage engine:

1.MyISAM: Transactions are not supported for read-only programs to improve performance

2.InnoDB: Supports acid transactions, row-level locks, concurrency

3.Berkeley DB: Support transactions

A detailed explanation of the things in MySQL