Learning notes for MYSQL Transaction Processing

Transaction processing is a very good feature in mysql. For example, if you do not use Transaction Processing When deleting a large amount of data, some deletion failures may lead to incomplete data. If you use transaction processing, such problems will not occur, let's take a look at the usage of mysql transaction processing.

Mysql transaction processing steps:
1. Disable Automatic submission
2. Start Transaction Processing
3. If an exception exists, the system automatically throws the exception prompt and rolls back.
4. Enable automatic submission

Note: Only the InnoDB driver in mysql supports transaction processing. By default, the MyIsAM driver does not.

Because the transfer of money is involved in the project design, MYSQL transaction processing is required to ensure the correctness of a group of processing results. When transactions are used, it is inevitable to sacrifice a part of the speed to ensure data correctness.
Only InnoDB supports transactions

Transaction ACID Atomicity (Atomicity), Consistency (stability), Isolation (Isolation), Durability (reliability)

1. atomicity of transactions
A group of transactions are either successful or recalled.

2. Stability
If there is illegal data (foreign key constraints and so on), the transaction is recalled.

3. Isolation
Transactions run independently.
If the results of one transaction affect other transactions, other transactions will be recalled.
100% isolation of transactions requires a sacrifice of speed.

4. Reliability
After the software and hardware crash, the InnoDB data table driver uses the log file to reconstruct and modify the data table.
Reliability and high speed cannot both be achieved. The innodb_flush_log_at_trx_commit option determines when to save the transaction to the log.
Start transaction
Start transaction or BEGIN

Commit a transaction (close the transaction)
COMMIT

Discard transaction (close transaction)
ROLLBACK

Return Point
SAVEPOINT adqoo_1
Rollback to savepoint adqoo_1
Transactions that occur before the return point adqoo_1 are committed, and subsequent transactions are ignored.

Transaction termination

Set "automatic submission" Mode
Set autocommit = 0
Each SQL statement is a different command of the same transaction, separated by COMMIT or ROLLBACK.
After the disconnection, all transactions without COMMIT are abandoned.

Transaction lock mode

Default System: You can directly query the results without waiting for the end of a transaction, but cannot modify or delete the results.
Disadvantage: the query result may have expired.
Advantage: You can directly query the results without waiting for the end of a transaction.

Use the following mode to set the lock mode:

1. SELECT ...... Lock in share mode (shared LOCK)
The queried data is the data of the database at this time point (the results of other committed transactions have been reflected here)
The SELECT statement can only be executed after a transaction ends.

2. SELECT ...... For update (exclusive lock)
For example, SELECT * FROM tablename WHERE id <200
Then, no data with the id <200 and queried can be modified, deleted, or selected ...... Lock in share mode operation
Until the transaction ends

The difference between a shared lock and an exclusive lock is: whether to block the SELECT statements issued by other customers ...... Lock in share mode command

3. INSERT/UPDATE/DELETE
All associated data will be locked with the exclusive lock.

4. Anti-insert lock
For example, SELECT * FROM tablename WHERE id> 200
Therefore, records with IDs> 200 cannot be inserted.

5. deadlock
Automatic deadlock Identification
The advanced process is executed, the later process receives an error message, and rolls back in ROLLBACK mode.
Innodb_lock_wait_timeout = n to set the maximum wait time. The default value is 50 seconds.

Transaction Isolation Mode

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
Read uncommitted | read committed | repeatable read | SERIALIZABLE
1. SET commands without SESSION and GLOBAL commands
Valid only for the next transaction
2. SET SESSION
Set the Isolation Mode for the current session
3. SET GLOBAL
Set the Isolation Mode for all newly created MYSQL connections (the current connection is not included)

Isolation Mode

READ UNCOMMITTED
Do not isolate SELECT
The results of uncompleted modifications to other transactions (not COMMIT) are also taken into account.

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

Repeatable read (default)
No matter whether other transactions have been committed using the COMMIT command
In the same transaction, the same SELECT statement returns the same result (provided that this transaction is not modified)

SERIALIZABLE
Similar to repeatable read, shared locks are applied to all SELECT statements.

Error Handling

Perform corresponding processing based on error information

 

Transaction processing is widely used in various management systems. For example, in the personnel management system, many synchronous database operations require transaction processing. For example, in the personnel management system, if you delete a person, you need to delete the basic information of the person and the information related to the person, such as the mailbox and articles, 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 no transaction is processed, if an error occurs during the deletion process and only the first sentence is executed, the consequences are unimaginable!
But it is processed by transactions. If a deletion error occurs, you only need to roll back to cancel the deletion operation (in fact, as long as you do not have a commit, you have not actually performed the deletion operation)

Generally, transaction processing must be taken into account in business-level applications!

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, we can create a table that supports transaction processing.

MYSQL transaction processing function!
I have always thought that MYSQL does not support transaction processing, so it is always troublesome to process data from multiple data tables (I have to write it into a text file, write data to the database only when the system is reloaded to prevent errors )~ Today, we found that MYSQL Databases have supported the transaction function since 4.1, and 5.0 introduced the Stored Procedure pai_^.

Let's briefly introduce the transaction! A transaction is the unit of execution of a DBMS. It consists of a finite sequence of database operations. But not any database operation sequence can become a transaction.

Generally, a transaction must meet four conditions (ACID ):

Atomicity (Autmic): when a transaction is executed, "Do not do it, or do it all !", That is to say, the Department is not allowed to perform tasks separately. Even if the transaction cannot be completed due to a fault, the impact on the database should be eliminated during rollback!
Consistency: the transaction operation should change the database from a consistent state to another consistent state! Take online shopping for example. You only need to let the goods go out of stock and let the goods enter the shopping basket of the customer to form a transaction!
Isolation: if multiple transactions are executed concurrently, it should be the same as the independent execution of each transaction!
Durability: A successfully executed transaction has a lasting effect on the database. Even if the database fails or fails, the transaction can be recovered!
There are two main methods to process MYSQL transactions:

Use begin, rollback, and commit to implement:
Start a transaction
Rollback transaction rollback
Commit transaction validation
Directly use set to change the automatic mysql submission mode:
MYSQL is automatically submitted by default, that is, when you submit a QUERY, It will be executed directly! We can use

Set autocommit = 0 disable automatic submission
Set autocommit = 1 enable automatic submission
Note that when you use set autocommit = 0, all your SQL statements will be processed as transactions until you use commit to confirm or roll back, note that a new transaction is also started when you end the transaction! In the first method, only the current transaction is used!
The first method is recommended!
In MYSQL, only INNODB and BDB Data Tables 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: rollback

The Code is as follows:

Copy code

<? Php $ Handler = mysql_connect ('localhost', 'root ',''); Mysql_select_db ('task '); Mysql_query ('set AUTOCOMMIT = 0'); // It is SET to not automatically submitted because MYSQL runs immediately by default. Mysql_query ('begin'); // start transaction Definition If (! Mysql_query ('insert INTO 'Trans '('id') VALUES (2 );')) { Mysql_query ('roolback'); // determines whether to roll back When execution fails. } If (! Mysql_query ('insert INTO 'Trans '('id') VALUES (4 );')) { Mysql_query ('roolback'); // determines whether the execution fails to be rolled back. } Mysql_query ('commit '); // executes the transaction Mysql_close ($ handler ); ?>