Detailed explanation of MySQL transaction processing usage and example code

MySQL transaction support is not bound to the MySQL server itself, but is related to the storage engine.

1.MyISAM: does not support transactions and is used for read-only programs to improve performance
2. InnoDB: supports ACID transactions, row-level locks, and concurrency
3. Berkeley DB: Support for transactions

A transaction is a sequential set of database operations that are performed as if it were a single unit of work. In other words, there is never a complete transaction unless every individual operation within the group is successful. If any operation in a transaction fails, the entire transaction fails.
In practice, you will club many SQL queries into a group and execute all of them together as part of a transaction.

Characteristics of transactions:

Transactions have the following four standard properties, often referred to by the acronym ACID:

Atomicity: Ensure that all operations within a unit of work are completed successfully, otherwise the transaction will be aborted at the failure point, and previous operations will be rolled back to the previous state.

Consistency: Ensure that the database changes state correctly after a successful transaction.

Isolation: Makes transaction operations independent and transparent.

Durability: Ensuring that the results or effects of a committed transaction persist in the event of a system failure.

In MySQL, transactions start working and end using COMMIT or ROLLBACK statements. A large transaction is formed between the SQL commands that begin and end the statement.

COMMIT & ROLLBACK:

The two keywords commit and rollback are mainly used for MySQL transactions.

When a successful transaction is completed, issuing a COMMIT command should make the changes to all participating tables take effect.

If a failure occurs, a ROLLBACK command should be issued to return every table referenced in the transaction to its previous state.

You can control transaction behavior by setting a session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then every SQL statement (in a transaction or not) is considered a full transaction and is committed by default when it completes. When AUTOCOMMIT is set to 0, issuing the SET AUTOCOMMIT = 0 command, the subsequent series of statements acts like a transaction until an explicit COMMIT statement is issued, with no active commits.

These SQL commands can be executed in PHP by using the mysql_query() function.

Common transaction examples

This sequence of events is independent of the programming language used and can be built in any language used to create the logic path of the application.

These SQL commands can be executed in PHP by using the mysql_query() function.

BEGIN WORK starts a transaction and issues a SQL command

Issue one or more SQL commands such as SELECT, INSERT, UPDATE, or DELETE

Check if there are any errors and that everything is as per the need.

If there are any errors then issue ROLLBACK command, otherwise issue COMMIT command.

Transaction-safe table types in MySQL:

If you plan to use MySQL transactional programming, you need to create tables in a special way. There are many table types that support transactions but the most popular is InnoDB.

When compiling MySQL from source, InnoDB table support requires specific compilation parameters. If your MySQL version does not have InnoDB support, ask your Internet service provider to build a version of MySQL that supports the InnoDB table type, or download and install a MySQL-Max binary distribution for Windows or Linux/UNIX and use the table type in your development environment.

If your MySQL installation supports InnoDB tables, simply add a TYPE=InnoDB definition to the table creation statement. For example, the following code creates the InnoDB table tcount_tbl:

