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

Today, I will talk to you about the role of affairs and ACID in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

The so-called Transaction is to maintain the integrity of the database by ensuring that batches of operations are either fully executed or not executed at all. Take an example of a bad street: a transfers 1000 yuan to B, and the corresponding SQL statement is: (no transaction is explicitly defined)

UPDATE deposit_table set depositdeposit = deposit-1000 WHERE name ='A'; UPDATE deposit_table set depositdeposit = deposit + 1000 WHERE name ='B'

The results are as follows:

Mysql > SELECT * FROM deposit_table; +-+ | name | deposit | +-+-+ | A | 3000 | B | 5000 | +-+-+

There may be problems in doing so, such as when the database crashes after the first statement is executed, and the final result may be this (debatable, depending on whether the next SQL is written to the log):

+-+-+ | name | deposit | +-+-+ | A | 2000 | | B | 5000 | +-+-+

A's 1000 yuan disappeared for no reason, which must not be appropriate. Transactions are created to solve similar problems. If transactions are used to process money transfers, the corresponding SQL is:

START TRANSACTION; UPDATE deposit_table set depositdeposit = deposit-1000 WHERE name = 'Agar; UPDATE deposit_table set depositdeposit = deposit + 1000 WHERE name =' Bamboo; COMMIT

Simply adding START TRANSACTION and COMMIT before and after the original two SQL statements ensures that the balance of A will not decrease even if the transfer operation fails.

When you think about it, it is not particularly appropriate to find that this example is not particularly appropriate, because the database recovery technology (which we will talk about later) will affect the final result, and it is not easy to simulate this kind of failure. the final result can only be guessed:) but I can't think of any other more appropriate example.

Next, some of the features of the transaction and (some) implementation details are discussed in detail.

ACID

A:Atomicity (atomicity)

C:Consistency (consistency)

I:Isolation (isolation)

D:Durability (persistence)

1. Atomicity (atomicity)

Let's start with two important concepts: commit and rollback. When we perform the commit operation, we will make permanent changes to the database and perform the rollback operation, which means that the database will undo all ongoing uncommitted changes. Note that persistence here does not mean that data is brushed to disk as soon as the transaction is completed. Even if it is not flushed to disk, MySQL has a logging mechanism to ensure that changes are not lost.

A transaction is a unit of work that supports commit and rollback. Atomicity means that when a transaction makes multiple changes to the database, all changes are successful when the transaction is committed or undone when the transaction is rolled back. This is an official statement, but some people seem to misunderstand the commit statement. In fact, even if there is an error in a statement in the transaction, once you execute commit, the previous normal changes will still be committed, and MySQL will not automatically judge whether the SQL execution in the transaction is successful or not.

Let's take a look at commit and rollback with examples:

Mysql > SELECT * FROM deposit_table; +-+-+ | name | deposit | +-+-+ | A | 2000 | B | 6000 | +-+-+ 2 rows in set (0.04 sec) mysql > START TRANSACTION; INSERT INTO deposit_table VALUES ('START TRANSACTION; INSERT INTO deposit_table VALUES, 7000); INSERT INTO deposit_table VALUES (' Downs, 8000) # insert D again, because of the uniqueness of the primary key, the statement will execute the failed INSERT INTO deposit_table VALUES ('Downs, 9000); COMMIT; # commit transaction Query OK, 0 rows affected (0 sec) Query OK, 1 row affected (0 sec) Query OK, 1 row affected (0 00 sec) 1062-Duplicate entry' D 'for key' PRIMARY' Query OK, 0 rows affected (0 07 sec) mysql > SELECT * FROM deposit_table +-+-+ | name | deposit | +-+-+ | A | 2000 | B | 6000 | C | 7000 | D | 8000 | +-+-+ 4 rows in set (sec)

