MySQL transaction

MySQL transactions are mainly used to process data with large operations and high complexity. For example, in the personnel management system, if you delete a person, you need to delete the basic information of the person, and also delete the information related to the person, such as mailbox, articles, etc. In this way, these database operation statements constitute a transaction. !

• In MySQL, only databases or tables using the Innodb database engine support transactions

• Transaction processing can be used to maintain the integrity of the database , to ensure that batches of SQL statements are either all executed or not executed at all

• Transactions are used to manage insert, update, and delete statements

General Generally speaking, transactions must meet four conditions (ACID): Atomicity (atomicity), Consistency (stability), Isolation (isolation), Durability (reliability)

• 1 . Atomicity of transactions: A set of transactions either succeeds or is withdrawn.

• 2. Stability: If there is illegal data (foreign key constraints and the like), the transaction will be withdrawn.

• 3. Isolation: Transactions run independently. If the result of one transaction affects other transactions, the other transactions will be withdrawn. 100% isolation of transactions requires sacrificing speed.

• 4. Reliability: After the software or hardware crashes, the InnoDB data table driver will use the log file to reconstruct and modify it. Reliability and high speed cannot be achieved at the same time. The innodb_flush_log_at_trx_commit option determines when to save transactions to the log.

Use transactions in the Mysql console to operate

1, start a transaction

start transaction

2, Make a save point

save point Save point name

3, operation

4, you can roll back, you can submit. If there is no problem, just submit it. If there is any problem, just submit it. rollback.

Examples of using transactions in PHP

<?php
$handler=mysql_connect("localhost","root","password");
mysql_select_db("task");
mysql_query("SET AUTOCOMMIT=0");//设置为不自动提交，因为MYSQL默认立即执行
mysql_query("BEGIN");//开始事务定义
if(!mysql_query("insert into trans (id) values(&#39;2&#39;)"))
{
mysql_query("ROLLBACK");//判断当执行失败时回滚
}
if(!mysql_query("insert into trans (id) values(&#39;4&#39;)"))
{
mysql_query("ROLLBACK");//判断执行失败回滚
}
mysql_query("COMMIT");//执行事务
mysql_close($handler);
?>

The above is the content of MySQL transactions. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!