What is transaction processing in mysql

In mysql, transaction processing is a mechanism used to manage MySQL operations that must be performed in batches to ensure that the database does not contain incomplete operation results; transaction processing can be used to maintain the integrity of the database. It is guaranteed that batches of MySQL operations will not be stopped midway, they will either be fully executed or not executed at all.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Transaction processing

Transaction processing can be used to maintain the integrity of the database. It guarantees that batches of MySQL operations are either fully executed or not executed at all.

For example, in the personnel management system, deleting a person requires deleting not only the basic information of the person, but also the information related to the person, such as mailboxes, articles, etc. In this way, these databases The operation statement constitutes a transaction!

Transaction processing is a mechanism used to manage MySQL operations that must be performed in batches to ensure that the database does not contain incomplete operation results. Using transaction processing, you can ensure that a set of operations will not stop midway, they will either be executed as a whole, or not executed at all (unless explicitly instructed) . If no errors occur, the entire set of statements is committed (written) to the database table. If an error occurs, roll back (undo) to restore the database to a known and safe state.

Generally speaking, a transaction must meet four conditions (ACID): Atomicity(Atomicity, or indivisibility), Consistency(Consistency), isolation( Isolation, also known as independence), persistence(Durability).

• Atomicity: All operations in a transaction will either be completed or not completed, and will not end in any intermediate link. If an error occurs during the execution of the transaction, it will be rolled back to the state before the transaction started, as if the transaction had never been executed.

• Consistency: The integrity of the database is not destroyed before the transaction starts and after the transaction ends. This means that the data written must fully comply with all preset rules, including the accuracy and concatenation of the data, and that the subsequent database can spontaneously complete the predetermined work.

• Isolation: The database allows multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent multiple transactions from being executed concurrently due to Cross execution leads to data inconsistency. Transaction isolation is divided into different levels, including read uncommitted, read committed, repeatable read and serializable.

• Persistence: After the transaction is completed, the modification to the data is permanent and will not be lost even if the system fails.

1.1 Keywords

When using transactions and transaction processing, there are several key words that appear repeatedly. Here are a few terms you need to know about transaction processing:

• Transaction: refers to a set of SQL statements;
• Rollback ( rollback): refers to the process of undoing the specified SQL statement ;
• Submit (commit): refers to writing the unstored SQL statement results to the database table;
• Savepoint: refers to the temporary placeholder (placeholder) set in the transaction, for which you can issue a rollback (different from rolling back the entire transaction) .

1.2 Transaction Control Statement

The key to managing transaction processing is to decompose SQL statement groups into logical blocks, and clearly specify when the data should be rolled back and when it should not be rolled back.

##1.2.1 START TRANSACTION Start transaction

MySQL uses the following statement to identify the start of a

transaction:

START  TRANSACTION

1.2.2 COMMIT

General MySQL statements are executed and written directly against database tables. This is the so-called implicit commit (implicit commit), that is, the submission (write or save) operation is performed automatically.
         However, In the transaction block, the commit will not be performed implicitly. To make an explicit commit, use the COMMIT statement. COMMIT commits the transaction and makes all modifications that have been made to the database permanent; As shown below:

START TRANSACTION  
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

In this example, order 20010 is completely deleted from the system. Because it involves updating two database tables, orders and orderItems, a transaction block is used to ensure that the order is not partially deleted. The final COMMIT statement only writes out changes if no errors occur. If the first DELETE works but the second fails, the DELETE is not committed (in fact, it is automatically revoked).

1.2.3 ROLLBACK

MySQL’s ROLLBACK command is used to roll back (undo) the MySQL statement , As follows:

SELECT * FROM orderitems;
START TRANSACTION        -- 事务开始
DELETE FROM orderitems;
SELECT * FROM orderitems;
ROLLBACK;
SELECT * FROM orderitems;

The above example starts by displaying the contents of the ordertotals table. First execute a SELECT to show that the table is not empty. Then start a
transaction processing , use a DELETE statement to delete all rows in ordertotals. Another SELECT statement verifies that ordertotals is indeed empty. At this time, use a ROLLBACK statement to roll back all statements after START TRANSACTION , and the last SELECT statement shows that the table is not empty.
      Obviously, ROLLBACK can only be used within a transaction (after executing a START TRANSACTION command) .

Transaction processing is used to manage INSERT, UPDATE and DELETE statements. You cannot roll back a SELECT statement. (This also makes little sense.) You cannot roll back a CREATE or DROP operation. These two statements can be used within a transaction block, but they will not be undone if you perform a rollback.

1.2.4 Retention point

          Simple ROLLBACK and COMMIT statements can write or cancel the entire transaction . However, this is only possible for simple transactions; more complex transactions may require partial commit or rollback.

In order to support rolling back part of the transaction, placeholders must be placed at appropriate locations in the transaction block. This way, if you need to roll back, you can fall back to a placeholder. These placeholders are called retention points. In order to create a placeholder, you can use the following SAVEPOINT statement:

SAVEPOINT delete1;

Each retain point has a unique name that identifies it, so that when rolling back, MySQL Know where to fall back to. The following operations can fall back to the given retention point:

ROLLBACK TO delete1;

1.2.5 Change the default submission behavior

As mentioned, the default The MySQL behavior is to automatically commit all changes. In other words, any time you execute a MySQL statement, the statement is actually executed against the table, and the changes take effect immediately. To instruct MySQL not to automatically commit changes, you need to use the following statement:

SET autocommit = 0;

      autocommit flag determines whether to automatically commit changes , regardless of whether there is a COMMIT statement. Setting autocommit to 0 (false) instructs MySQL not to automatically commit changes (until autocommit is set to true).

Flag for connection Specialized autocommit flag is for each connection rather than the server.

1.3 Transaction processing methods

There are two main methods for transaction processing in MYSQL:

Use BEGIN, ROLLBACK, COMMIT to implement

1)

BEGIN

Start a transaction 2)

ROLLBACK

Transaction Rollback 3)

COMMIT

Transaction confirmation Example:

START TRANSACTION; -- 开始事务
INSERT INTO runoob_transaction_test VALUE(5);
INSERT INTO runoob_transaction_test VALUE(6);
COMMIT; -- 提交事务

select * from runoob_transaction_test;

• Use SET directly to change MySQL's automatic submission mode:

SET AUTOCOMMIT=0 Disable automatic submission

        SET AUTOCOMMIT=1 Turn on automatic submission

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is transaction processing in mysql. For more information, please follow other related articles on the PHP Chinese website!