MySQL Transaction usage and examples

Source: The transaction support reproduced in MySQL is not tied to the MySQL server itself, but is related to the storage engine 1. MyISAM: Transaction not supported, for read-only program lift high performance 2.InnoDB: Acid transaction, row level lock, concurrent 3.Berkeley DB: Support Transaction A transaction is a contiguous set of database operations, as if it were a single unit of work. In other words, it will never be a complete transaction unless each individual operation within that group is successful. If any operation on the transaction fails, the entire transaction fails.

In fact, club many SQL queries into a group that will execute all the people together as part of the transaction.

Features of the transaction:
Transactions have the following four standard attributes of the abbreviated acid, commonly referred to as:

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

Consistency: Ensure that the database has successfully committed a transaction after it has changed state correctly.

Isolation: Makes transactional operations independent and transparent to each other.

Persistence: Ensures that the result of the committed transaction or the effect of the system is still present in the event of a failure.

In MySQL, the transaction begins working and ending with a commit or ROLLBACK statement. A large number of transactions are formed between the start and end statements of the SQL command.

COMMIT & ROLLBACK:
These two keywords commit and rollback transactions that are primarily used for MySQL.

When a successful transaction is completed, issuing a commit command should make changes to all participating tables effective.

In the event of a failure, you should issue a rollback command to return each of the tables referenced in the transaction to the previous state.

The transaction behavior that can be controlled is called a autocommit set session variable. If Autocommit is set to 1 (the default), then each SQL statement (either in the transaction or not) is considered to be a complete transaction and is committed by default when it is complete. When Autocommit is set to 0 o'clock, the set autocommit = 0 command is issued, and the subsequent sequence of statements acts like a transaction until an explicit commit statement is made, and there is no active commit.

You can execute these SQL commands in PHP by using the mysql_query () function.

Common transaction Examples
This series of events is independent of the programming language used and can be built in any language used to create the logical path of the application.
You can execute these SQL commands in PHP by using the mysql_query () function.

Begin work start transaction issue SQL command

Issue one or more SQL commands, such as select,insert,update or delete

Check if there are any errors, everything is based on the need.

If there are any errors, then the issue rollback command, otherwise a commit command is issued.

Transaction security table type in MySQL:

If you plan to use MySQL transaction programming, then you need a special way to create the table. There are many support transactions but the most popular is the InnoDB table type.

When compiling MySQL from source code, the InnoDB table supports the need for specific compilation parameters. If the MySQL version does not have INNODB support, please Internet service provider build a version of MySQL support InnoDB table type, or download and install Windows or Linux/unix Mysql-max binary distribution and use the table type in the development environment.
If the MySQL installation supports the InnoDB table, simply add one of the TYPE=INNODB definition table creation statements. For example, the following code creates a InnoDB table tcount_tbl:

[Email protected] # mysql-u root-p password; Enter password:*******mysql use tutorials;database changedmysql> Create Table Tcount_tbl     -(    NULL,     tutorial_count  INT    ) type=InnoDB; Query OK, 0 rows affected (0.05 sec)

You can use a different Gemini or BDB table type, but it depends on your installation if it supports both types.

Because of the project design, involved in the transfer of money, it is necessary to use the MySQL transaction processing, to ensure the correctness of a set of processing results. With the transaction, it is inevitable to sacrifice part of the speed to ensure the correctness of the data.
Only InnoDB support transactions

Transaction ACID atomicity (atomicity), consistency (stability), isolation (isolation), durability (reliability)

1, the atomicity of the transaction
A set of transactions, either successful or withdrawn.

2. Stability
There are illegal data (foreign key constraints, and so on), transaction recall.

3, the isolation of
The transaction runs independently.
The result of one transaction affects other transactions, and then other transactions are recalled.
The 100% isolation of a transaction requires a sacrifice of speed.

4. Reliability
After the hardware and software crashes, the INNODB data table driver uses the log file to refactor the changes.
Reliability and high speed cannot be combined, the INNODB_FLUSH_LOG_AT_TRX_COMMIT option determines when the transaction is saved to the log.
Open transaction
START TRANSACTION or BEGIN

Commit TRANSACTION (Close transaction)
COMMIT

Discard transaction (Close transaction)
ROLLBACK

Reentry point
SavePoint Adqoo_1
ROLLBACK to SavePoint Adqoo_1
Transactions that occurred before the Adqoo_1 were committed, followed by the ignored

Termination of a transaction

Set auto-commit mode
SET autocommit = 0
Each SQL is a different command for the same transaction, separated by COMMIT or rollback
After the line is dropped, no COMMIT transactions are discarded.

Transaction lock mode

System default: No need to wait for a transaction to end, can directly query the results, but can not be modified, deleted.
Disadvantage: The result of the query may have expired.
Pros: You don't have to wait for a transaction to end, you can query the results directly.

You need to use the following mode to set the lock mode

1. SELECT ... Lock in SHARE MODE (shared Lock)
The data that is queried is the data of the database at this point (the result of other commit transactions has been reflected here)
SELECT must wait for the end of a transaction to execute

2. SELECT ... For UPDATE (exclusive lock)
For example SELECT * FROM TableName WHERE id<200
So id<200 data, the data that is queried, will no longer be modified, deleted, SELECT ... LOCK in SHARE mode operation
Until the end of the transaction.

The difference between shared and exclusive locks: whether to block SELECT from other customers ... LOCK in SHARE mode command

3, Insert/update/delete
All associated data will be locked, plus an exclusive lock