We can see that the execution of this SQL statement failed with a 1062-Duplicate entry 'D'for key' PRIMARY' error when executing INSERT INTO deposit_table VALUES ('Downs, 9000) because D had been inserted in the previous statement, but after executing COMMIT, the previous changes were still committed, which is obviously not in line with our expectations.

Note: if you are using the Navicat query interface, you will not be able to execute the COMMIT statement, you can only execute to the place where the error is reported, it is recommended to use the command line to execute.

So in practice, we need to determine whether to use ROLLBACK or COMMIT based on the error return value of MySQL. It's like this:

# create a stored procedure CREATE DEFINER= `root` @ `localhost` PROCEDURE `localhost` () BEGIN # create a marker, and set it to 1 DECLARE err_flg INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1 if there is an error; START TRANSACTION; INSERT INTO deposit_table VALUES ('DECLARE err_flg INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg, 7000); INSERT INTO deposit_table VALUES (' DECLARE err_flg INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg, 8000) INSERT INTO deposit_table VALUES ('FROM deposit_table;, 9000); # if an error occurs, roll back the transaction IF err_flg = 1 THEN SELECT' SQL Err Invoked'; # error message ROLLBACK; SELECT * FROM deposit_table; # No error occurred, just submit ELSE SELECT 'TRANSACTION Success' COMMIT; SELECT * FROM deposit_table; END IF; END

Next we call the stored procedure:

Mysql > call insert_test () +-+ | SQL Err Invoked | +-+ | SQL Err Invoked | +-+ 1 row in set (0.04 sec) +-+-+ | name | deposit | +-+-+ | A | 2000 | | B | 6000 | +-+-+ 2 rows in set (0.09 sec) Query OK 0 rows affected (0.00 sec)

The error message was printed out and the contents of the SQL Err Invoked table did not change, indicating that our ROLLBACK successfully rolled back the transaction as we expected. If you are calling MySQL's interface in another language, you only need to get the error flag and execute ROLLBACK or COMMIT accordingly.

2. Consistency (consistency)

The explanation given on the website is as follows:

The database remains in a consistent state at all times-after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.

The database is always in a consistent state after each commit or rollback and during ongoing transactions, and if the relevant data is updated across multiple tables, the query will see all old values or all new values, not a mixture of new and old values.

For example:

# the definition of START TRANSACTION; UPDATE a SET name b is skipped by START TRANSACTION; UPDATE a SET name ='a baby new 'WHERE name =' a baby oldboy; UPDATE b SET name = 'baked new' WHERE name = 'baked oldboy; COMMIT

Consistency in this example, that is, if there is a query SELECT a.name, b.name FROM a, b; the result is either a_old b_old (indicating that the transaction has been rolled back or in progress) or a_new b_new (indicating that the transaction has been successfully committed), instead of a_old b_new and a_new b_old.

Some bloggers interpret consistency as "data meets real-world constraints, such as uniqueness constraints, and so on." Personally, I am inclined to the interpretation of the official documents, which is a matter of opinion, but these concepts do not make much sense.

3. Isolation (isolation)

The isolation of transactions means that transactions cannot interfere with each other or see each other's uncommitted data. This isolation is achieved through the locking mechanism. We have also learned in the operating system that the use of locks often means a decline in concurrency performance because blocking or even deadlocks can occur.

Of course, when users are sure that transactions do not interfere with each other, they can adjust the isolation level and sacrifice some isolation to improve performance and concurrency. As for which isolation level (isolation level) to use, you need to do your own trade off.

Because isolation involves a lot of content, I will explain it in detail in the next article.

4. Durability (persistence)

The persistence of the transaction means that once the commit operation is successful, the changes made by the transaction will not be lost due to accidents, such as power outages, system crashes and other potential threats. MySQL provides many mechanisms, such as logging technology, doublewrite buffer, and so on.

I will write a separate article on MySQL's log recovery technology, which will talk about doublewrite buffer technology here.

Although the technology is called buffer, the buffer is actually not located in memory, but on disk. This may sound weird-since you are putting the data on disk, why not write it directly to data file, instead of doing so?

This is because the Page Size of InnoDB is generally 16kb, and its data check is also calculated for the page. In the process of brushing the data into the disk, if a failure such as a power outage occurs, the page may only be written to a part (partial page write). This situation cannot be solved by the redo log, because the redo log records the physical operation of the page, and if the page itself is damaged, there is no point in redo it. So we need a copy to restore the page when this happens.

Moreover, the buffer is written sequentially, and the overhead is much less than random read and write, so the performance is not reduced to 50% after doublewrite.

Second, common sentences in transactions

START TRANSACTION / BEGIN explicitly opens a transaction

COMMIT commits transactions and permanently modifies the database

SAVEPOINT creates Savepoints in a transaction

RELEASE SAVAPOINT removes a SavePoint

ROLLBACK rolls back the transaction, withdraws all uncommitted changes, and the transaction terminates

ROLLBACK TO [SAVEPOINT] rollback to the given SavePoint, but the transaction is not terminated. In addition, the row lock after the SavePoint will not be released. For more information, please see SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements

InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.)

SET TRANSACTION sets transaction isolation level

Whether SET autocommit 0Plus 1 is automatically committed (default autocommit)

Emphasize the autocommit parameter. By default, if you do not explicitly use START TRANSACTION / BEGIN, MySQL treats each SQL as a separate transaction, for example:

The original table structure:

Mysql > SELECT * FROM deposit_table; +-+ | name | deposit | +-+-+ | A | 2000 | B | 6000 | +-+-+ 2 rows in set (0.04 sec)

New stored procedure (only START TRANSACTION deleted):

CREATE DEFINER= `root` @ `localhost` PROCEDURE `insert_ test` () BEGIN # Routine body goes here... DECLARE err_flg INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1; # START TRANSACTION; INSERT INTO deposit_table VALUES ('THEN SELECT, 7000); INSERT INTO deposit_table VALUES (' Downs, 8000); INSERT INTO deposit_table VALUES ('Downs, 9000); IF err_flg = 1 THEN SELECT' SQL Err Invoked'; ROLLBACK; SELECT * FROM deposit_table ELSE SELECT 'TRANSACTION Success'; COMMIT; SELECT * FROM deposit_table; END IF; END

Result of the call:

Mysql > call insert_test () +-+ | SQL Err Invoked | +-+ | SQL Err Invoked | +-+ 1 row in set (0.24 sec) +-+-+ | name | deposit | +-+-+ | A | 2000 | | B | 6000 | | C | 7000 | | D | 8000 | +-+-+ 4 rows in set (0.28sec) Query OK 0 rows affected (0.21 sec)

Here, we see that although ROLLBACK is actually executed, C and D are still plugged into the deposit_table. This is because there is no explicit indication of the transaction, and MySQL does an implicit transaction and automatically commits each change, so it cannot be rolled back.

After reading the above, do you have any further understanding of the role of transactions and ACID in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.