Summary of MySQL databases and related transactions

The following articles mainly describe the MySQL database and related transactions. In practice, many people think that the MySQL database does not support transaction processing. In fact, as long as the MySQL database version supports the BDB or InnoDB table type, your MySQL database can process transactions.

In this regard, InnoDB tables are the most used. Although there were some unpleasant things such as Oracle's purchase of InnoDB, the commercial struggles were not related to the technology, the following uses the InnoDB table type as an example to briefly describe the transactions in MySQL.

First, let's clarify the related knowledge involved in the transaction:

Transactions should all have ACID features. The so-called ACID is Atomic atomicity), Consistent consistency), Isolated isolation), Durable continuity) written in the first letter of the four words, the following uses "bank transfer" as an example to explain their meanings:

Atomicity: the statements that make up transaction processing form a logical unit and cannot only execute a part of it. In other words, transactions are the smallest units that are inseparable. For example, in the bank transfer process, it is unreasonable to change only one account by subtracting the transfer amount from one account and adding it to another account.

Consistency: the MySQL database is consistent before and after transaction processing. That is to say, the transaction should correctly switch the system status. For example, in the bank transfer process, either the transfer amount is transferred from one account to another, or both accounts remain unchanged.

Isolation: one transaction has no impact on the processing of another transaction. That is to say, no transaction can see a transaction in an incomplete state. For example, in the bank transfer process, another transfer transaction can only be in the waiting state before the transfer transaction is committed.

Continuity: the effect of transaction processing can be permanently saved. Conversely, transactions should be able to withstand all failures, including server, process, communication, and media failures. For example, in the bank transfer process, the account status must be saved after the transfer.

Let's take a look at what problems will be handled by transactions:

Here we will not talk about the example of "bank transfer". It is an example of "online book purchase" that is more likely to be encountered by everyone. Let's take a look at the background: When I buy a book online, the MySQL database Number of a book is 123), only the last one is left. At this time, two users send a purchase request to this book almost simultaneously, let's take a look at the entire process:

Before specific analysis, let's take a look at the definition of the data table:

 
 

  
  
Create table book
  
  
(
  
  
Book_id unsigned int (10) not null auto_increment,
  
  
Book_name varchar (100) not null,
  
  
Book_price float (5, 2) not null, # I assume that the price of each book will not exceed 999.99 yuan
  
  
Book_number int (10) not null,
  
  
Primary key (book_id)
  
  
)
  
  
Type = innodb; # engine = innodb.
 
 

For User A, his actions are a little faster than those of user B. The actions triggered during the purchase process are roughly as follows:

1. SELECT book_number FROM book WHERE book_id = 123;

Book_number is greater than zero. Confirm the purchase behavior and update book_number

2. UPDATE book SET book_number = book_number-1 WHERE book_id = 123;

Book purchased successfully

For user B, his actions are a little slower than those of user A. The actions triggered during the purchase process are the same as those of user:

1. SELECT book_number FROM book WHERE book_id = 123;

At this time, the first step of operation has just been completed by Jia, and the second step has not been completed yet, so book_number must be greater than zero

2. UPDATE book SET book_number = book_number-1 WHERE book_id = 123;

Book purchased successfully

On the surface, the operations of Party A and Party B have been successful. They have all bought books, but there is only one stock. How can they all succeed? Let's take a look at the content of book_number in the data table, which has already changed to "-1". This is of course not allowed. In fact, the attribute of unsigned should be added to declare such a column type, to ensure that it cannot be negative, this is to illustrate the problem so it is not set in this way)

Well, the problem is clearly stated. Let's take a look at how to solve this problem using transactions. Open the MySQL manual and you can see that it is very easy to use transactions to protect the correct execution of your SQL statements, there are basically three statements: Start, submit, and rollback.

START: start transaction or BEGIN statement can START a new TRANSACTION.

Submit: COMMIT can submit the current transaction, which is a permanent change of a change.

ROLLBACK: ROLLBACK can roll back the current transaction and cancel its changes.

In addition, set autocommit = {0 | 1} can be used to disable or enable the default autocommit mode for the current connection.

Is it true that we only need to wrap our SQL statements with transaction statements? For example, the following code:

 
 

  
  
BEGIN;  
  
  
SELECT book_number FROM book WHERE book_id = 123;  
  
  
// ...  
  
  
UPDATE book SET book_numberbook_number = book_number - 1 WHERE book_id = 123;  
  
  
COMMIT;  
 
 

The answer is no. In this way, the problem still cannot be avoided. To avoid this situation, the actual situation should be as follows:

 
 

  
  
BEGIN;  
  
  
SELECT book_number FROM book WHERE book_id = 123 FOR UPDATE;  
  
  
// ...  
  
  
UPDATE book SET book_numberbook_number = book_number - 1 WHERE book_id = 123;  
  
  
COMMIT;  
 
 

Because for update is added, a row lock will be added to this record. If the transaction is not completely completed, other transactions are using SELECT... the for update request will be in the waiting state until the end of the previous transaction, so as to avoid the problem, if your other transactions use SELECT statements without for update, this protection will not be available.

The above content is an introduction to MySQL Databases and transactions. I hope you will get some benefits.