4, anti-insert lock
For example SELECT * FROM TableName WHERE id>200
Then the id>200 record cannot be inserted.

5. Deadlock
Automatic identification of deadlocks
The advanced process is executed, subsequent processes receive an error message and are rolled back by rollback
Innodb_lock_wait_timeout = N to set the maximum wait time, default is 50 seconds

Transaction isolation mode

SET [session| GLOBAL] TRANSACTION Isolation Level
READ Uncommitted | READ COMMITTED | Repeatable READ | SERIALIZABLE
1. Set command without session and global
Valid only for the next transaction
2. SET SESSION
To set isolation mode for the current session
3. SET GLOBAL
Set isolation mode for all new MySQL connections that are created later (the current connection is not included)

Isolation mode

READ UNCOMMITTED
Do not isolate Select
Other transaction incomplete modifications (not commit), the results are also taken into account

READ COMMITTED
Take into account the COMMIT modification of other transactions
In the same transaction, the same SELECT may return different results

Repeatable READ (default)
Do not take into account changes to other transactions, regardless of whether other transactions have been submitted with a commit command
In the same transaction, the same SELECT returns the same result (provided this transaction is not modified)

SERIALIZABLE
Similar to repeatable read, a shared lock is added to all the Select

Error handling
Perform the appropriate processing based on the error message

MySQL Things processing example

There are two main ways to handle MySQL's transactions
1. Using Begin,rollback,commit to achieve
Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Acknowledgement
2. Use set directly to change the MySQL auto-commit mode
MySQL default is automatically submitted, that is, you submit a query, directly execute! can be done by
Set autocommit = 0 Disables auto-commit
Set autocommit = 1 turn on auto commit
To implement transaction processing.
Note, however, that when you use set autocommit = 0, all of your future SQL will be transacted until you end it with commit confirmation or rollback, and notice that when you end the transaction, you also open a new transaction! The first way is to only do the current as a transaction!
MySQL only InnoDB and BDB types of data tables support transaction processing, the other types are not supported!
MYSQL5.0 winxp under test through ~ ^_^

MySQL> Usetest; Database changedMySQL>CREATE TABLE ' dbtest ' (ID Int (4)    ) type=INNODB; Query OK, 0 rows affected, 1 warning (0.05sec)MySQL> select *From dbtest- ;EmptySet (0.01sec)MySQL>begin; Query OK, 0 rows affected (0.00sec)MySQL> INSERT into dbtest values (5); Query OK, 1 row affected (0.00sec)MySQL> INSERT into Dbtest value (6); Query OK, 1 row affected (0.00sec)MySQL>commit; Query OK, 0 rows affected (0.00sec)MySQL> select *From dbtest;+------+|    ID |+------+|    5 | | 6 |+------+2 rows in Set (0.00sec)MySQL>begin; Query OK, 0 rows affected (0.00sec)MySQL> INSERT into dbtest values (7); Query OK, 1 row affected (0.00sec)MySQL>rollback; Query OK, 0 rows affected (0.00sec)MySQL> select *From dbtest;+------+|    ID |+------+|    5 | | 6 |+------+2 rows in Set (0.00 sec)

mysql> MySQL Transaction processing

The processing of the PHP code implements the transaction can be implemented by the following methods of PHP predefined class mysqli.
Autocommit (Boolean): This method is used to limit whether the query results are automatically committed if the parameter of the method is true and autocommit is turned off if the argument is false. The MySQL database is automatically committed by default.
Rollback (): The rollback of a transaction can be achieved using this method in the Mysqli class.
Commit (): Use this method to commit all queries.

<?PHPinclude_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"?>

<?PHPrequire(' connectdb.php ');//establishing a database connectionMssql_query ("BEGIN TRANSACTION Deps02_del");//Start a transaction$delete _dep_sql= "DELETE from tbldepartment WHERE deptid= ' {$_get[DeptID]} ' ";//echo $delete _dep_sql. " <br> ";Mssql_query ($delete _dep_sql);//Operational 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){//determine whether to roll back the commitMssql_query ("COMMIT TRANSACTION Deps02_del");//Commit a transactionEcho"Alert (' delete success! ');";}Else{mssql_query ("ROLLBACK TRANSACTION Deps02_del");//rolling back a transactionEcho"Alert (' delete faile! ');";}Echo"</script>"; Mssql_close ();?>

Example 3

MySQL transaction processing has a wide and important application in dealing with practical problems, the most common applications are bank transfer business, e-commerce payment business and so on. However, it is worth noting that MySQL's transactional capabilities are not supported in the Mysiam storage engine and are supported in the INNODB storage engine. Now upload a piece of code, as a guide to understand the beginning of MySQL transaction processing, simple examples, but the fusion of ideas, I believe there will be a 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 ') orExit(Mysql_error());//determine rollback when execution fails&nbsp;Exit();}$sqlB= "Update B set account=account+1";if(!mysql_query($sqlB)){&nbsp;&nbsp;&nbsp;mysql_query(' ROLLBACK ') orExit(Mysql_error());//determine rollback when execution fails&nbsp;Exit();}mysql_query(' COMMIT ') or die(Mysql_error());//Execution TransactionsMysql_close($conn);?>

The above code can be used as a transactional process for simulating bank transfer operations. The table A and B represent two accounts that have been opened at the bank, and when account a performs an operation that transfers 1 yuan to account B, if the operation fails, the roll-out will be rolled back to its original state without continuing to perform the action down. Conversely, if the operation succeeds, the account B Available balance will be increased by $1 or the transaction is rolled back to its original state.

MySQL Transaction usage and examples