MYSQL Getting started: basic transaction processing operations _ MySQL

MYSQL: basic transaction processing operations bitsCN.com

MYSQL basics: basic operations for transaction processing

Related links:

MYSQL: Basic operations

Http: // database/201212/173868 .html

MYSQL 2: use regular expressions to search

Http: // database/201212/173869 .html

MYSQL 3: full text search

Http: // database/201212/173873 .html

MYSQL entry 4: MYSQL data types

Http: // database/201212/175536 .html

MYSQL entry 5: MYSQL character set

Http: // database/201212/175541 .html

MYSQL getting started 6: MYSQL operators

Http: // database/201212/175862 .html

MYSQL entry 7: MYSQL common functions

Http: // database/201212/175864 .html

Step 8: basic database and table operations

Http: // database/201212/175867 .html

MYSQL entry 9: simple indexing operations

Http: // database/201212/176772 .html

MYSQL Getting started: Basic View operations

Http: // database/201212/176775 .html

MYSQL getting started 11: basic trigger operations

Http: // database/201212/176781 .html

MYSQL entry 12: basic operations of stored procedures

Http: // database/201212/177380 .html

Step 13: basic operations of user-defined functions

Http: // database/201212/177382 .html

MYSQL tutorial 14: basic operations on cursors

Http: // database/201212/177384 .html

Transaction processing (transaction processing) can be used to maintain the integrity of the database. it ensures that batch MySQL operations are either completely executed or not executed at all.

However, not all database engines support transaction processing. MyISAM and InnoDB are two of the most commonly used engines. The former does not support clear transaction processing management, while the latter does.

I. several terms related to transaction processing

Transaction refers to a group of SQL statements;

Rollback refers to the process of revoking a specified SQL statement;

Commit refers to writing unstored SQL statement results to the database table;

A savepoint is a temporary placeholder (placeholder) set in transaction processing. it can be released and rolled back (different from the process of rolling back the entire transaction ).

II. control transaction processing

The key to managing transaction processing is to split SQL statement components into logical blocks and specify when data should be rolled back and when data should not be rolled back.

1. start transaction

Start transaction;

Use the preceding statement to identify the start of a transaction;

2. use ROLLBACK

The ROLLBACK command is used to roll back (undo) MySQL statements:

In the following example, start a transaction, delete all content in the test_inn table, and view the content in the table, indicating that the table has been cleared. However, after the ROLLBACK transaction is executed, the data in the table is re-queried. The data in the table is still:

[SQL]

Mysql> start transaction;

Mysql> delete from test_inn;

Mysql> select * from test_inn;

Empty set (0.00 sec)

Mysql> rollback;

Mysql> select * from test_inn;

+ ------ + ------------ +

| Id | birthday |

+ ------ + ------------ +

| 1 | 2012-12-12 |

| 1 | 0000-00-00 |

| 1 | 2012-12-00 |

| 1 | 0000-00-00 |

+ ------ + ------------ +

Mysql> show create table test_inn;

+ ---------- + ----------------------------

| Table | Create Table

+ ---------- + ----------------------------

| Test_inn | create table 'test _ inn '(

'Id' int (11) default null,

'Birthday' date DEFAULT NULL

) ENGINE = InnoDB default charset = latin1 |

+ ---------- + ----------------------------

Note: In the preceding example, the table test_inn uses the InnoDB engine. if a table using the MyISAM engine performs the preceding operations, the data is actually cleared even after ROLLBACK.

3. use COMMIT

Generally, MySQL statements are implicitly submitted (implicit commit), that is, the commit (write or save) operation is automatically performed.

However, commit is not performed implicitly in the transaction processing block. The COMMIT statement is required for explicit submission.

Example:

[SQL]

Mysql> start transaction;

Mysql> delete from test_inn;

Mysql> commit;

When a COMMIT or ROLLBACK statement is executed, the started transaction is automatically closed.

4. retain points

To support the rollback of some transactions, a reserved point must be placed at an appropriate position in the transaction processing block.

Create retention point: SAVEPOINT sap_name;

Roll back TO the reserved point: rollback to sap_name;

RELEASE retention point: RELEASE sp_name;

Retention points can also be automatically released after the transaction is processed (execute a ROLLBACK or COMMIT.

5. change the default submission behavior

SET autocommit = 0;

The autocommit flag is for each connection, not the server.

Reference: MySQL required knowledge

BitsCN.com