MySQL transactions

MySQL transactions

MySQL transactions are primarily used to process data with high operational volumes and complexity. For example, in a personnel management system, you delete a person, you need to delete the basic information of the person, but also to delete information related to that person, such as mailboxes, articles, etc., so that these database operating statements constitute a transaction!

• In MySQL, transactions are supported only by databases or tables that use the Innodb database engine.
• Transactions can be used to maintain the integrity of the database and ensure that batches of SQL statements are executed either in full or not at all.
• Transactions are used to manage insert, update, delete statements.

In general, transactions must meet four conditions (ACID): Atomicity (atomicity or indivisible), Consistency (consistency), Isolation (isolation or independence), Durability (persistence)

• 1, atomicity: A set of transactions, either successful or withdrawn, that is, the transaction error in the execution process will be rolled back to the state before the transaction began.
• 2. Consistency: The integrity of a database is not compromised either before or after a transaction begins or ends. The data written must therefore be fully compliant with all preset rules (data accuracy, concatenation, and subsequent databases are able to do the scheduled work spontaneously).
• 3, isolation: the database allows multiple transactions to be accompanied by read and write modification of its data and other operations, isolation can prevent multiple transactions from being executed in a time of cross-execution resulting in data insanity. Transaction isolation can be divided into Read uncommitted (read not committed), Readcommitted (read commit), Repeatable read,serialize.
• 4, persistence: the transaction after the end of processing of the data to make changes are permanent, can not be lost.

The transaction control statement

1, explicitly starting a transaction:

start transaction

Or

begin

2, do save points, a transaction can have more than one save point:

savepoint 保存点名称

3, commit the transaction and make all changes made in the database permanent:

commit

Or

commit work

4, roll back the end of the user's transaction and undo all pending uncommitted modifications:

rollback

Or

rollback work

5. Delete the save point of a transaction, if no save point is specified, the statement operation will be thrown wrong.

release savepoint 保存点名称

6. Roll the transaction back to the marker point:

rollback to 标记点

7. Set the isolation level of the transaction. The InnoDB storage engine provides isolation levels for transactions such as READ UNCOMMITTED, READCOMMITTED, REPEATABLE READ, and SERIALIZABLE.

set transaction

The transaction method

1. Use begin, rollback, commit to implement transaction processing.

2. Use set to change MySQL's auto-submission mode.

• set autocommit s 0 (auto-submission is prohibited).
• set autocommit s 1 (auto-submit turned on).

The transaction instance is used in PHP

<?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("ROOLBACK");//判断当执行失败时回滚
}
if(!mysql_query("insert into trans (id) values('4')"))
{
mysql_query("ROOLBACK");//判断执行失败回滚
}
mysql_query("COMMIT");//执行事务
mysql_close($handler);
?>