MySQL database transaction rollback tu'ufua/talatala manatu/level processing php fa'amatalaga

Fa'aesea/fa'agaoioia manatu/taulimaina tulaga o fa'amatalaga PHP o fefa'ataua'iga i le fa'amaumauga o le MySQL

MySQL Mataupu

O fefaʻatauaiga a MySQL e masani ona faʻaaogaina e faʻatautaia ai faʻamaumauga ma le tele o gaioiga ma le lavelave maualuga.Mo se faʻataʻitaʻiga, i totonu o le pulega o tagata faigaluega, afai e te tapeina se tagata, e tatau ona e tape uma faʻamatalaga autu o le tagata ma faʻamatalaga e fesoʻotaʻi ma le tagata, e pei o pusameli, tala, ma isi. fai se fefa'ataua'iga!

• O fefaʻatauaiga e lagolagoina i MySQL naʻo faʻamaumauga poʻo laulau e faʻaogaina ai le Innodb database engine.
• E mafai ona fa'aogaina le fa'agaioiga o fefa'atauaiga e fa'amautu ai le fa'amaoni o fa'amaumauga, fa'amautinoa o lo'o fa'atinoina uma fa'amatalaga SQL pe leai fo'i se tasi e fa'atinoina.
• O fefa'atauaiga e fa'aaoga e pulea ai fa'aofi, fa'afou, tape fa'amatalaga

I le tulaga lautele, o fefaʻatauaiga e tatau ona ausia tulaga 4 (ACID): Atomicity (atomicity), Faʻamaoni (mautu), Tuʻuesea (tuʻueseeseina), Tumau (faʻalagolago)

• 1,Atomicity o fefa'atauaiga:Se seti o fefaʻatauaiga e manuia pe faaui i tua.
• 2,mautu:O lo'o iai fa'amaumauga e le tusa ai ma tulafono (fa'agata mai fafo ma fa'apena), ma fa'aui i tua le fefa'atauaiga.
• 3,Tu'ufua:O fefa'ataua'iga e fa'atino tuto'atasi.Afai o le taunuuga o se fefaʻatauaʻiga e aʻafia ai isi fefaʻatauaiga, ona faaui i tua lea o isi fefaʻatauaiga.100% vavaeeseina o fefaʻatauaiga i le faʻaalu o le saoasaoa.
• 4,fa'atuatuaina:A maeʻa le faʻalavelave faʻapipiʻi ma meafaigaluega, o le a faʻaaogaina e le avetaʻavale laulau faʻamaumauga a le InnoDB le faila ogalaau e toe faʻaleleia ma suia ai.Fa'atuatuaina ma le saoasaoa maualuga e le mafai ona maua uma, o le innodb_flush_log_at_trx_commit filifiliga e fuafua le taimi e teu ai fefaʻatauaiga i le ogalaau.

I lalo o tulaga le lelei o le MySQL command line, o fefaʻatauaiga e otometi lava ona faia, o lona uiga, o le COMMIT o le a faʻataunuʻuina vave pe a maeʻa le faʻamatalaga SQL.O le mea lea, ina ia tatala manino se fefaʻatauaiga e tatau ona faʻaaoga le poloaiga BEGIN poʻo START TRANSACTION, pe faʻatino le poloaiga SET AUTOCOMMIT = 0, e faʻamalo ai le faʻaogaina o le faʻaogaina otometi mo le taimi nei.

Fa'amatalaga fa'afoega o fefa'atauaiga:

• AMATA pe AMATA LE FEAGAIGA; amata manino se fefaʻatauaiga;
• COMMIT; COMMIT GALUEGA e mafai foi ona faʻaaogaina, ae tutusa le lua. COMMIT faia le fefaʻatauaiga ma faʻamautu suiga uma na faia i le database;
• ROLLBACK; e mafai ona fa'aoga ROLLBACK WORK, ae tutusa le lua.Toe fa'afo'i fa'ai'u le fefa'ataua'iga a le tagata fa'aoga ma fa'ate'aina uma suiga e le'i tu'uina atu o lo'o fa'agasolo;
• Fa'ailoga SAVEPOINT, SAVEPOINT e mafai ai ona fai se fa'asao i se fefa'atauaiga, ma e mafai ona tele SAVEPOINTs i se fefa'atauaiga;
• FA'AALI'E SAVEPOINT fa'ailoa, tape se nofoaga fa'asao o se fefa'ataua'iga, pe a leai se fa'amatalaga fa'asao fa'apitoa, o le fa'atinoina o le fa'amatalaga o le a tu'u ese ai;
• TALI I LE fa'ailoa, toe fa'afo'i le fefa'ataua'iga i le tulaga;
• SET TRANSACTION; fa'aaoga e seti ai le tulaga tu'ufua o le fefa'atauaiga. O le masini teu oloa a le InnoDB e tu'uina atu ai tulaga fa'aesea o fefa'ataua'iga o FA'ITAU E LE'I FA'ATAUINA, FA'AITAU FAITAU, FAITAU FA'ATAU'I, MA LE FA'ATAUINA.

E lua auala autu o le MYSQL fefaʻatauaiga:

1. Fa'aaoga le AMATA, ROLLBACK, COMMIT e ausia ai

• GALUEGA amata se fefaʻatauaiga
• TOE FAI toe fa'afo'i fefa'ataua'iga
• KOMITI Fa'amaoniga o fefa'atauaiga

2. Fa'aoga sa'o le SET e sui ai le faiga o le auto-commit a MySQL:

• SETAUTOCOMMIT=0 Taofi le auto-commit
• SETAUTOCOMMIT=1 Ki ai le auto-commit

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>

Fa'aaogaina o Fa'amatalaga Fa'amatalaga ile PHP

<?
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);
?>