MySQL and transactions

Author: Lao Wang

Mysql5.x has been released for a long time, but many people think that MySQL does not support transaction processing, which has to blame them for being ignorant. In fact, as long as your MySQL version supports the bdb or InnoDB table type, your MySQL has the ability to 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. 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. 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 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 assume the background of the question: When I buy a book online (the database number 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 exampleCode:

-------------------------------------------------------------------------------

Begin;

Select book_number from book where book_id = 123;

//...

Update book set book_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 = 123For update;

//...

Update book set book_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.

Finally, let's take a look at the PHP + MySQL transaction operation code Demonstration:

In actual lamp applications, PHP generally uses ADODB to operate mysql. The following shows the corresponding code of ADODB for your convenience:

-------------------------------------------------------------------------------

<? PHP
//...

$ ADODB->Starttrans();

// Actually, the query called by getone can also be directly placed in rowlock. This is only for demonstration of more obvious results.

$ ADODB->Rowlock('Book','Book _ id = 123');

$ Booknumber=$ ADODB->Getone("Select book_number from book where book_id = 123");

$ ADODB->Execute("Update book set book_number = book_number-1 where book_id = 123");

$ ADODB->Completetrans();

//...
?>

-------------------------------------------------------------------------------

The rowlock method is the row lock implemented by the for update method. You may want to write "for update" directly to $ ADODB-> getone () the SQL statement called to implement the row lock function is good, it is indeed OK, but not all databases use the "for update" syntax to implement the row lock function, for example, Sybase uses the "holdlock" syntax to implement the row lock function. To ensure portability of your database abstraction layer, I suggest you use rowlock to implement the row lock function, as for portability, you can leave it to ADODB. Well, it's a bit far away. I'll talk about it now.

-------------------------------------------------------------------------------

Appendix:

In ADODB, there is a settransactionmode () method that can set the transaction isolation level, as shown below:

Settransactionmode allows you to pass in the transaction mode to use for all subsequent transactions for that connection session. note: If you have persistent connections and using MySQL or MSSQL, you might have to explicitly reset your transaction mode at the beginning of each page request. this is only supported in PostgreSQL, MSSQL, MySQL with InnoDB and oci8 currently. for example:

$ Db-> settransactionmode ("serializable ");
$ Db-> begintrans ();
$ Db-> execute (...);
$ Db-> commitrans ();

$ Db-> settransactionmode (""); // restore to default
$ Db-> starttrans ();
$ Db-> execute (...);
$ Db-> completetrans ();

Supported values to pass in:

* Read uncommitted (allows dirty reads, But fastest)
* Read committed (default Postgres, MSSQL and oci8)
* Repeatable read (default MySQL)
* Serializable (slowest and most restrictive)

You can also pass in database specific values such as 'snapshot' for MSSQL or 'read only' for oci8/Postgres.