How to implement MySQL transaction security

Transaction security transaction

Transaction transaction accesses a program execution unit that may update various data items in the database

A transaction starts with a transaction (begin transaction) and ends with a transaction (end transaction) All operations performed in between are composed of

Basic Principles of Transactions

MySQL allows transactions to be managed uniformly (storage engine innodb), and the operations performed by the user are temporarily saved and not directly placed in the database. Data table (update), wait until the user confirms the result before proceeding

Transactions are usually submitted automatically, or they can be submitted manually

Automatic transactions

When the client sends a SQL When instructions (write operations, additions, deletions and modifications) are given to the server, the server will automatically synchronize the results to the data table without waiting for user feedback after execution.

Two clients, one client executes the SQL command, Another client views the execution results

Control automatic transactions through variables

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
-- 关闭自动事务
set autocommit = off;

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

After closing the automatic transaction, one client modifies the data, and the other client cannot see the modified results

Once the automatic transaction is turned off, the user needs to provide a synchronization command

• commit submission (synchronized to the data table, the transaction will be cleared)

• rollback Rollback (clear previous operations, no more)

-- 客户端A关闭自动事务后操作数据
mysql> select * from my_class;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  3 | 三班   |
+----+--------+
2 rows in set (0.01 sec)
mysql> insert into my_class (name) values('四班');
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_class;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  3 | 三班   |
|  5 | 四班   |
+----+--------+
3 rows in set (0.00 sec)
-- 客户端B看不到新增的 四班数据, 
mysql> select * from my_class;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  3 | 三班   |
+----+--------+
2 rows in set (0.00 sec)

After client A executes commit to submit the transaction, client B can see the new data

Usually there is no need to turn off automatic transactions. When you need to use transactions, use manual transactions

Manual transactions

The user must manually send transaction operation instructions for the start, process, and end. Implement

Manual transaction instructions

-- 1、开启事务，从这条语句开始，后面所有的语句都不会直接写入到数据表，保存在事务日志中
start transaction 
-- 2、事务处理，多个指令构成
-- 3、事务提交，结束事务
commit / rollback

Use of transactions

mysql> select * from my_class;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  3 | 三班   |
|  5 | 四班   |
+----+--------+
3 rows in set (0.04 sec)
mysql> select * from my_student;
+----+--------+----------+------+--------+
| id | name   | class_id | age  | gender |
+----+--------+----------+------+--------+
|  1 | 刘备   |        1 |   18 |      2 |
|  2 | 李四   |        1 |   19 |      1 |
|  3 | 王五   |     NULL |   20 |      2 |
|  4 | 张飞   |     NULL |   21 |      1 |
|  5 | 关羽   |     NULL |   22 |      2 |
|  6 | 曹操   |        1 |   20 |   NULL |
+----+--------+----------+------+--------+
6 rows in set (0.00 sec)
-- 开启事务
start transaction;
-- 执行事务操作，多个修改操作
insert into my_class (name)values ('六班');
insert into my_student (name, class_id, age, gender)values ('司马懿', 6, 26, 1);
-- 提交事务
commit;
-- 或者回滚操作，所有数据无效清空
rollback;

Rollback point

When there is a series of transaction operations, and if the steps are successful , there is no need to start over, you can set a mark (rollback point) at a certain point, and then if there is a failure later, you can return to the position of this mark

-- 增加回滚点
savepoint 回滚点名字;
-- 回到回滚点 清空之后所有操作
rollback to 回滚点名字;

During a transaction, if There are many steps and you can set multiple rollback points

Characteristics of transactions

ACID:

• Atomicity A transaction is an indivisible work Unit, either do it all or do nothing

• Consistency transaction must change the database from one consistency state to another consistency state

• Isolation isolation The execution of a transaction cannot be interfered by other transactions. When operating data, the data will be locked

• Durability Durability Once a transaction is submitted, it will Data changes in the database are permanent

If an index is used in the condition, a record will be isolated; otherwise, through full table retrieval, the entire table will be locked

The above is the detailed content of How to implement MySQL transaction security. For more information, please follow other related articles on the PHP Chinese website!