Shulou(Shulou.com)05/31 Report--

This article will explain in detail how the database transaction control statements are, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Under the default setting of the MYSQL command line, transactions are committed automatically, that is, the COMMIT operation is performed immediately after the SQL statement is executed.

So to open a transaction to display, you must use the commands BEGIN and STARTTRANSACTION, or execute the command SETAUTOCOMMIT=0 to disable autocommit for the current session.

Transaction Control statement of MYSQL

1) STARTTRANSACTION | BEGIN

Start a transaction as shown. In a stored procedure, the parser of the MYSQL database automatically recognizes BEGIN as BEGIN...END, so only one transaction can be opened using the STARTRANSACTION statement in the stored procedure.

2) COMMIT

To use the simplest form of this statement, simply issue COMMIT. COMMIT commits the transaction and makes any changes that have been made to the database permanent. The COMMIT and COMMITWORK statements are basically the same, and both are used to commit transactions.

The difference is that COMMITWORK is used to control whether the behavior after the transaction ends is CHAIN or RELEASE. If it is in CHAIN mode, the transaction becomes a chain transaction. Users can control it through the parameter completion_type, which is 0 by default, indicating that there is no action.

In this setting, COMMIT and COMMITWORK are completely equivalent. When the parameter value is 1, COMMITWORK is equivalent to COMMITANDCHAIN, which means that a transaction with the same isolation level is automatically started immediately.

When the parameter value is 1, COMMITWORK is equivalent to COMMITANDRELEASE. Automatically disconnects from the server when the transaction is committed.

3) ROLLBACK

A rollback ends the user's transaction and undoes any uncommitted changes that are in progress.

4) SAVEPOINTidentifiter

SAVEPOINT allows users to create a SavePoint in a transaction, and a transaction can have many Savepoints.

5) RELEASESAVEPOINTidentifier

Delete a SavePoint for a transaction, and an exception is thrown when no SavePoint executes the statement.

6) Rollback to [SavePoint] identifier

This statement is used with the SAVEPOINT command. You can roll back the transaction to the marked point without rolling back any work prior to that marked point.

Note: although there is ROLLBACK, it doesn't really end a transaction, so even if you execute ROLLBACKTOSAVEPOINT, you need to run COMMIT or ROLLBACK commands as shown later.

7) SETTRANSACTION

This statement is used to set the isolation level for transactions. The transaction isolation levels provided by the InnoDB storage engine are READUNCOMMITTED, READCOMMITTED, REPEATABLEREAD, and SERIALIZABLE.

About how the database transaction control statement is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.