MySQL Business Knowledge Essentials (ii)

The basic knowledge of the transaction is described in point one, so how do you deal with the transaction in MySQL?

A transaction is made up of a set of SQL statements that are entered by a user and are terminated as modified to persist or roll to the original state. In MySQL, at the beginning of a session, the system variable autocommit value is 1, that is, the auto-commit function is open, when the user executes an SQL statement, the statement changes to the database are immediately committed to a persistent modification to the disk, a transaction is ended. Therefore, the user must turn off autocommit, and the transaction can consist of more than one SQL statement. Use the following statement:

SET @ @AUTOCOMMIT = 0;

After you execute this statement, you must explicitly indicate the termination of each transaction in order for the SQL statement in the transaction to make changes to the database to be persisted.

Example: Execute the following statement:

Delete from student where study number = ' 3160707001 '

SELECT * from student;

From the execution results, it was found that a row had been deleted from the table. However, this modification is not persisted because Autocommit has been closed. The user can undo this modification by rollback, or use the commit statement to persist the change.

Here's how to handle a transaction:

1. Start a transaction

A new transaction begins when the first SQL statement for an application or the first SQL after the commit or ROLLBACK statement is executed. Alternatively, you can use a start TRANSACTION statement to explicitly start a transaction.

Syntax format: START TRANSACTION | BEGIN work

A BEGIN work statement can be used to replace the start transaction statement, but start transaction is more commonly used.

2. End Transaction

A COMMIT statement is a commit statement that makes all data modifications that have been performed since the beginning of the transaction become a permanent part of the database, as well as the end of a transaction in the syntax format:

COMMIT [Work] [and [No] CHAIN] [[No] RELEASE]

3. Revocation of transactions

Rollback is the undo statement, which revokes the changes made by the firm and ends the current transaction. Syntax format:,

ROLLBACK [Work] [and [No] CHAIN] [[No] RELEASE]

In the preceding example, if you add the following statement at the end

Rollback work;

After executing this statement, the previous delete action is revoked, and the SELECT statement can be used to query whether the row data is restored.

4. Rolling back a transaction

In addition to undoing the entire transaction, the user can also use the rollback to statement to roll back a transaction to a point, before which a savepoint needs to be set up with a savepoint statement.

SavePoint Syntax Format:

SavePoint identifier

Where identifier is the name of the save point.

The ROLLBACK to savepoint statement rolls back a transaction to the named SavePoint. If the current transaction changes the data after the savepoint is set, the changes are revoked in the rollback, in the syntax format:

ROLLBACK [work] to savepoint identifier

When a transaction is rolled back to a savepoint, the save point that is set after the savepoint is deleted.

The RELEASE savepoint statement removes the named SavePoint from a set of savepoint for the current transaction. No commit or rollback occurs. If the save point does not exist, an error occurs. Its syntax format is:

RELEASE savepoint Identifier

MySQL Business Knowledge Essentials (ii)