PHP operation MySQL Transaction instance

PHP and MySQL transaction processing

In general, transactions 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.

/*
There are two main ways to handle MySQL transactions.
1, with Begin,rollback,commit to achieve
Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Acknowledgement
2, directly with set to change the MySQL automatic submission mode
MySQL is automatically submitted by default, that is, you submit a query, it is executed directly! We can pass
Set autocommit=0 prohibit auto-commit
Set autocommit=1 turn on auto-commit
To implement transaction processing.
When you use Set autocommit=0, all of your later SQL will be transacted until you end with commit confirmation or rollback.
Note that when you end this transaction, you also open a new transaction! Press the first method to only present the current as a transaction!
The first method of personal recommendation!
Only InnoDB and BDB types of data tables in MySQL can support transactional processing! Other types are not supported!
: General MySQL Database The default engine is MyISAM, this engine does not support transactions! If you want to allow MySQL to support transactions, you can manually modify them:
The method is as follows: 1. Modify the C:\appserv\mysql\my.ini file, find the Skip-innodb, add # to the front, save the file.
2. In the operation, enter: services.msc, restart the MySQL service.
3. To phpMyAdmin, mysql->show engines; (or execute mysql->show variables like ' have_% '; ), viewing InnoDB as yes means that the database supports InnoDB.
It also indicates that the support transaction is transaction.
4. When creating a table, you can select the InnoDB engine for storage. If it is a previously created table, you can use Mysql->alter table table_name Type=innodb;
or Mysql->alter table table_name Engine=innodb; Change the engine of the data table to support transactions.
*/
/*************** transaction--1 ***************/
$conn = mysql_connect (' localhost ', ' root ', ' root ') or Die ("Data connection error!!!");
mysql_select_db (' Test ', $conn);
mysql_query ("Set names ' GBK '"); Use GBK Chinese code;
Start a transaction
mysql_query ("BEGIN"); or mysql_query ("START TRANSACTION");
$sql = "INSERT into ' user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";
$sql 2 = "INSERT into ' user ' (' Do ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";//This one I deliberately wrote wrong
$res = mysql_query ($sql);
$res 1 = mysql_query ($sql 2);
if ($res && $res 1) {
mysql_query ("COMMIT");
Echo ' submitted successfully. ‘;
}else{
mysql_query ("ROLLBACK");
echo ' data rollback. ‘;
}
mysql_query ("END");
/**************** transaction--2 *******************/
/* Method Two */
mysql_query ("SET autocommit=0"); Set MySQL not to submit automatically, you need to commit the commit statement by itself
$sql = "INSERT into ' user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";
$sql 2 = "INSERT into ' user ' (' Do ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";//This one I deliberately wrote wrong to test
$res = mysql_query ($sql);
$res 1 = mysql_query ($sql 2);
if ($res && $res 1) {
mysql_query ("COMMIT");
Echo ' submitted successfully. ‘;
}else{
mysql_query ("ROLLBACK");
echo ' data rollback. ‘;
}
mysql_query ("END"); Don't forget mysql_query ("SET autocommit=1") when the transaction is finished; autocommit


/****************** table locking methods are available for MyISAM engine databases that do not support transactions: ********************/

MyISAM & InnoDB all support,
/*
Lock tables can lock a table for the current thread. If the table is locked by another thread, it will clog until all locks can be obtained.
UNLOCK tables can release any locks held by the current thread. When a thread publishes another lock tables, or when the connection to the server is closed, all tables locked by the current thread are implicitly unlocked.
*/

mysql_query ("Lock TABLES ' user ' WRITE");//Lock ' user ' table
$sql = "INSERT into ' user ' (' id ', ' username ', ' sex ') VALUES (NULL, ' test1 ', ' 0 ')";
$res = mysql_query ($sql);
if ($res) {
Echo ' submitted successfully.! ';
}else{
echo ' Failure! ';
}
mysql_query ("UNLOCK TABLES");//Unlocked

MyISAM is the default storage engine in MySQL, and generally not too many people are concerned about this thing. Deciding what kind of storage engine to use is a very tricky thing to do, but it's worth it to study, the article here only considers the two MyISAM and InnoDB, because these two are the most common.

Let's answer some questions first:
Do you have a foreign key in your database?
Do you need business support?
Do you need full-text indexing?
What query patterns do you often use?
How big is your data?

MyISAM only Index cache

InnoDB index file data file InnoDB buffer

MyISAM can only manage indexes, which are used by the operating system to cache when index data is greater than allocated resources, and data files depend on the operating system's cache. InnoDB, whether indexed or data, is managed by itself

