Latest web development tutorials

MySQL transaction

MySQL transaction processing operation is mainly used for large, high complexity of data. For example, in personnel management system, you remove a person, that is, you need to remove the basic information officer, and the officer also delete related information, such as mailbox, articles, etc., so that the database operation statement constitutes a transaction !

  • Only in the MySQL database engine used Innodb database or table to support transactions
  • Transactions can be used to maintain the integrity of the database to ensure that the bulk of the SQL statements either all executed or not executed all
  • Transaction management for insert, update, delete statement

Generally, the transaction must meet four conditions (ACID): Atomicity (Atomicity), Consistency (stability), Isolation (isolation), Durability (reliability)

  • 1, atomic transaction: a set of transactions, or success; or withdrawn.
  • 2. Stability: illegal data (the foreign key constraint and the like), the withdrawal transaction.
  • 3. Isolation: A transaction independently. Results After a transaction, affects the other transaction, other transactions will be withdrawn. 100% of the transaction isolation, the need to sacrifice speed.
  • 4. Reliability: After software and hardware crash, InnoDB data table-driven Reconstruction will use the log file modification. Reliability and speed can not have both, innodb_flush_log_at_trx_commit options when it decided to save the transaction log.

In Mysql console uses transactions to operate

1, the beginning of a transaction 

start transaction

2, do save point 

savepoint 保存点名称

3, the operation

4, can be rolled back, you can submit, no problem, we submit, there are problems rollback.

PHP used transaction instance 

<?php
$handler=mysql_connect("localhost","root","password");
mysql_select_db("task");
mysql_query("SET AUTOCOMMIT=0");//设置为不自动提交，因为MYSQL默认立即执行
mysql_query("BEGIN");//开始事务定义
if(!mysql_query("insert into trans (id) values('2')"))
{
mysql_query("ROLLBACK");//判断当执行失败时回滚
}
if(!mysql_query("insert into trans (id) values('4')"))
{
mysql_query("ROLLBACK");//判断执行失败回滚
}
mysql_query("COMMIT");//执行事务
mysql_close($handler);
?>