Mysql transaction processing study notes

What is a transaction?

In the MySQL environment, a transaction is composed of one or more SQL statements as a separate unit. Each SQL statement in this unit is mutually dependent, and the unit as a whole is inseparable. If a statement in a unit cannot be completed successfully, the entire unit will be rolled back, and all affected data will be returned to the status before the start of the transaction. Therefore, the transaction is successfully executed only when all statements in the transaction are successfully executed.

Four Features of transactions:

Atomicity, each transaction must be considered an inseparable unit.

Consistency: whether the transaction is completely successful or fails midway through, there is consistency when the transaction makes the system consistent.

Isolation: each transaction occurs in its own space and is isolated from other transactions in the system. The transaction result can be seen only when it is fully executed.

Persistence, even if the system crashes, a committed transaction is thrown at persistence.

Lifecycle

To initialize a transaction and tell MySQL that all subsequent SQL statements need to be considered as a unit, MySQL provides the start transaction command to mark the start of a transaction. You can also use the begin or begin work Command to initialize a transaction. Generally, the start transction command follows the SQL statement that constitutes the transaction.

Once the SQL statement is executed, you can use the commit command to save the entire transaction on the disk, or use the rollback command to cancel all changes. If the transaction includes changes in the transaction table and non-transaction table, the transaction processing part of the non-transaction table cannot be undone using the rollback command. In this case, MySQL will return an error notifying you of an incomplete revocation.

The commit command indicates the end of the transaction block.

Control transaction behavior

MySQL provides two variables to control transaction behavior: the autocommit variable and the transaction isolation level variable.

Automatic submission. By default, once a MySQL SQL query is executed, the results are automatically submitted to the database. This default behavior can be modified using specific autocommit variables. Set autocommit = 0. Subsequent table updates will not be saved until a commit command is explicitly issued.

Transaction isolation level. MySQL uses repeatable read isolation level by default. You can use set to modify

Transactions and performance

Because the databases supporting transactions are more difficult than non-transaction databases to keep different users isolated from each other, the system performance is naturally reflected.

We need to do something to ensure that the transaction will not add an inappropriate burden to the system.

Two general strategies for using small transactions

1: ensure that all required user input is feasible before the start transaction command is issued

2: try to divide large transactions into small transactions and execute them separately.

Select the appropriate isolation level. The higher the isolation level, the lower the performance. Therefore, selecting the appropriate isolation level helps to optimize the performance.

Avoid deadlocks. In a transaction environment, a deadlock occurs when two or more customers in different sequences want to update the same data at the same time. We should avoid deadlocks.

Example

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
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
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!

Author: Feifengxlq Email: feifengxlq@sohu.com
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 MYSQL 4.1. It is said that MYSQL 5.0 will introduce 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, transactions 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.
1. Use begin, rollback, and commit to implement
Start a transaction
Rollback transaction rollback
Commit transaction validation
2. Directly use set to change the mysql automatic 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
To process the transaction.
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 !)

Next time, let's talk about how to lock and unlock MYSQL data tables!

MYSQL5.0 WINXP passed the test ~ Pai_^

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 value (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>

**************************************** **************************************** ***********************************

[PHP]
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]

**************************************** ********

<? Php
$ Handler = mysql_connect ("localhost", "root ","");
Mysql_select_db ("task ");
Mysql_query ("set autocommit = 0"); // SET to not automatically submit, 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 );
?>

Mysql transaction processing problems

Transaction processing is to treat a series of operations as an atomic operation, or all the operations are successfully executed. If the execution fails, the execution period is retained. Operations are performed by submitting and rolling back the machine. If all the operations are successfully performed, the results of the committed operations are recorded in the database, if the execution fails, roll back all the errors before the error occurs and roll back to the original status.

