apache php mysql PHP and MYSQL transaction processing

/*
There are two main methods of transaction processing in MYSQL.
1. Use begin, rollback, and commit to implement
begin to start a transaction
rollback transaction rollback
commit transaction confirmation
2. Use set directly to change the automatic submission mode of MySQL
MYSQL automatically submits by default, that is, you submit A QUERY and it will be executed directly! We can use
set autocommit=0 to disable automatic submission
set autocommit=1 to enable automatic submission
to implement transaction processing.
When you use set autocommit=0, all your subsequent SQL will be processed as transactions until you confirm it with commit or rollback.
Note that when you end this transaction, you also start a new transaction! According to the first method, only the current one is used as a transaction!
Personally recommend using the first method!
Only INNODB and BDB type data tables in MYSQL can support transaction processing! Other types are not supported!
***: Generally, the default engine of MYSQL database is MyISAM. This engine does not support transactions! If you want MYSQL to support transactions, you can modify it manually:
The method is as follows: 1. Modify the c:appservmysqlmy.ini file, find skip-InnoDB, add # in front, and save the file.
2. Enter: services.msc during operation to restart the mysql service.
3. Go to phpmyadmin, mysql->show engines; (or execute mysql->show variables like 'have_%';), and check that InnoDB is YES, which means the database supports InnoDB.
This means that transaction transactions are supported.
4. When creating a table, you can select the InnoDB engine for the Storage Engine. 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; to 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 encoding;
//Start a transaction
mysql_query("BEGIN"); //Or mysql_query("START TRANSACTION");
$ sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";
$sql2 = "INSERT INTO `user` (`did`, `username`, `sex`) VALUES (NULL, 'test1', '0')";//I intentionally wrote this wrong
$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");
/**************** transaction--2 *******************/
/*Method 2*/
mysql_query("SET AUTOCOMMIT=0"); //Set mysql not to submit automatically, you need to use it yourself Commit statement submission
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";
$sql2 = "INSERT INTO `user` (`did`, `username`, `sex`) VALUES (NULL, 'test1', '0')";//I deliberately wrote this wrong
$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"); //Don't forget to mysql_query("SET AUTOCOMMIT=1"); automatically submit when the transaction is completed
/******************For MyISAM engine databases that do not support transactions, you can use the table locking method:*************************/
//MyISAM & InnoDB are supported,
/*
LOCK TABLES can lock tables used by the current thread. If the table is locked by another thread, blocking occurs until all locks can be acquired.
UNLOCK TABLES releases any locks held by the current thread. When a thread issues 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 the `user` table
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL , 'test1', '0')";
$res = mysql_query($sql);
if($res){
echo 'Submission successful. !';
}else{
echo 'Failure!';
}
mysql_query("UNLOCK TABLES");//Unlock

MyISAM is the default storage engine in MySQL. Generally speaking, not many people care about this thing. Deciding what storage engine to use is a very tricky thing, but it is still worth studying. This article only considers MyISAM and InnoDB, because these two are the most common.​
Let us answer some questions first: ​
◆Does your database have foreign keys?​
◆Do you need transaction support?​
◆Do you need full-text indexing?​
◆What kind of query pattern do you often use?​
◆How big is your data?
myisam only has index cache
innodb does not distinguish between index files and data files innodb buffer
myisam can only manage indexes. When the index data is larger than the allocated resources, it will be cached by the operating system; the data file depends on the cache of the operating system. Innodb manages both indexes and data by itself
Thinking about the above questions can help you find the right direction, but that is not absolute. If you need transaction processing or foreign keys, then InnoDB may be a better way. If you need full-text indexing, then MyISAM is usually a good choice because it is built into the system. However, we don't actually test 2 million rows of records very often. So, even if it's a little slower, we can get a full-text index from InnoDB by using Sphinx.
The size of the data is an important factor that affects which storage engine you choose. Large-size data sets tend to choose InnoDB because it supports transaction processing and failure recovery. The size of the database determines the length of fault recovery time. InnoDB can use transaction logs for data recovery, which will be faster. While MyISAM may take hours or even days to do these things, InnoDB only takes a few minutes.​
Your habit of operating database tables may also be a factor that has a great impact on performance. For example: COUNT() will be very fast on MyISAM tables, but may be painful on InnoDB tables. The primary key query will be quite fast under InnoDB, but we need to be careful that if our primary key is too long, it will also cause performance problems. A large number of inserts statements will be faster under MyISAM, but updates will be faster under InnoDB - especially when the amount of concurrency is large.​
So, which one should you use? According to experience, if it is some small applications or projects, then MyISAM may be more suitable. Of course, there are times when using MyISAM in large environments is very successful, but this is not always the case. If you are planning to use a very large data volume project and need transaction processing or foreign key support, then you should really use InnoDB directly. But you need to remember that InnoDB tables require more memory and storage, and converting a 100GB MyISAM table to an InnoDB table may give you a very bad experience.
==================================================== ==========
MyISAM: This is the default type. It is based on the traditional ISAM type. ISAM is the abbreviation of Indexed Sequential Access Method. It is used to store records and files. Standard approach. Compared to other storage engines, MyISAM has most of the tools to check and repair tables. MyISAM tables can be compressed, and they support full-text search. They are not transaction-safe, and they do not support foreign keys. If things are rolled back, it will cause an incomplete rollback and is not atomic. If you perform a large number of SELECTs, MyISAM is a better choice.
InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, they also support foreign keys. InnoDB tables are fast. It has richer features than BDB, so if you need a transaction-safe storage engine, It is recommended to use it. If your data performs a large number of INSERT or UPDATE, for performance reasons, you should use InnoDB tables.
For InnoDB type standards that support things, the main reason that affects the speed is that the default setting of AUTOCOMMIT is turned on. Moreover, the program did not explicitly call BEGIN to start the transaction, resulting in automatic Commit for each inserted item, seriously affecting the speed. You can call begin before executing the SQL. Multiple SQLs form one transaction (even if autocommit is turned on), which will greatly improve performance.
==================================================== ==============
InnoDB and MyISAM are the two most commonly used table types when using MySQL. Each has its own advantages and disadvantages, depending on the specific application. The following are the known differences between the two, for reference only.
innodb
InnoDB provides MySQL with transaction-safe (ACID compliant) tables with transaction (commit), rollback (rollback), and crash recovery capabilities. InnoDB provides row locking (locking on row level) and non-locking read in SELECTs consistent with Oracle types. These features improve the performance of multi-user concurrent operations. There is no need for lock escalation in InnoDB tables because InnoDB's row level locks fit into very small spaces. InnoDB is the first table engine on MySQL to provide foreign key constraints (FOREIGN KEY constraints).​
InnoDB is designed to handle large-capacity database systems, and its CPU utilization is unmatched by other disk-based relational database engines. Technically, InnoDB is a complete database system placed on the MySQL background. InnoDB establishes its own dedicated buffer pool in main memory for caching data and indexes. InnoDB stores data and indexes in tablespaces, which may contain multiple files, unlike others. For example, in MyISAM, tables are stored in separate files. The size of an InnoDB table is limited only by the file size of the operating system, which is typically 2 GB.
All tables in InnoDB are stored in the same data file ibdata1 (it may also be multiple files, or independent table space files). It is relatively difficult to back up. Free solutions can be to copy data files and backup binlog , or use mysqldump.
MyISAM
MyISAM is the default storage engine of MySQL.
Each MyISAM table is stored in three files. The frm file stores table definitions. The data file is MYD (MYData). The index file is an extension of MYI (MYIndex).
Because MyISAM is relatively simple, it is better than InnoDB in efficiency. It is a good choice for small applications to use MyISAM.
MyISAM tables are saved in the form of files. Using MyISAM storage in cross-platform data transfer will save a lot of time. Trouble
The following are some details and specific implementation differences:
1.InnoDB does not support FULLTEXT type indexes.
2. InnoDB does not save the specific number of rows in the table. That is to say, when executing select count(*) from table, InnoDB has to scan the entire table to calculate how many rows there are, but MyISAM simply reads out the saved rows. Just count. Note that when the count(*) statement contains a where condition, the operations of the two tables are the same.
3. For fields of type AUTO_INCREMENT, InnoDB must contain an index for only this field, but in the MyISAM table, a joint index can be established with other fields.
4.When DELETE FROM table, InnoDB will not re-create the table, but will delete it row by row.
5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, and then change it to an InnoDB table after importing the data. However, for the additional InnoDB features (such as foreign keys) used, The table is not applicable.
In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table, for example, update table set num=1 where name like “%aaa% ”
No table is omnipotent. Only by selecting the appropriate table type appropriately for the business type can the performance advantages of MySQL be maximized.
==================================================== ==============
The following are some connections and differences between InnoDB and MyISAM!​
1. Mysqld 4.0 and above supports transactions, including non-max versions. 3.23 requires the max version of mysqld to support transactions.
2. If type is not specified when creating a table, it will default to myisam, which does not support transactions.​
You can use the show create table tablename command to see the table type.
2.1 The start/commit operation on a table that does not support transactions has no effect. It has been submitted before executing commit. Test:
Execute a msyql:
use test;
drop table if exists tn;
create table tn (a varchar( 10)) type=myisam;
drop table if exists ty;
create table ty (a varchar(10)) type=innodb;
begin;
insert into tn values('a');
insert into ty values(' a');
select * from tn;
select * from ty;
Everyone can see a record
Execute another mysql:
use test;
select * from tn;
select * from ty;
Only tn can see it Go to a record
and then commit;
on the other side to see the record.​
3. You can execute the following command to switch non-transactional tables to transactions (data will not be lost). Innodb tables are more secure than myisam tables:
alter table tablename type=innodb;
3.1 The repair table command and myisamchk -r table_name cannot be used for innodb tables
But you can use check table, and mysqlcheck [OPTIONS] database [tables]
==================================== =============================
The use of select for update in mysql must be for InnoDb and in a transaction before it can be used. effect.​
The conditions for selection are different, and whether row-level locks or table-level locks are used are different.
Instructions transferred to http://www.neo.com.tw/archives/900
Since InnoDB defaults to Row-Level Lock, MySQL will execute Row lock only if the primary key is "explicitly" specified (only the locked Selected data (for example), otherwise MySQL will execute Table Lock (lock the entire data form).​
For example: ​
Suppose there is a form products, which has two fields: id and name, and id is the primary key.
Example 1: (Explicitly specify the primary key, and there is this data, row lock)
SELECT * FROM products WHERE id='3′ FOR UPDATE;
Example 2: (Explicitly specify the primary key, if there is no such data, 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: (The primary key is not clear, table lock)
SELECT * FROM products WHERE id LIKE '3′ FOR UPDATE;
Example 4: (Unclear primary key, table lock)
SELECT * FROM products WHERE id LIKE '3′ FOR UPDATE;
Note 1:
FOR UPDATE is only applicable to InnoDB and must be in the transaction block (BEGIN/COMMIT) to take effect

The above introduces apache php mysql PHP and MYSQL transaction processing, including the content of apache php mysql. I hope it will be helpful to friends who are interested in PHP tutorials.