PHP mysql transaction processing example

Transactions must meet 4 conditions (ACID): atomicity (Autmic), consistency (Consistency), isolation (Isolation), durability (Durability)

Atomicity (Autmic): transactions must be executed during execution To "either don't do it or do it all!" means that no part of the transaction is allowed to be executed. Even if the transaction cannot be completed due to a failure, the impact on the database must be eliminated during rollback!

Consistency: Transaction operations should transform the database from one consistent state to another! Take online shopping as an example. You can only create a business by getting the goods out of the warehouse and entering the customer's shopping basket!

Isolation: If multiple transactions are executed concurrently, it should be as if each transaction is executed independently!

Durability: A successfully executed transaction has a lasting effect on the database. Even if the database fails and an error occurs, it should be able to recover!

There are two main methods of transaction processing in MYSQL.
1. Use begin, rollback, and commit to implement
Begin to start a transaction
Either you execute commit or rollback
2. Use set directly to change the automatic submission mode of mysql
          MYSQL automatically submits by default, that is, if you submit a QUERY, it will be executed directly! We can implement transaction processing by
                                                                                                use using
          use ‐ ‐ ‐ ‐ ‐ ‐ ‐‐ ‐ set autocommit=0 ​ to prohibit automatic submission
                                                                out out off ‐ ‐ ‐ ‐ ‐‐ ‐‐‐‐ ‐ to disable automatic submission
                                           

Note that when you use set autocommit=0, all your subsequent SQL will be processed as transactions until you confirm it with commit or rollback. Note that when you end this transaction, you also start a new transaction! According to the first method, only the current one is used as a transaction!

Only INNODB and BDB type data tables in MYSQL can support transaction processing! Other types are not supported!
***: Generally, the default engine of MYSQL database is MyISAM. This engine does not support transactions! If you want MYSQL to support transactions, you can modify it manually:
The method is as follows: 1. Modify the c:appservmysqlmy.ini file, find skip-InnoDB, add # in front, and save the file.
2. Enter: services.msc during operation to restart the mysql service.
3. Go to phpmyadmin, mysql->show engines; (or execute mysql->show variables like 'have_%';), and check that InnoDB is YES, which means the database supports InnoDB.
This means that transaction transactions are supported.
4. When creating a table, you can select the InnoDB engine for the Storage Engine. If it is a previously created table, you can use mysql->alter table table_name type=InnoDB; or mysql->alter table table_name engine=InnoDB; to change the engine of the data table to support transactions.

Example 1.

$conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!"); 
    mysql_select_db('test',$conn); 
    mysql_query("set names 'GBK'"); //使用GBK中文编码; 
    //开始一个事务 
    mysql_query("BEGIN"); //或者mysql_query("START TRANSACTION"); 
    $sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')"; 
    $sql2 = "INSERT INTO `user` (`did`, `username`, `sex`) VALUES (NULL, 'test1', '0')";//这条我故意写错 
    $res = mysql_query($sql); 
    $res1 = mysql_query($sql2);  
    if($res && $res1){ 
    mysql_query("COMMIT"); 
    echo '提交成功。'; 
    }else{ 
    mysql_query("ROLLBACK"); 
    echo '数据回滚。'; 
    } 
    mysql_query("END");

Example 2

Php code

mysql_query("SET AUTOCOMMIT=0"); //设置mysql不自动提交，需自行用commit语句提交   
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";   
$sql2 = "INSERT INTO `user` (`did`, `username`, `sex`) VALUES (NULL, 'test1', '0')";//这条我故意写错   
$res = mysql_query($sql);   
$res1 = mysql_query($sql2);    
if($res && $res1){   
mysql_query("COMMIT");   
echo '提交成功。';   
}else{   
mysql_query("ROLLBACK");   
echo '数据回滚。';   
}   
mysql_query("END"); //事务处理完时别忘记mysql_query("SET AUTOCOMMIT=1");自动提交

For MyISAM engine databases that do not support transactions, you can use the table locking method

//Both MyISAM & InnoDB support,

LOCK TABLES can lock tables used by the current thread. If the table is locked by another thread, blocking occurs until all locks can be acquired.
UNLOCK TABLES releases any locks held by the current thread. When a thread issues another LOCK TABLES, or when the connection to the server is closed, all tables locked by the current thread are implicitly unlocked.
*/

Php code

mysql_query("LOCK TABLES `user` WRITE");//锁住`user`表   
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";   
$res = mysql_query($sql);   
if($res){   
echo '提交成功。!';   
}else{   
echo '失败!';   
}   
mysql_query("UNLOCK TABLES");//解除锁定