MySQL database transaksi rollback isolasi/konsep terbuka/level processing php statement

database MySQLTransaksi rollback isolasi/konsep terbuka/level processing php statement

MySQL Urusan

Transaksi MySQL utamane digunakake kanggo ngolah data kanthi akeh operasi lan kerumitan sing dhuwur.Contone, ing sistem manajemen personel, yen sampeyan mbusak wong, sampeyan kudu mbusak informasi dhasar saka wong lan informasi sing gegandhengan karo wong, kayata kothak layang, artikel, etc. Kanthi cara iki, statements operasi database iki. dadi transaksi!

• Transaksi didhukung ing MySQL mung kanggo database utawa tabel sing nggunakake mesin database Innodb.
• Pangolahan transaksi bisa digunakake kanggo njaga integritas basis data, kanggo mesthekake yen batch statement SQL kabeh dieksekusi utawa ora ana sing dieksekusi.
• Transaksi digunakake kanggo ngatur insert, nganyari, mbusak statements

Umumé, transaksi kudu nyukupi 4 syarat (ACID): Atomisitas (atomicity), Konsistensi (stabilitas), Isolasi (isolasi), Daya tahan (reliabilitas)

• 1,Atomitas transaksi:Sakumpulan transaksi sing sukses utawa mundur.
• 2,stabilitas:Ana data ilegal (kendala kunci asing lan liya-liyane), lan transaksi kasebut ditarik.
• 3,Isolasi:Transaksi mlaku kanthi mandiri.Yen asil transaksi mengaruhi transaksi liyane, banjur transaksi liyane bakal ditarik.100% isolasi transaksi kanthi biaya kacepetan.
• 4,linuwih:Sawise kacilakan piranti lunak lan hardware, driver tabel data InnoDB bakal nggunakake file log kanggo mbangun maneh lan ngowahi.Reliabilitas lan kacepetan dhuwur ora bisa duwe loro, pilihan innodb_flush_log_at_trx_commit nemtokake nalika nyimpen transaksi menyang log.

Ing setelan gawan baris printah MySQL, transaksi kanthi otomatis dileksanakake, yaiku, operasi COMMIT bakal kaleksanan sanalika sawise statement SQL kaleksanan.Mulane, kanggo mbukak transaksi kanthi jelas kudu nggunakake printah BEGIN utawa START TRANSACTION, utawa nglakokake printah SET AUTOCOMMIT=0, kanggo mateni panggunaan komit otomatis kanggo sesi saiki.

Pernyataan kontrol transaksi:

• MULAI utawa MULAI TRANSAKSI; kanthi tegas miwiti transaksi;
• COMMIT; COMMIT WORK uga bisa digunakake, nanging loro-lorone padha. COMMIT nindakake transaksi lan nggawe kabeh modifikasi sing digawe kanggo database permanen;
• ROLLBACK; sampeyan bisa nggunakake ROLLBACK WORK, nanging loro-lorone padha.Rollback mungkasi transaksi pangguna lan mbatalake kabeh modifikasi sing ora ditindakake;
• Pengenal SAVEPOINT; SAVEPOINT ngidini nggawe titik nyimpen ing transaksi, lan bisa uga ana sawetara SAVEPOINT ing transaksi;
• RELEASE SAVEPOINT pengenal; mbusak savepoint saka transaksi, nalika ora ana titik nyimpen tartamtu, nglakokaké statement bakal mbuwang pangecualian;
• ROLLBACK TO pengenal; muter bali transaksi menyang titik;
• SET TRANSAKSI; digunakake kanggo nyetel tingkat isolasi transaksi. Tingkat isolasi transaksi sing diwenehake dening mesin panyimpenan InnoDB yaiku READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, lan SERIALIZABLE.

Ana rong cara utama pangolahan transaksi MYSQL:

1. Gunakake BEGIN, ROLLBACK, COMMIT kanggo entuk

• BEGIN miwiti transaksi
• GULUNG MULIH transaksi rollback
• KOMITAS Konfirmasi transaksi

2. Gunakake SET langsung kanggo ngganti mode komit otomatis MySQL:

• SETAUTOCOMMIT=0 Pateni auto-komit
• SETAUTOCOMMIT=1 Aktifake 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>

Nggunakake Instance Transaksi ing 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);
?>