root@host#mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> create table tcount_tbl
 -> (
 -> tutorial_author varchar(40) NOT NULL,
 -> tutorial_count INT
 -> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

Other GEMINI or BDB table types can be used, but it depends on your installation if it supports both types.

Since the project design involves the transfer of money, MYSQL transaction processing is required to ensure the correctness of a set of processing results. When using transactions, it is inevitable to sacrifice some speed to ensure the correctness of the data.

Only InnoDB supports transactions

Transaction ACID Atomicity, Consistency, Isolation, Durability

1. Atomicity of transactions A group of transactions either succeeds or is withdrawn.

2. If there is illegal data (such as foreign key constraints), the transaction will be withdrawn.

3. Isolation transactions run independently.
If the result of a transaction affects other transactions, then the other transactions will be withdrawn.
100% isolation of transactions requires sacrificing speed.

4. Reliability: After a software or hardware crash, the InnoDB data table driver will use log files 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 transactions are saved to the log.

Open transaction

START TRANSACTION or BEGIN

Commit transaction (close transaction)

COMMIT

Abandon transaction (close transaction)

ROLLBACK

Turnaround Point

SAVEPOINT adqoo_1
ROLLBACK TO SAVEPOINT adqoo_1

Transactions occurring before the reentry point adqoo_1 are committed, and those occurring after adqoo_1 are ignored.

Termination of transaction

Setting the Autocommit mode
SET AUTOCOMMIT = 0
Each SQL statement is a different command of the same transaction, separated by COMMIT or ROLLBACK. After disconnection, all transactions without COMMIT are abandoned.

Transaction lock mode

System default: You do not need to wait for a transaction to end, and can query the results directly, but you cannot modify or delete them.
Disadvantage: The query results may be out of date.
Advantages: No need to wait for a transaction to end, the result can be queried directly.

The following mode is required to set the lock mode

1. SELECT ... LOCK IN SHARE MODE (shared lock)
The queried data is the data of the database at this moment (the results of other committed transactions have been reflected here)
SELECT must wait until a transaction ends before it can be executed

2. SELECT ... FOR UPDATE (exclusive lock)
For example, SELECT * FROM tablename WHERE id<200
Then the data with id < 200 and the queried data can no longer be modified, deleted, or SELECT ... LOCK IN SHARE MODE operations until the end of this transaction. The difference between shared locks and exclusive locks is whether to block SELECT ... LOCK IN SHARE MODE commands issued by other clients.

3. INSERT / UPDATE / DELETE
All associated data will be locked, plus an exclusive lock

4. Anti-insert lock <br /> For example, SELECT * FROM tablename WHERE id>200
Then the record with id>200 cannot be inserted

5. Deadlock <br /> Automatically identify deadlocks. The first process to come in is executed, and the later process receives an error message and rolls back in ROLLBACK mode.
innodb_lock_wait_timeout = n to set the maximum waiting time, the default is 50 seconds

Transaction isolation mode

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

1. The SET command without SESSION or GLOBAL is only valid for the next transaction

2. SET SESSION
Sets the isolation mode for the current session

3. SET GLOBAL
Set the isolation mode for all new MYSQL connections (not including the current connection)
Isolation Mode
READ UNCOMMITTED
Do not isolate SELECT
The results of unfinished modifications (not COMMIT) by other transactions are also taken into account
READ COMMITTED
Taking into account the COMMIT changes of other transactions, the same SELECT statement may return different results within the same transaction.
REPEATABLE READ (default)
Do not take into account changes made by other transactions. Regardless of whether other transactions have committed the same transaction using the COMMIT command, the same SELECT statement returns the same result (assuming that the transaction does not make changes).
SERIALIZABLE
Similar to REPEATABLE READ, shared locks are added to all SELECTs.

Error handling

Perform corresponding processing according to the error information

MySQL transaction processing example

There are two main methods for MYSQL transaction processing

1. Use begin, rollback, commit to implement

• beginStart a transaction
• Rollback transaction rollback
• commit transaction confirmation

2. Use set directly to change the automatic submission mode of MySQL

• MySQL is automatically submitted by default, that is, when you submit a query, it is executed directly! Can be
• set autocommit = 0 Disable automatic commit
• set autocommit = 1 Enable automatic commit

To implement transaction processing.

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

MYSQL only supports transaction processing for INNODB and BDB type data tables, other types are not supported!

mysql> use test;
 Database changed
 mysql> CREATE TABLE `dbtest`(
 -> id int(4)
 -> ) TYPE=INNODB;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> select * from dbtest
 -> ;
Empty set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dbtest values(5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into dbtest value(6);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dbtest;
+------+
|id|
+------+
| 5 |
| 6 |
+------+
2 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dbtest values(7);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dbtest;
+------+
|id|
+------+
| 5 |
| 6 |
+------+
2 rows in set (0.00 sec)

MySQL transaction processing

PHP code can implement transaction processing through the following methods of PHP predefined class mysqli.

• autocommit(boolean): This method is used to limit whether the query results are automatically submitted. If the parameter of this method is true, it is automatically submitted. If the parameter is false, automatic submission is turned off. The MySQL database is set to autocommit by default.
• rollback(): This method in the mysqli class can be used to roll back a transaction.
• commit(): This method can be used to commit all queries.

<?php
include_once("conn.php");

$id=$_GET[id];
$conn->autocommit(false);
if(!$conn->query("delete from tb_sco where id='".$id."'"))
{
 $conn->rollback();
}
if(!$conn->query("delete from tb_stu where id='".$id."'"))
{
 $conn->rollback();
}
 $conn->commit();
 $conn->autocommit(true);
 echo "ok"
?>

<?php
require('connectDB.php'); //Establish database connection mssql_query("BEGIN TRANSACTION DEPS02_DEL"); //Start transaction $delete_dep_sql="DELETE FROM TBLDEPARTMENT WHERE DEPTID='{$_GET[deptid]}'";
// echo $delete_dep_sql."<br>";
mssql_query($delete_dep_sql); //Operate database// var_dump($del_result);
$delete_result = mssql_query("select @@ROWCOUNT as id");
$delete_info = mssql_fetch_array($delete_result);
$delete_rows = $delete_info[0];
// var_dump($delete_rows);
mssql_free_result($delete_result);
echo "<script language=javascript>";
if(true){ //Judge whether to roll back the commit mssql_query("COMMIT TRANSACTION DEPS02_DEL"); //Commit the transaction echo "alert('delete success!');";
}else{
mssql_query("ROLLBACK TRANSACTION DEPS02_DEL"); //Rollback transaction echo "alert('delete faile!');";
}
echo "</script>";mssql_close();
?>

MySQL transaction processing has extensive and important applications in dealing with practical problems. The most common applications include bank transfer services, e-commerce payment services, etc. However, it is worth noting that MySQL's transaction processing function is not supported in the MYSIAM storage engine, but is supported in the InnoDB storage engine. Now I will upload a piece of code as a guide to the beginning of understanding MySQL transaction processing. It is a simple example, but it integrates ideas and I believe it will be of great help.

<?php
$conn = mysql_connect('localhost','root','yourpassword')or die(mysql_error());
mysql_select_db('transaction',$conn);
mysql_query('set names utf8');

//Create transactionmysql_query('START TRANSACTION') or die(mysql_error());
$sqlA="update A set account=account-1";
if(!mysql_query($sqlA)){
&nbsp;&nbsp;&nbsp; mysql_query('ROLLBACK') or exit(mysql_error());//Judge and roll back when the execution fails&nbsp; exit();
}
$sqlB="update B set account=account+1";
if(!mysql_query($sqlB)){
&nbsp;&nbsp;&nbsp; mysql_query('ROLLBACK') or exit(mysql_error());//Judge and roll back when the execution fails&nbsp; exit();
}
mysql_query('COMMIT')or die(mysql_error());//Execute transaction mysql_close($conn);
?>

The above code can be used as a transaction flow to simulate bank transfer business. Tables A and B represent two accounts opened in a bank. When account A transfers 1 yuan to account B, if the operation fails, the transfer will be rolled back to the original state and no further action will be performed. On the contrary, if the operation is executed successfully, the available balance of account B will increase by 1 yuan, otherwise the transaction will be rolled back to the original state.

I hope this article is helpful to you. This is the end of the introduction to MySQL transaction processing usage and example code. I hope everyone will continue to pay attention to our website! If you want to learn MySQL, you can continue to pay attention to this site.