PHP and MYSQL transaction processing, PHPMYSQL transaction processing
/*
There are two main methods for MYSQL transaction processing.
1. Use begin, rollback, and commit to implement
begin starts 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, if you submit a QUERY, it will be executed directly! We can implement transaction processing by
set autocommit=0 to disable automatic submission
set autocommit=1 and enable automatic submission
.
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!
I 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_%';), check InnoDB for 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')";//This I made a mistake on purpose
$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 submit it yourself using the commit statement
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1 ', '0')";
$sql2 = "INSERT INTO `user` (`did`, `username`, `sex`) VALUES (NULL, 'test1', '0')";//This I made a mistake on purpose
$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 the table for 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 mode 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 triggered by the operating system To cache; data files rely on the operating system's cache. 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 what 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 greatly affects 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, which is based on the traditional ISAM type. ISAM is Indexed Sequential Access Method (indexed sequential access method) Abbreviation for , it is a standard method of storing records and files. 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 are not transaction-safe. Foreign keys are not supported. 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. Has richer features than BDB, so if a transaction is needed It is a safe storage engine and 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, it will affect the speed. The main reason is that the default setting of AUTOCOMMIT is on, and the program does 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. The free solution can be to copy the data file, Back up binlog, or use mysqldump.
MyISAM
MyISAM is the default storage engine for 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, which can be used for cross-platform data transfer Using MyISAM storage will save a lot of 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 only needs to simply read and save them. The number of rows is enough. 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 additional InnoDB features (such as external Key) tables are 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 “�a%”
No table is omnipotent. Only by choosing 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 defaults to myisam and 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; Can see a record
Execute another mysql:
use test;
select * from tn;
select * from ty;
Only tn can see a record
Then on the other side
commit;
Everyone can see the records.
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 within a transaction to work.
The conditions for select 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 only execute if the primary key is "explicitly" specified. Row lock (only lock the selected data 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 the data is not found, there is 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: (Unclear primary key, table lock)
SELECT * FROM products WHERE id< ;>'3′ FOR UPDATE;
Example 4: (primary key is not clear, table lock) >Note 1:
FOR UPDATE is only applicable to InnoDB and must be in the transaction block (BEGIN/COMMIT) to take effect
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
