MySQL transaction example tutorial

MySQL Transaction

MySQL transaction is 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 that use the Innodb database engine support transactions

Transaction processing can be used to maintain the integrity of the database and ensure that batches of SQL statements are either fully executed or Do not execute all

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

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

start transaction

save point 保存点名称

Using transaction examples 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("ROOLBACK");//判断当执行失败时回滚
}
if(!mysql_query("insert into trans (id) values(&#39;4&#39;)"))
{
mysql_query("ROOLBACK");//判断执行失败回滚
}
mysql_query("COMMIT");//执行事务
mysql_close($handler);
?

Special recommendation:"php Programmer Toolbox" V0.1 version download

Free mysql online video tutorial

Those things about database design

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