Transactions should all have ACID features. ACID is the first letter of four words: Atomic (atomicity), Consistent (consistency), Isolated (isolation), and Durable (continuity, the following uses bank transfers as an example to describe their meanings:

Atomicity: The statements that make up transaction processing form a logical unit and cannot only execute a part of it. In other words, transactions are the smallest units that are inseparable. For example, in the bank transfer process, it is unreasonable to change only one account by subtracting the transfer amount from one account and adding it to another account.

Consistency: The database is consistent before and after transaction processing. That is to say, the transaction should correctly switch the system status. For example, in the bank transfer process, either the transfer amount is transferred from one account to another, or both accounts remain unchanged.

Isolation: one transaction has no impact on the processing of another transaction. That is to say, no transaction can see a transaction in an incomplete state. For example, in the bank transfer process, another transfer transaction can only be in the waiting state before the transfer transaction is committed.

Continuity: The effect of transaction processing can be permanently saved. Conversely, transactions should be able to withstand all failures, including server, process, communication, and media failures. For example, in the bank transfer process, the account status must be saved after the transfer.

Note: in the storage engine supported by Mysql, the default value is MyISAM, which does not support transaction processing. Generally, InnoDB is available and supports transaction type.

(1) If transaction support is required for operations on a table, you need to configure the storage engine to InnoDB and other support transaction-type.

Create table XX () engine = InnoDB;

(2) by default, mysql uses the autocommit mode (autocommit = 1). After each statement is executed, the modifications are submitted immediately. At this time, the commit mode is useless, rollback only applies to the previous statement. In fact, it is useless. A mysql statement is also an atomic operation by default and is unnecessary.

If you set the default transaction processing, you need to disable the automatic commit mode to set autocommit to 0.

Set autocommit = 0; set mode to disabled

Select @ autocommit; check whether the value has changed

Note: if the settings are completed on the client, disconnect the connection and re-connect again. Each client can only set its own.

(3) If the automatic submission mode is enabled, use the following statement:

Start transaction; start transaction processing

XX1;

XX2;

Commit;/rollback;

To start transaction processing. If it is set to disabled, you do not need to use start transaction, and the continuous statement guides the transaction to rollback or commit.

(4) note that creating, changing, or deleting a database, or the data definition language and lock related to the database cannot be part of the transaction, as shown below:

Import MySQLdb
 
Try:
Conn = MySQLdb. connect (host = "localhost", user = "root", passwd = "your passwd", db = "dbName ")
Counter T MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])
 
Else:
Pass # conn. close ()

Html "name =" code "> execute a transaction. When a table is to be created, mysql automatically submits the statement and then runs the statement. If I of test1 is the primary key, the third statement fails. When the rollback is performed, test1 is still inserted successfully and the table test2.

(5) it is best to use this form to use databases in python,

Try:
Cur = conn. cursor ()
Cur.exe cute ('set autocommit = 0') export cur.exe cute ('Start transaction ')
Cur.exe cute ('Insert into test1 values ("8 ")')
Cur.exe cute ('Insert into test1 values ("8 ")')
 
Counter T MySQLdb. Error, e:
Conn. rollback ()
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])
 
Else:
Conn. commit ()
Cur. close ()
Conn. close ()
(6) parallel processing

Mysql is a multi-user system with multiple users accessing a unified data table at the same time. MySIAM uses a data table-level lock mark to ensure that only one user accesses the table at the same time; innodb uses a row-level data access mechanism, that is, two users can modify the data of different rows in the same table at the same time. If the data is the same row, the first user locks the row first, the next user can release the lock after the operation ends.

(7) isolation of transaction processing

The default isolation level of InnoDB is repeatable read. If a user executes the same select statement twice, the results can be repeated, if a user performs operations on the data to be read during the transaction, the data will not be displayed, for example, a table with the storage engine of innodb, if one customer uses a transaction to read table data in the select statement, another user performs an insert operation on the table at this time. When the first user reads the same select statement, the data displayed is unchanged.

(8) multi-statement operations non-atomic operations

For example, there will be a problem in the above (6). If it is a transaction operation, you want to operate the data after reading the data, but another person may have done this operation, then the operation on the data is incorrect.

In this case, you need to explicitly lock the table to prevent others from changing the data and release the lock after the execution ends.

Lock tables XX write;

XXXXXX;

Unlock tables;

You can also use relative update instead of absolute update to update relative to the current value, instead of calculating an absolute value based on the previous value. This avoids non-atomic operations on multiple statements.

Set a = a-3 XXXXXXXXXXX;