Learning notes for MySQL business

Because of the project design, involved in the transfer of money, so we need to use MySQL transaction processing, to ensure that a set of processing the correctness of the results

Using the transaction, it is inevitable to sacrifice a part of the speed to ensure the correctness of the data.

Only InnoDB support transactions

Transaction ACID atomicity (atomicity), consistency (stability), isolation (isolation), durability (reliability)

1, the atomic nature of the transaction
A set of transactions, either successful or withdrawn.

2, stability
Illegal data (foreign KEY constraint, etc.), transaction withdrawn.

3, the isolation of
Transactions run independently.
The results of one transaction affect other transactions, then other transactions are withdrawn.
The 100% isolation of the transaction requires sacrificing speed.

4. Reliability
After the hardware and software crashes, the INNODB data-table driver uses the log file refactoring modifications.
Reliability and high speed can not be both, the INNODB_FLUSH_LOG_AT_TRX_COMMIT option determines when the transaction is saved to the log.
Open transaction
START TRANSACTION or BEGIN

Commit TRANSACTION (Close transaction)
COMMIT

Discard transaction (Close transaction)
ROLLBACK

Reentry point
SavePoint Adqoo_1
ROLLBACK to SavePoint Adqoo_1
Transactions that occurred before the adqoo_1 of the exhumation point were committed and then ignored

Termination of a transaction

Set autocommit mode
SET autocommit = 0
Each SQL is a different command of the same transaction, separated by a COMMIT or rollback
After the line is dropped, no COMMIT transaction is abandoned.

Transaction lockout mode

System defaults: No need to wait for a transaction to end, you can directly query the results, but can not be modified, deleted.
Disadvantage: The results of the query may have expired.
Advantages: No need to wait for the end of a transaction, you can directly query the results.

You need to set the lock mode in the following mode

1. SELECT ... Lock in SHARE MODE (shared Lock)
The data that is queried is the data of the database at this time (the results of other commit transactions have been reflected here)
The SELECT must wait for a transaction to be completed before it can be executed

2. SELECT ... For UPDATE (exclusive lock)
For example, SELECT * FROM TableName WHERE id<200
So id<200 data, the data being queried, will no longer be modified, deleted, SELECT ... LOCK in SHARE mode operation
Until the end of this transaction

The difference between shared and exclusive locks is whether to block SELECT from other customers ... LOCK in SHARE mode command

3, Insert/update/delete
All associated data will be locked, plus exclusive locks

4. Anti-insert Lock
For example, SELECT * FROM TableName WHERE id>200
Then the id>200 record cannot be inserted.

5, Dead Lock
Automatically recognize deadlocks
The advanced process is executed, and subsequent processes receive an error message and are rolled back in a rollback manner
Innodb_lock_wait_timeout = N to set maximum wait time, default is 50 seconds

Transaction isolation mode

SET [session| GLOBAL] TRANSACTION Isolation Level
READ Uncommitted | READ Committed | Repeatable READ | SERIALIZABLE
1, without session, Global SET command
Valid for next transaction only
2, SET session
Set isolation mode for the current session
3, SET GLOBAL
Set quarantine mode for all future new MySQL connections (not included in the current connection)

Isolation mode

READ UNCOMMITTED
Do not isolate Select
Incomplete modifications to other transactions (not commit) and the results are taken into account

READ committed
Take into account the other transaction's COMMIT modification
The same SELECT may return different results in the same transaction

Repeatable READ (default)
Does not take into account changes in other matters, whether or not other transactions have been submitted with a commit order
In the same transaction, the same SELECT returns the same result (provided this transaction is not modified)

SERIALIZABLE
Like Repeatable read, a shared lock was added to all the Select

Error handling
Perform the appropriate processing according to the error message

Finally look at the PHP + MySQL Transaction operation code Demo:

The actual lamp application, the general PHP uses the ADODB operation MySQL, below gives the ADODB corresponding code convenient everybody to consult:

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

code is as follows

copy code

<?php //... $adodb->starttrans (); //Actually, the query invoked by GetOne can also be placed directly into rowlock, just to demonstrate that the results are more obvious. $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 (); //... ? -------------------------------------------------------------------------------

Where the Rowlock method is a row lock that is implemented by the call for update, you might want to write "for update" directly to the SQL statement that $adodb->getone () calls to implement the function of the row lock, yes, that's OK, However, not all databases use the "for UPDATE" syntax to implement the row lock function, such as Sybase use "HOLDLOCK" syntax to implement the row lock function, so for your database abstraction layer to maintain portability, I would like to persuade you to use Rowlock to implement the row lock function