The simplest MySQL tutorial in history () "transactions (below )」,

The simplest MySQL tutorial in history () "transactions (below )」,

Tip: This series of blog posts have been synchronized to GitHub. The address is "mysql-tutorial". Thank you for your interest.Star,Fork, Error correction.

Tip: In transaction (I), we have learned how to start a transaction in MySQL and some basic operations on the transaction. In this article, we will further learn about transactions, including transaction principles, automatic transactions, rollback points, and transaction features.

Transaction Principle

Transaction Principle: After the transaction is started, all operations will be temporarily stored in the transaction log, and the transaction log will only be receivedcommitThe operation is synchronized to the data table only after the command is run. Transaction logs are cleared in any other situation, such as suddenly disconnecting and receivingrollbackCommand.

Next, let's briefly analyze the MySQL operation process:

• Step 1: The client establishes a connection with the server and starts a temporary transaction log. This transaction log only acts on the current user's current connection;
• Step 2: When the client uses SQL statements to perform write operations, the client receives and executes the SQL statements, directly writes the results to the data table, and synchronizes the data table to the database;
• Step 3: When transactions are enabled on the client, the original operation mechanism of the server is changed, and all subsequent operations are first written to the temporary log file;
• Step 4: When an SQL statement (such as a write operation) is executed on the client, the server receives the SQL statement, runs the statement, writes the result to a temporary log file, and does not synchronize the result to the database;
• Step 5: The query operation is performed on the client. The server directly fetches data from the temporary log file and returns the data to the client;
• Step 6: RuncommitOrrollbackCommand to clear the temporary log file.commitCommand to synchronize the results to the database.rollbackCommand.

Through the above analysis, we can see why when we open two MySQL client windows (two connections) at the same time, when a window opens transactions and executes SQL operations, the other window does not receive synchronization data during query. The reason is that after the transaction is started, the server will write subsequent operations to the temporary log file, while the other window will directly retrieve data from the database during query, the previous Temporary log file will not be removed.

Rollback point

Rollback point: After an operation is completed successfully, subsequent operations may succeed or fail. However, no matter the result of subsequent operations, the previous operation has been successful, therefore, we can set an operation point at the current successful position for subsequent operations to return this position, rather than returning all operations. This point is called a rollback point. The basic syntax of the rollback point is,

• Set a rollback point:Savepoint + rollback point name;
• Return rollback point:Rollback to + rollback point name;

Run the following SQL statement for testing:

-- Test rollback point -- Query bank_account table data select * from bank_account; -- enable transaction start transaction; -- transaction operation 1: pay Charies 1000 RMB update bank_account set money = money + 10000 where id = 1; -- set the rollback point savepoint spone; -- bank tax deduction: error update bank_account set money = money-10000*0.05 where id = 2; -- Query bank_account table data select * from bank_account;

Run the following SQL statement to continue the test:

-- Test rollback point -- Return rollback point rollback to spone; -- bank tax deduction: Correct update bank_account set money = money-10000*0.05 where id = 1; -- Query bank_account table data select * from bank_account; -- submit the transaction commit;

As shown in, our previous error operations are corrected after the rollback point is returned.

Automatic transactions

In MySQL, automatic transaction processing is performed by default. After the operation is completed, the operation result is immediately synchronized to the database.

Automatic transactions are performed throughautocommitFor variable control, we can view the following SQL statement:

-- Query the automatic transaction show variables like 'autocommit ';

As shown in, this is the default MySQL setting. In fact, we can choose whether to enable automatic transaction processing. The basic syntax is,

• Enable Automatic Transaction Processing:set autocommit = on / 1;
• Disable Automatic Transaction Processing:set autocommit = off / 0;

Here, we take disabling automatic transaction processing as an example to demonstrate:

-- Disable automatic transaction processing set autocommit = 0; -- View automatic transaction processing show variables like 'autocommit '; -- View bank_account table data select * from bank_account; -- modify bank_account table data update bank_account set money = money + 1000 where id = 1; -- View bank_account table data select * from bank_account;

As shown in, we did not enable transactions, but only disabled automatic transaction processing. However, we found that when we modifiedbank_accountAfter the data is in the table, the results are not synchronized to the database immediately. In fact, this is the normal phenomenon that automatic transaction processing is disabled. RuncommitAfter the command, the result of the above operation can be synchronized:

-- Submit commit; -- view the bank_account table data select * from bank_account;

Of course, if we do not executecommitCommand, but executerollbackCommand, the previous operations will be rolled back to the initial state. Here, we need to note that:In general, we should enable automatic transaction processing. Otherwise, we need to manually submit each operation after it is completed. Isn't it exhausting??

Transaction Features

The features of transactions can be summarizedACID, Specifically:

• Atomicity:AtomicIndicates that the entire transaction operation is an integral whole, which is either all successful or all failed;
• Consistency:ConsistencyIndicates that the data in the data table is consistent before and after the transaction operation;
• Isolation:Isolation, Indicates that different transaction operations are isolated from each other and do not affect each other;
• Durability:Durability, Indicates that once the transaction is committed, it cannot be modified, and the data in the data table is changed permanently.

The atomicity, consistency, and durability of the preceding transactions are easy to understand, but isolation requires special attention. For example, to open two client windows, run the following SQL statement to test the window:

-- Demonstrate isolation operation: Window 1 -- start transaction; -- modify data with id 1 to update bank_account set money = money + 666 where id = 1; -- View bank_account table data select * from bank_account; --------- fascinating line ----------- demonstrate isolation operation: window 2 -- start transaction; -- update bank_account set money = money + 666 where id = 2; -- View bank_account table data select * from bank_account;

As shown in, it perfectly shows the effect of transaction isolation, that is, the transaction operation in window 1 does not affect the transaction operation in window 2; The transaction operation in window 2, the transaction operation in window 1 is not affected either. But, after we execute the following SQL statement, we will see different results:

-- Demonstrate isolation operation: Window 1 -- start transaction; -- modify the data update bank_account set money = money + 666 where name = 'charies 'For Charies '; -- View bank_account table data select * from bank_account; --------- fascinating line ----------- demonstrate isolation operation: window 2 -- start transaction; -- change the name to Gavin data update bank_account set money = money + 666 where name = 'gavin'; -- view the bank_account table data select * from bank_account;

As shown in, the transaction in window 1 can be executed normally, but the transaction in window 2 is successfully started, but it is "stuck" when the data is modified, and after a period of time, ALock wait timeout exceededError:

So what is the cause of the above error? This is another knowledge point involving the database.Lock MechanismNow!

In fact, the default storage engine used by MySQL is InnoDB, while the default locking mechanism used by InnoDB isRow lock(Lock the current row of the operation), but if the index field is not used during the transaction operation, the system will automatically perform the full table search, that is, it automatically upgrades the row lockTable lock(Lock the current table ).

Now let's look back at the conditions we used during the first test.idIs the primary key index, so the two transactions can indicate good isolation and do not affect each other. During the second test, we replace the conditionname, AndnameIt is not an index field. Therefore, during the second test, the transaction in window 1 used the table lock to lock the entire table. before the transaction is committed or rolled back, it does not release the lock, so all operations that attempt to modify the data in the locked table will be in the waiting state. Wait for timeout. An error is reported!

We will not introduce the lock mechanism too much in the "Basic tutorial", but will discuss it in detail in the subsequent "performance optimization.

Tip: Symbol[]The enclosed content, indicating the option; symbol+, Indicates the meaning of the connection.

---- ☆☆☆-- Return-> the simplest MySQL tutorial in history <-directory -- ☆☆☆ ----

Top

11

Step on

2

View comments