Description of usage of MySQL transaction processing

There are two main ways to handle MySQL transactions.

1, with Begin,rollback,commit to achieve

Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Confirmation

2, directly with the set to change the MySQL automatic submission mode

MySQL default is automatically submitted, that is, you submit a query, it directly executed! We can pass
Set autocommit=0 prohibit automatic submission
Set autocommit=1 turn on automatic submission
To implement transaction processing.
When you use Set autocommit=0, all of your later SQL will be transacted until you end with a commit or rollback.
Note that when you end this business, you start a new business! The first method will only present as a transaction!

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

General MySQL database default engine is MyISAM, this engine does not support transactions! If you want MySQL to support transactions, you can modify them manually:
The method is as follows: 1. Modify the C:/appserv/mysql/my.ini file, find the Skip-innodb, precede it with #, and save the file.
2. In operation input: services.msc, restart the MySQL service.

3. In phpMyAdmin, mysql->show engines; (or perform mysql->show variables like ' have_% '; To see InnoDB as yes, which means that the database supports InnoDB.

It also means that support services are transaction.

4. When you create a table, you can select the InnoDB engine for storage engine. If you have previously created a table, you can use the Mysql->alter table table_name Type=innodb;

or Mysql->alter table table_name Engine=innodb to change the engine of the datasheet to support transactions.

Principle

The code is as follows	Copy Code
function Tran ($sql) { $judge = 1; mysql_query (' begin '); foreach ($sql as $v) { if (!mysql_query ($v)) { $judge = 0; } } if ($judge = = 0) { mysql_query (' rollback '); return false; } ElseIf ($judge = = 1) { mysql_query (' commit '); return true; } }

Cases

code is as follows

copy code

<?php $handler =mysql_connect ("localhost", "root", ""); mysql_select_db ("task"); mysql_query ("Set autocommit=0");//set to not commit automatically because MySQL executes immediately by default mysql_query ("Begin");/start a transaction definition if (!mysql_ Query (insert into trans (ID) VALUES (' 2 ')) { mysql_query ("Roolback");/to determine rollback of } If execution fails (!mysql_query INSERT into trans (ID) VALUES (' 4 ')) { mysql_query ("Roolback");/Judgment execution failure rollback } mysql_query ("COMMIT");// Execute transaction Mysql_close ($handler);