Thinking about these questions can help you find the right direction, but that's not absolute. If you need transaction processing or foreign keys, then InnoDB may be a good way. If you need full-text indexing, then generally speaking, MyISAM is a good choice because it is built in the system, however, we do not actually test 2 million rows of records in a regular manner. So, even slower, we can get full-text indexing from InnoDB by using Sphinx.

The size of the data is an important factor in what kind of storage engine you choose, and large datasets tend to choose the INNODB approach because they support transactional processing and failback. The small database determines the length of time to recover, and InnoDB can use the transaction log for data recovery, which is faster. While MyISAM may take hours or even days to do these things, InnoDB only takes a few minutes.

Your habit of manipulating database tables can also be a factor that has a significant impact on performance. For example, COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. While the primary key query will be quite fast under InnoDB, it is important to be careful that if our primary key is too long it can cause performance problems. A large number of inserts statements will be faster under MyISAM, but updates will be faster under innodb-especially when concurrency is large.

So, which one do you use to check? From experience, if it is a small application or project, then MyISAM may be more appropriate. Of course, the use of MyISAM in large-scale environments can be a great success, but it's not always the case. If you are planning to use a project with a large amount of data and require transactional or foreign key support, then you should really use the InnoDB method directly. But it is important to remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables may make you have a very bad experience.

===========================================================

MyISAM: This is the default type, which is based on the traditional ISAM type, ISAM is an abbreviation for Indexed sequential access method (indexed sequential access methods), which is the standard way to store records and files. Compared to other storage engines, MyISAM has most of the tools for checking and repairing tables. MyISAM tables can be compressed, and they support full-text search. They are not transaction-safe and do not support foreign keys. If the rollback of a thing causes incomplete rollback, it does not have atomicity. If executing a lot of select,myisam is a better choice.

InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are fast. Has a richer feature than BDB, so it is recommended if a transaction-safe storage engine is required. If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table.

For INNODB types that support things, the main reason for the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call begin to start a transaction, resulting in an automatic commit for each insert, which seriously affects the speed. You can call begin before you execute SQL, and multiple SQL forms a thing (even if the autocommit is open), which will greatly improve performance.

===============================================================

InnoDB and MyISAM are the two most common table types used in MySQL, each with its pros and cons, depending on the application. The following is a known difference between the two, for informational purposes only.

InnoDB
InnoDB provides MySQL with transaction security with transactional (commit), rollback (rollback), and crash-repair capabilities (crash recovery capabilities) (Transaction-safe (ACID compliant )) Type table. The InnoDB provides a row lock (locking on row level) that provides an unlocked read (non-locking read in selects) that is consistent with the Oracle type. These features improve the performance of multi-user concurrency operations. There is no need to widen the lock (lock escalation) in the InnoDB table because the InnoDB column lock (Row level locks) is suitable for very small space. InnoDB is the first table engine on MySQL to provide a foreign key constraint (FOREIGN key constraints).

InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines. Technically, InnoDB is a complete database system placed in the background of MySQL, InnoDB in the main memory to establish its dedicated buffer pool for caching data and indexes. InnoDB the data and index in the table space, may contain multiple files, which is different from other, for example, in MyISAM, the table is stored in a separate file. The size of the InnoDB table is limited only by the size of the operating system file, typically 2 GB.
InnoDB All tables are stored in the same data file Ibdata1 (or possibly multiple files, or stand-alone tablespace files), relatively poorly backed up, the free scheme can be copy data files, backup Binlog, or mysqldump.


MyISAM
MyISAM is the MySQL default storage engine.

Each of the MyISAM tables is stored in three files. The frm file holds the table definition. The data file is MyD (MYData). The index file is an MYI (myindex) extension.

Because MyISAM is relatively simple, it is better than innodb in efficiency. Using MyISAM for small applications is a good choice.

MyISAM tables are saved as files, and using MyISAM storage in cross-platform data transfer saves a lot of hassle

The following are some of the details and the specific implementation differences:

1.InnoDB does not support indexes of type Fulltext.
The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.
4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time.
The 5.LOAD table from master operation has no effect on InnoDB, and the workaround is to first change the InnoDB table to a MyISAM table, import the data and then change it to a InnoDB table, but not for tables that use additional InnoDB features, such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "%aaa%"

Any kind of table is not omnipotent, only appropriate for the business type to choose the appropriate table type, to maximize the performance advantage of MySQL.

===============================================================


Here are some of the links and differences between InnoDB and MyISAM!

