MySQL transaction processing

MySQL transaction processing2011-03-06 16:26 2984 people read Comments (0) favorite reports Mysqltransactionspostgresql Database Sybasenull

mysql5.x have been released for a long time, but there are many people think that MySQL is not support transaction processing, this has to blame them is ignorant, in fact, as long as your MySQL version support BDB or InnoDB table type, then your MySQL has the ability of transaction processing. In this, but also with the InnoDB table type of the most, although later, such as Oracle acquisition InnoDB and other things that make MySQL uncomfortable, but those business struggles and technology-independent, the following InnoDB table type as an example simply talk about the transaction in MySQL.

Let's start by clarifying the relevant knowledge of the transaction:

The business should have acid characteristics. The so-called acid is Atomic (atomicity), consistent (consistency), Isolated (isolation), durable (persistent) four words written in the first letter, the following "bank transfer" as an example to explain their meaning:

Atomicity: The statement that makes up the transaction forms a logical unit and cannot execute only part of it. In other words, a transaction is the smallest unit that is indivisible. For example, in a bank transfer process, you must subtract the transfer amount from one account and add it to another account, it is unreasonable to change only one account.

Consistency: The database is consistent before and after the execution of the transaction. In other words, the transaction should correctly convert the System state. For example: During a bank transfer, the transfer amount is transferred from one account to another, or two accounts are not changed, and there is no other situation.

Isolation: One transaction has no effect on another transaction processing. This means that no transaction is likely to see a transaction in an incomplete state. For example, in a bank transfer process, another transfer transaction can only be in the waiting state until the transfer transaction is not committed.

Persistence: The effect of transaction processing can be permanently preserved. Conversely, transactions should be able to withstand all failures, including servers, processes, communications, media failures, and so on. For example: In the process of bank transfer, the status of transfer payback can be saved.

Let's take a look at the issues that will be used for transaction processing:

This is not the case of "bank transfer", which is an example of an "online book" that we are actually more likely to encounter. Let's take a look at the background of the problem: online purchase, a book (database number 123) Only the last one, and this time, two users to this book almost at the same time the purchase request, lets us see the whole process:

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

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

CREATE TABLE book (book_id unsigned int (ten) not NULL auto_increment, Book_name varchar (+) NOT NULL, BOOK_PR Ice Float (5, 2) not NULL, #我假设每本书的价格不会超过999. $99 Book_number Int (ten) not NULL, primary key (book_id)) type = Innod b #engine = InnoDB is ok

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

For user A, he moves a little bit faster than B, and the actions triggered by the purchase process are roughly the same:

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

1. SELECT book_number from book WHERE book_id = 123;

Book_number greater than 0, confirm purchase behavior and update Book_number

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

Successful book purchase

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

For User B, his movements are slightly slower than a bit, and the purchase process triggers the same action as the armor:

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

1. SELECT book_number from book WHERE book_id = 123;

At this time, a just finished the first step of operation, not yet time to do the second step, so book_number must be greater than 0

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

Successful book purchase

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

On the surface, the operation of a B was successful, they all bought a book, but only one in stock, how can they all succeed? Look at the contents of the data table Book_number, has become "1", which is certainly not allowed (in fact, declaring such a column type should be added to the unsigned property to ensure that it can not be negative, this is to illustrate the problem, so there is no such setting)

OK, the problem is clear, and then to see how to use the transaction to solve the problem, open the MySQL manual, you can see that you want to use the transaction to protect your SQL correctly executed is very simple, basically three statements: start, Commit, rollback.

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

Start: The start transaction or BEGIN statement can start a new transaction

Commit: Commit can commit the current transaction, change becomes permanent change

Rollback: Rollback can roll back the current transaction and cancel its change

Additionally, SET autocommit = {0 | 1} can disable or enable the default autocommit mode for the current connection.

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

Is that just a matter of wrapping up our SQL statements with a transactional statement to make sure it's right? For example, the following code:

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

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, this still does not prevent the problem, if you want to avoid such a situation, the actual should be as follows:

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

BEGIN;

SELECT book_number from book WHERE book_id = 123 for UPDATE ;

// ...

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

COMMIT;

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

Because a for UPDATE is added, a row lock is added to this record, and if the transaction does not end completely, the other transactions are using SELECT ... The FOR update request will wait until the last transaction finishes before it can continue, avoiding the problem, and note that if your other transactions are using a SELECT statement without a for update, this protection will not be available.

Finally look at the code demo for PHP + MySQL transaction operation:

Actual lamp application, general PHP use ADODB operation MySQL, the following gives ADODB the corresponding code for easy access to everyone:

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

<?php //...
$adodb -Starttrans ();

In fact, the query called by GetOne can also be placed directly into the rowlock, here just to demonstrate the effect can be more obvious.
$adodb-Rowlock(' Book ',' book_id = 123 ');
$bookNumber=$adodb-GetOne(  "select book_number from book where   book_id = 123 " ); &NBSP;
$adodb  -> execute  (   "Update book set book_number = book_number  - 1 where  book_id = 123 " ); &NBSP;
$adodb  -> completetrans  (); &NBSP;
//   ,

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

Where the Rowlock method is to invoke the for update to implement the row lock, you may want to "for update" directly into the $adodb->getone () called the SQL statement to implement the row lock function, yes, that is true, However, not all databases use the "for UPDATE" syntax to implement row lock functionality, such as Sybase using the "HOLDLOCK" syntax to implement row locking, so for your database abstraction layer to maintain portability, I still advise you to use Rowlock to implement the row lock function, As for portability, give it to ADODB, well, it's a little far away, and here we are today.

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

Report:

There is a Settransactionmode () method in ADODB that enables you to set the isolation level of the transaction as follows:

Settransactionmode allows you-pass in the transaction mode-to-use for all subsequent transactions for that connection s Ession. Note:if You has persistent connections and using MySQL or MSSQL, you might has to explicitly reset your transaction mod E at the beginning for each page request. This is a supported in PostgreSQL, MSSQL, MySQL with InnoDB and Oci8 currently. For example:

$db->settransactionmode ("SERIALIZABLE"); $db->begintrans (); $db->execute (...); $db->execute (...); $db->commitrans ();

$db->settransactionmode (""); Restore to default $db->starttrans (); $db->execute (...); $db->execute (...); $db->completetrans ();

Supported values to pass in:

* READ UNCOMMITTED (allows dirty reads, but fastest) * Read COMMITTED (default Postgres, MSSQL and OCI8) * Repeata BLE READ (default mysql) * SERIALIZABLE (slowest and most restrictive)

You can also pass in the database specific values such as ' SNAPSHOT ' for MSSQL or ' READ only ' for oci8/postgres.

MySQL transaction processing