Learning notes for MySQL transaction processing

Several steps of MySQL transaction processing:
1. Turn off automatic submission
2. Open transaction Processing
3. The exception will automatically throw the exception prompt and roll back
4. Turn on automatic submission

Note: MySQL has only this innodb driver that supports transaction processing and is not supported by the default MyISAM driver.

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

Transaction processing in various management systems have a wide range of applications, such as personnel management system, many synchronous database operations need to use transactions. For example, in a personnel management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailboxes, articles and so on, so that these database operation statements constitute a transaction!
Deleted SQL statement

The code is as follows	Copy Code
Delete from userinfo where ~ ~ ~ Delete from Mail where ~ ~ Delete from article where~~

~ ~ If there is no transaction processing, in the process of your deletion, the assumption of error, only to execute the first sentence, then its consequences is unimaginable!
But with transactions. If the deletion is wrong, you can cancel the deletion as long as you rollback (in fact, as long as you do not commit you do not actually perform the delete operation)

Generally speaking, in the business class application, must consider the transaction processing!

View INODB Information

The code is as follows

Copy Code

Shell>/usr/local/mysql-u Root-p Mysql> Show variables like "have_%" The system will prompt: +-------------------+--------+ | variable_name | Value | +-------------------+--------+ | Have_bdb | YES | | Have_crypt | YES | | Have_innodb | YES | | Have_isam | YES | | Have_raid | YES | | Have_symlink | YES | | Have_openssl | NO | | Have_query_cache | YES | +-------------------+--------+ 8 rows in Set (0.05 sec)

If so, then we can create a table that supports transactional processing to try it out.

MySQL Transaction processing Function!
I've always thought MySQL doesn't support transactions, so it's always a hassle to work with data from multiple data tables (I had to write it to a text file, Write to database in case of error when the system reloads) ~ Today we found that the MySQL database supports transactional functionality from 4.1, and 5.0 introduces stored procedures ^_^

First of all, a brief introduction to business! A transaction is an executing unit of a DBMS. It consists of a limited number of database operation sequences. But not any database operation sequence can become a transaction.

In general, a transaction is required to meet 4 conditions (ACID):

Atomicity (autmic): Business in the execution, to do "either not to do, or all to do!" "is to say that the part of the transaction is not allowed to execute. Even if the transaction can not be completed because of the failure, in rollback to eliminate the impact on the database!
Consistency (consistency): Transaction operations should cause the database to transition from a consistent state to another consistent state! Take online shopping For example, you only let the goods out of the library, and let the goods into the customer's shopping basket can constitute a business!
Isolation (Isolation): If more than one transaction executes concurrently, it should be performed as if it were executed independently of each transaction!
Persistence (Durability): A successful implementation of the transaction to the database is a lasting effect, even if the database should be faulty, should be able to recover!
There are two main ways to handle MySQL transactions:

To implement with Begin, rollback, or commit:
Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Confirmation
Use set directly to change the automatic submission mode of MySQL:
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
But note that when you use set autocommit=0, all of your later SQL will be transacted, until you end with a commit or rollback, and notice that when you end the transaction, you start a new transaction! The first method will only present as a transaction!
The first method of personal recommendation!
Only InnoDB and BDB types of data tables in MySQL can support transaction processing! Other types are not supported! Remember ）

Test:
SQL code:

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 ' VALUES (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>

PHP Functions:

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; } }

PHP: Rolling back

code is as follows

copy code

<?php $handler = mysql_connect (' localhost ', ' root ', '); mysql_select_db (' Task '); mysql_query (' Set autocommit=0 ');   //set to not autocommit because MySQL defaults to execute immediately mysql_query (' BEGIN ');               //Start transaction definition   if (!mysql_ Query (' INSERT into ' trans ' (' ID ') VALUES (2); ') {     mysql_query (' roolback ');       //Judge rollback } when execution fails   if (!mysql_query (' INSERT into ' trans ' (' ID ') VALUES (4); ') {     mysql_query (' roolback ');       //Judgement execution failure rollback }   mysql_query (' COMMIT ');              //Executive Affairs Mysql_close ($handler); ?