Article directory
MySQL databaseTransaction rollback isolation/open concept/level processing php statement
MySQL Business
MySQL transactions are mainly used to process data with a large amount of operations and high complexity.For example, in the personnel management system, if you delete a person, you need to delete both the basic information of the person and the information related to the person, such as mailboxes, articles, etc. In this way, these database operation statements constitute a transaction !
- Transactions are supported in MySQL only for databases or tables that use the Innodb database engine.
- Transaction processing can be used to maintain the integrity of the database, ensuring that batches of SQL statements are either all executed or none of them are executed.
- Transactions are used to manage insert, update, delete statements
Generally speaking, a transaction must meet 4 conditions (ACID): Atomicity (atomicity), Consistency (stability), Isolation (isolation), Durability (reliability)
- 1,Atomicity of transactions:A set of transactions that either succeed or withdraw.
- 2,stability:There is illegal data (foreign key constraints and the like), and the transaction is withdrawn.
- 3,Isolation:Transactions run independently.If the result of a transaction affects other transactions, then other transactions will be withdrawn.100% isolation of transactions at the expense of speed.
- 4,reliability:After the software and hardware crash, the InnoDB data table driver will use the log file to reconstruct and modify it.Reliability and high speed cannot have both, the innodb_flush_log_at_trx_commit option determines when to save transactions to the log.
Under the default settings of the MySQL command line, transactions are automatically committed, that is, the COMMIT operation will be executed immediately after the SQL statement is executed.Therefore, to explicitly open a transaction must use the command BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0, to disable the use of automatic commit for the current session.
Transaction control statement:
- BEGIN or START TRANSACTION; explicitly start a transaction;
- COMMIT; COMMIT WORK can also be used, but the two are equivalent. COMMIT commits the transaction and makes all modifications made to the database permanent;
- ROLLBACK; it is possible to use ROLLBACK WORK, but the two are equivalent.Rollback ends the user's transaction and undoes all uncommitted modifications in progress;
- SAVEPOINT identifier; SAVEPOINT allows to create a savepoint in a transaction, and there can be multiple SAVEPOINTs in a transaction;
- RELEASE SAVEPOINT identifier; delete a savepoint of a transaction, when there is no specified savepoint, executing the statement will throw an exception;
- ROLLBACK TO identifier; roll back the transaction to the point;
- SET TRANSACTION; used to set the isolation level of the transaction. The transaction isolation levels provided by the InnoDB storage engine are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.
There are two main methods of MYSQL transaction processing:
1. Use BEGIN, ROLLBACK, COMMIT to achieve
- BEGIN start a transaction
- ROLL BACK transaction rollback
- COMMIT Transaction confirmation
2. Use SET directly to change MySQL's auto-commit mode:
- SETAUTOCOMMIT=0 Disable auto-commit
- SETAUTOCOMMIT=1 Turn on auto-commit
transaction test
mysql> use chenweiliang; Database changed mysql> CREATE TABLE chenweiliang_transaction_test( id int(5)) engine=innodb; # 创建数据表 Query OK, 0 rows affected (0.04 sec) mysql> select * from chenweiliang_transaction_test; Empty set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into chenweiliang_transaction_test value(5); Query OK, 1 rows affected (0.01 sec) mysql> insert into chenweiliang_transaction_test value(6); Query OK, 1 rows affected (0.00 sec) mysql> commit; # 提交事务 Query OK, 0 rows affected (0.01 sec) mysql> select * from chenweiliang_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into chenweiliang_transaction_test values(7); Query OK, 1 rows affected (0.00 sec) mysql> rollback; # 回滚 Query OK, 0 rows affected (0.00 sec) mysql> select * from chenweiliang_transaction_test; # 因为回滚所以数据没有插入 +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql>
Using Transaction Instances in PHP
MySQL ORDER BY test:
<? php $dbhost = 'localhost:3306'; // mysql服务器主机地址 $dbuser = 'root'; // mysql用户名 $dbpass = '123456'; // mysql用户名密码 $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('连接失败: ' . mysqli_error($conn)); } // 设置编码,防止中文乱码 mysqli_query($conn, "set names utf8"); mysqli_select_db( $conn, 'chenweiliang' ); mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交,因为MYSQL默认立即执行 mysqli_begin_transaction($conn); // 开始事务定义 if(!mysqli_query($conn, "insert into chenweiliang_transaction_test (id) values(8)")) { mysqli_query($conn, "ROLLBACK"); // 判断当执行失败时回滚 } if(!mysqli_query($conn, "insert into chenweiliang_transaction_test (id) values(9)")) { mysqli_query($conn, "ROLLBACK"); // 判断执行失败时回滚 } mysqli_commit($conn); //执行事务 mysqli_close($conn); ?>
Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) shared "MySQL Database Transaction Rollback Isolation/Open Concept/Level Processing of PHP Statements", which is helpful to you.
Welcome to share the link of this article:https://www.chenweiliang.com/cwl-494.html
Welcome to the Telegram channel of Chen Weiliang's blog to get the latest updates!
📚 This guide contains huge value, 🌟This is a rare opportunity, don’t miss it! ⏰⌛💨
Share and like if you like!
Your sharing and likes are our continuous motivation!