What is transaction processing in MySQL? (code example)

The content of this article is to introduce to you what MySQL transaction processing is? (code example). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

MySQL transaction processing

Steps:

1. Start transactionstart transaction

When we start a transaction, all our SQL operations occur in memory, but there is no real feedback to the file on the database disk!

2. Rollback rollback

Rollback is to restore to the original state before the transaction was started!

Note: The rollback operation will automatically close a transaction. If you want to execute the transaction again, you need to reopen the transaction!

3. Submitcommit

Basic principles of transactions

Ordinary execution is executed immediately and takes effect because by default, MySQL automatically submits the execution of the sql statement! Therefore, the essence of opening a transaction is to turn off the previous automatic submission function, and instead submit it manually (using the commit statement) by the user! [Related video tutorial recommendations: MySQL Tutorial]

Summary of the steps of the transaction:

1, Open the transaction

2, If the execution is successful, submit commit

3, If any SQL statement fails to execute, rollback!

Examples of transaction processing:

The most typical transaction processing is borrowing and repaying money,The following is an example of Zhang San repaying 1,000 yuan to Li Si:

First check the respective amounts of money in the database

The following is the processing of the return Code of money transaction:

<?php  

/**
* MySQL实现事务操作
*/

echo "<meta charset=utf-8>";

// 1 连接数据库
$link = @mysql_connect('localhost','root','') or die('连接数据库失败');
mysql_select_db('test',$link);
mysql_query('set names utf8');

// 2  开启事务
mysql_query("start transaction");
//设置一个变量，用来判断所有sql语句是否成功
$flag = true;

// 2.1执行事务中的一组sql语句 
   
// 李四的money+1000
$sql = "update pdo set mone=money+1000 where name='李四'";
$res = mysql_query($sql);
if (!$res) {
    //若sql语句执行失败，把$falg设置为false
    $flag = false;
}

//张三的money-1000
$sql = "update pdo set money=money-1000 where name='张三'";
$res = mysql_query($sql);
if (!$res) {
    //若sql语句执行失败，把$falg设置为false
    $flag = false;
}

// 2.2 判断事务是否执行成功
if ($flag) {
    //所有sql语句执行成功，把sql语句提交
    mysql_query('commit');
    echo "还钱成功！";
}else{
    // 如其中一条执行失败，则回滚到事务开启之前的状态
    mysql_query('rollback');
    echo "还钱失败！";
}

Result:

Below, we deliberately write one of the fields Wrong, check whether the transaction is processed normally and whether the amount of money in the database has changed!

// 李四的money+1000
$sql = "update pdo set mone=money+1000 where name='李四'";  //把moeny字段错写成mone

Result:

The result is that the repayment failed, and the respective amounts of money in the database have not changed, indicating that When a certain statement is not executed successfully, the thing will not be committed, but will be rolled back to restore the data to the original state before starting the transaction. This is also the role of using transactions, that is, only when all sql statements in the transaction are executed The transaction will be committed only if it succeeds, otherwise it will be rolled back!

Summary: The above is the entire content of this article, I hope it will be helpful to everyone's study.

The above is the detailed content of What is transaction processing in MySQL? (code example). For more information, please follow other related articles on the PHP Chinese website!