About database (MySQL) Transactions

About database (MySQL) Transactions

A transaction is a logical execution unit consisting of database operation sequences of one or more steps. These operations are either executed in full or abandoned.

Because MySQL databases are used for php development, this article mainly shares some knowledge about MySQL Data transactions, currently, only the BDB and InnoDB engine types are supported for database transactions. Therefore, if you need to develop transactions, you must first set the data table to the correct engine type, for example, InnoDB (this is a commonly used engine type in MySQL databases ). 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. ACID is the first letter of four words: Atomic (Atomicity), Consistent (consistency), Isolated (isolation), and Durable (continuity, the following uses bank transfers as an example to describe their meanings:

Atomicity: the statements that make up transaction processing form a logical unit and cannot only execute a part of it. Either all or none of the data modifications are performed. If the system executes only one subset of these operations, the overall goal of the transaction may be broken. In other words, a transaction is the smallest Execution Unit in an application, just as an atom is the smallest particle in nature and has the same characteristics that cannot be further divided. A transaction is the smallest logical execution body in an application that cannot be further divided. 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. What kind of constraints does the database have? After the transaction is executed, such constraints still exist. All rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures (such as B-tree indexes or two-way linked lists) and integrity constraints (indexes and primary keys) must be consistent. Consistency is ensured by atomicity. For example, in the bank transfer process, either the transfer amount is transferred from one account to another, or both accounts remain unchanged.

Isolation: the status of the data when the transaction views the data, either the status before it is modified by another transaction, or the status after it is modified by another transaction, transactions do not view data in the intermediate state. When a transaction is serializable, the highest isolation level is obtained. Isolation is relatively complex in the transaction mechanism. 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.

Persistence: persistence is also called persistence. Once a transaction is committed, any changes made to the data must be recorded in the permanent storage, which is usually stored in the physical database. That is to say, the transaction processing result 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.

The following describes a specific example of life that requires transaction processing.

Here we use "online book purchase" as an example. Let's assume the background of the question: When I buy a book online, there is only one last book (MySQL database number is 123). 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 definitions of data tables used in this instance:

Create table book (book_id unsigned int (10) not null auto_increment, book_name varchar (100) not null, book_price float () not null, # I suppose the price of each book will not exceed 999.99 yuan book_number int (10) not null, primary key (book_id)/* www.phpernote.com */type = 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 following is a source code example for connecting to MySQL using php and using transactions.

Mysql transaction processing is mainly divided into the following four major steps:

Begin starts a transaction
 
Commit transaction validation

Rollback transaction rollback
 
End transaction ends

<? Php $ conn = mysql_connect ('localhost', 'root', '000000') or die ("data connection error! "); Mysql_select_db ('shop ', $ conn); mysql_query (" set names 'utf8' "); // use utf8 Chinese encoding; // START a TRANSACTION mysql_query ("BEGIN"); // or mysql_query ("start transaction"); $ SQL = "INSERT INTO main (num) VALUES (123 )"; $ SQL 2 = "INSERT INTO main (num1) VALUES (456 )"; // I intentionally wrote this error // $ SQL will succeed if no transaction is needed or if it is used instead of the innodb Engine $ sql2 will fail // if one of the transactions has an error, neither of the two data will be execution successful $ res = mysql_query ($ SQL ); $ res1 = mysql_query ($ sql2); if ($ res & $ res1) {mysql_query ("COMMIT"); echo 'Submission successful. ';} Else {mysql_query ("ROLLBACK"); echo' data ROLLBACK. ';} Mysql_query ("END ");

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

Articles you may be interested in

• Summary of using database transactions in Yii
• Improve database performance secret SQL optimization skills
• How to know that the SQL statements executed in the database are slow
• Navicat for mysql remote connection to the mySql database prompt 10061,1045 error Solution
• Summarize the causes and solutions for the slow MySQL Database Server
• How to optimize servers, static databases, and load balancing for high-traffic websites
• Learn to set five common MySQL database Parameters
• Steps for synchronizing and backing up mysql Databases in windows