MySQL transaction processing usage and example detailed explanation

A transaction is a continuous 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 in the transaction fails, the entire transaction fails.

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

Attributes of the transaction:
Transactions have abbreviated acid for the following four standard properties, commonly referred to as:

Atomicity: Ensure that all operations within the work unit 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: A transaction that is successfully committed after the database has been correctly changed to state.

Isolation: Enables transactions to operate independently and transparently from one another.

Persistence: Ensures that the results or effects of a committed transaction still exist in the event of a system failure.

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

COMMIT & ROLLBACK:
These two keyword submissions and rollbacks are primarily used for MySQL transactions.

When a successful transaction completes, the commit command should cause all participating table changes to take effect.

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

The transaction behavior that can be controlled is called the autocommit set session variable. If Autocommit is set to 1 (the default), then each SQL statement (in transaction or not) is considered a complete transaction and is committed by default when it is completed. 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 a definite commit statement is made, and no activity is committed.

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

Examples of common transactions
This sequence 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 needs.

If there are any errors, then the problem rollback the command, or a commit is issued.

Transaction security table types in MySQL:

If you plan to use MySQL transaction programming, you need a special way to create a 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, ask the Internet service provider to establish a version of MySQL support InnoDB table type, or download and install Windows or Linux/unix Mysql-max binary distribution and use the table types in the development environment.
If the MySQL installation supports InnoDB tables, simply add a TYPE=INNODB definition table creation statement. For example, the following code creates a InnoDB table tcount_tbl:

The code is as follows	Copy Code
root@host# mysql-u root-p password; Enter password:******* mysql> use tutorials; Database changed Mysql> CREATE TABLE Tcount_tbl -> ( -> tutorial_author varchar () not NULL, -> Tutorial_count INT ->) Type=innodb; Query OK, 0 rows affected (0.05 sec)

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

Because of the project design, involved in the transfer of money, so we need to use MySQL transaction processing, to ensure that a group of processing results of correctness. Using the transaction, it is inevitable to sacrifice a 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 atomic nature of the transaction
A set of transactions, either successful or withdrawn.

2, stability
Illegal data (foreign KEY constraint, etc.), transaction withdrawn.

3, the isolation of
Transactions run independently.
The results of one transaction affect other transactions, then other transactions are withdrawn.
The 100% isolation of the transaction requires sacrificing speed.

4. Reliability
After the hardware and software crashes, the INNODB data-table driver uses the log file refactoring modifications.
Reliability and high speed can not be both, 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 of the exhumation point were committed and then ignored

Termination of a transaction

Set autocommit mode
SET autocommit = 0
Each SQL is a different command of the same transaction, separated by a COMMIT or rollback
After the line is dropped, no COMMIT transaction is abandoned.

Transaction lockout mode

System defaults: No need to wait for a transaction to end, you can directly query the results, but can not be modified, deleted.
Disadvantage: The results of the query may have expired.
Advantages: No need to wait for the end of a transaction, you can directly query the results.

You need to set the lock mode in the following mode

1. SELECT ... Lock in SHARE MODE (shared Lock)
The data that is queried is the data of the database at this time (the results of other commit transactions have been reflected here)
The SELECT must wait for a transaction to be completed before it can be executed

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

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

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

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

5, Dead Lock
Automatically recognize deadlocks
The advanced process is executed, and subsequent processes receive an error message and are rolled back in a rollback manner
Innodb_lock_wait_timeout = N to set maximum wait time, default is 50 seconds

Transaction isolation mode

SET [session| GLOBAL] TRANSACTION Isolation Level
READ Uncommitted | READ Committed | Repeatable READ | SERIALIZABLE
1, without session, Global SET command
Valid for next transaction only
2, SET session
Set isolation mode for the current session
3, SET GLOBAL
Set quarantine mode for all future new MySQL connections (not included in the current connection)

Isolation mode

READ UNCOMMITTED
Do not isolate Select
Incomplete modifications to other transactions (not commit) and the results are taken into account

READ committed
Take into account the other transaction's COMMIT modification
The same SELECT may return different results in the same transaction

Repeatable READ (default)
Does not take into account changes in other matters, whether or not other transactions have been submitted with a commit order
In the same transaction, the same SELECT returns the same result (provided this transaction is not modified)

SERIALIZABLE
Like Repeatable read, a shared lock was added to all the Select

Error handling
Perform the appropriate processing according to the error message

MySQL thing processing example

There are two main ways to handle MySQL transactions
1. Using Begin,rollback,commit to achieve
Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Confirmation
2. Directly use set to change the automatic submission mode of MySQL
MySQL default is automatically submitted, that is, you submit a query, directly executed! Can pass
Set autocommit = 0 Prohibit autocommit
Set autocommit = 1 turn on autocommit
to implement transaction processing.
But note that when you use set autocommit = 0, all of your later SQL will be handled as transactions until you end with a commit or rollback, and notice that when you end the transaction, you start a new transaction! In the first method, only the current is done as a transaction!
MySQL only InnoDB and BDB types of data tables to support transaction processing, the other types are not supported!
MYSQL5.0 winxp under test pass ~ ^_^

The code is as follows

Copy Code

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> MySQL Transaction processing

The processing of the PHP code implementation transaction can be achieved through the following methods of the PHP predefined class mysqli.
autocommit (Boolean): This method is used to qualify whether the query results are automatically submitted, or to automatically commit if the method's argument is true, or to turn off autocommit if the argument is false. The MySQL database defaults to autocommit.
Rollback (): Use this method in the Mysqli class to implement a rollback of a transaction.
Commit (): This method enables you to submit all queries.

The code is as follows	Copy Code
<?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" ?>

Column Two

code is as follows

copy code

<?php Require (' connectdb.php '); Establish a 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);//Operations 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) {   //To determine whether to roll back the commit Mssql_query ("Commit TRANSACTION Deps02_del");//COMMIT TRANSACTION Echo alert (' Delete success! '); "; }else{ Mssql_query ("ROLLBACK TRANSACTION Deps02_del");//ROLLBACK TRANSACTION Echo 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 such as bank transfer business, E-commerce payment business and so on. However, it is noteworthy that MySQL's transaction processing 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 to integrate ideas, I believe there will be a great help.

The code is as follows

Copy Code

<?php $conn =mysql_connect (' localhost ', ' root ', ' yourpassword ') or Die (Mysql_error ()); mysql_select_db (' transaction ', $conn); mysql_query (' Set names UTF8 '); //Create Transaction mysql_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 ()); To determine rollback of &nbsp;   exit () when execution fails (); $sqlB = "Update B set account=account+1"; if (!mysql_query ($sqlB)) { &nbsp;&nbsp;&nbsp; mysql_query (' ROLLBACK ') or exit (Mysql_error ()); To determine rollback of &nbsp;   exit () when execution fails (); mysql_query (' COMMIT ') or Die (Mysql_error ());/Execute transaction Mysql_close ($conn); ?

The

  above code can be used as a transaction flow for simulating bank transfer operations. In Table A, B, respectively, two accounts have been opened in the bank, when account a performs a transfer of 1 yuan to account B, if the operation fails, the turn out will be rolled back to the original state, do not continue to execute the action. Conversely, if the operation succeeds, account B balances will be increased by 1 yuan, otherwise the transaction rolls back to its original state.