1. More than 4.0 mysqld support transactions, including non-max versions. 3.23 requires Max version mysqld to support transactions.

2. If you do not specify a type when creating a table, the default is MyISAM, and transactions are not supported.
You can use the Show CREATE TABLE tablename command to see the type of the table.

2.1 Doing a start/commit operation on a table that does not support transactions has no effect and has been committed before committing a commit, testing:
Perform a msyql:
Use test;
drop table if exists TN;
Create TABLE TN (a varchar (ten)) Type=myisam;
drop table if exists Ty;
Create table Ty (a varchar (ten)) Type=innodb;

Begin
Insert into TN values (' a ');
Insert into Ty values (' a ');
SELECT * from TN;
select * from Ty;
Can see a record.

To perform another MySQL:
Use test;
SELECT * from TN;
select * from Ty;
Only TN can see a record
And then on the other side
Commit
Can only see the record.

3. You can execute the following command to toggle non-transactional tables to transactions (data is not lost), and the InnoDB table is more secure than the MyISAM table:
ALTER TABLE TableName TYPE=INNODB;

3.1 InnoDB table cannot use Repair Table command and MYISAMCHK-R table_name
But you can use check table, and Mysqlcheck [OPTIONS] database [tables]

==============================================================

The use of select for update in MySQL must be for InnoDB and is in one transaction to work.

Select does not have the same conditions, either row-level or table-level locks.
Instructions for turning http://www.neo.com.tw/archives/900

Because the InnoDB preset is Row-level lock, MySQL executes row lock (only the selected data sample) only if the specified primary key is "clear", otherwise MySQL will execute table lock (lock the entire data form).


As an example:

Suppose there is a form of products with ID and name two fields, ID is the primary key.

Example 1: (explicitly specify the primary key, and there is this information, row lock)

SELECT * FROM Products WHERE id= ' 3′for UPDATE;

Example 2: (explicitly specify the primary key, if the information is not found, no lock)

SELECT * FROM Products WHERE id= ' -1′for UPDATE;

Example 2: (No primary key, table lock)

SELECT * FROM Products WHERE name= ' Mouse ' for UPDATE;

Example 3: (primary key ambiguous, table lock)

SELECT * FROM Products WHERE id<> ' 3′for UPDATE;

Example 4: (primary key ambiguous, table lock)

SELECT * from the products WHERE id like ' 3′for UPDATE;

Note 1:
For UPDATE only applies to innodb and must be in the transaction block (Begin/commit) to take effect

Paste Code

<?php
$dbhost = ' localhost:3306 '; MySQL Server host address
$dbuser = ' root '; MySQL User name
$dbpass = ' root '; MySQL User name password
$conn = Mysqli_connect ($dbhost, $dbuser, $dbpass);
if (! $conn)
{
Die (' Connection failed: '. Mysqli_error ($conn));
}
Set the encoding to prevent Chinese garbled characters
Mysqli_query ($conn, "Set names UTF8");
mysqli_select_db ($conn, ' blog ');
Mysqli_query ($conn, "SET autocommit=0"); Set to not auto-commit because MySQL defaults to execute immediately
Mysqli_begin_transaction ($conn); Start Transaction definition

$sql 1 = mysqli_query ($conn, "select * from Inn where names= ' 4wtZt ' and snum > + for UPDATE"); For UPDATE Row It lock learn more about watching mysql lock mechanism
$sql 2 = mysqli_query ($conn, "Update inn set snum = snum-800 where names= ' 4wtZt '");
$sql 3 = mysqli_query ($conn, "Update inn set snum = snum+800 where names= ' super brother '");
Sleep (10);//Pause View data table not committed before commit
if (Mysqli_num_rows ($sql 1) >= 1 && $sql 2 && $sql 3)
{
Two verification I test found that if the operation of the user more than the occurrence of data confusion execution transaction is multithreaded SQL1 statement has been executed the wrong place please comment below
if (Mysqli_query ($conn, "select * from Inn where names= ' 4wtZt ' and Snum > 800")) {

Mysqli_commit ($conn);
Echo ' transfer success ';
Echo ' Photo if 2: '. Time (). ' <br/> ';
Mysqli_query ($conn, "UNLOCK TABLES");
}else{
Echo ' transfer failed ';
Mysqli_query ($conn, "ROLLBACK");
}

Determine rollback when execution fails
}else{
Echo ' transfer failed ';
Mysqli_query ($conn, "ROLLBACK");

}

Mysqli_close ($conn);

PHP operation MySQL Transaction instance