Copy Code code as follows:
mysql_query ("Set autocommit=0");
$list _one = $db->fetch_first ("SELECT * from prizes where id =". $id. " For UPDATE ");
$db->query ("DELETE from prizes WHERE id =". $list _one[' id '));
mysql_query ("commit");
START TRANSACTION, Commit and rollback syntax
Copy Code code as follows:
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [and [No] CHAIN] [[No] release]
ROLLBACK [WORK] [and [No] CHAIN] [[No] release]
SET autocommit = {0 | 1}
The start transaction or BEGIN statement can begin a new transaction. A commit can commit the current transaction, and the change becomes a permanent change. Rollback can roll back the current transaction and cancel its change. The SET autocommit statement can disable or enable the default autocommit mode for the current connection.
Optional work keywords are supported for commit and release, with chain and release clauses. Chain and release can be used to attach control to the completion of a transaction. The value of the COMPLETION_TYPE system variable determines the nature of the default completion.
The AND Chain clause starts a new transaction immediately at the end of the current transaction, and the new transaction has the same isolation level as the transaction just ended. The release clause will cause the server to disconnect from the current client after the current transaction has been terminated. Containing no keywords can inhibit chain or release completion. If the Completion_type system variable is set to a certain value, the chain or release completion can be done by default, at which point no keyword is useful.
By default, MySQL runs in autocommit mode. This means that when you execute a statement that updates (modifies) a table, MySQL stores the update to disk immediately.
If you are using a transaction-safe storage engine (such as InnoDB, BDB, or NDB clusters), you can disable the autocommit mode by using the following statement:
SET autocommit=0;
By setting the autocommit variable to zero and disabling autocommit mode, you must use commit to store the changes on disk, or if you want to ignore the changes made since the transaction started, use rollback.
If you want to disable autocommit mode for a single series of statements, you can use the START TRANSACTION statement:
Copy Code code as follows:
START TRANSACTION;
SELECT @a:=sum (Salary) from table1 WHERE type=1;
UPDATE table2 SET summary=@a WHERE type=1;
COMMIT;
Using the start Transaction,autocommit is still disabled until you use commit or rollback to end a transaction. The autocommit mode is then restored to its original state.
The begin and begin work are supported for initializing transactions as the alias for start transaction. The start transaction is a standard SQL syntax and is the recommended way to start a ad-hoc transaction. The BEGIN statement differs from the use of the BEGIN keyword. Begin keyword can start a BEGIN ... End Compound statement. The latter does not begin a transaction.
You can also start a transaction in the following ways:
START TRANSACTION with consistent SNAPSHOT;
The WITH consistent snapshot clause is used to initiate a consistent read for the storage engine with such functionality. Currently, this clause applies only to InnoDB. The effect of the clause is the same as that of the publication of a start TRANSACTION, followed by a select from any InnoDB table. See section 15.2.10.4, "consistent, non-locked reads."
Starting a transaction causes an implied unlock tables to be executed.
For best results, transactions should only be performed using tables managed by a single transaction storage engine. Otherwise, the following problems occur:
If you are using tables from multiple transaction-safe storage engines (such as InnoDB and BDB), and the transaction isolation level is not serializable, it is possible that when a transaction commits, other in-progress transactions that use the same table will only occur changes that are produced by the first transaction. That is, using a hybrid engine does not guarantee the atomicity of the transaction and can cause inconsistencies. (If mixed engine transactions are not often available, you can set the isolation level to serializable as needed using the set TRANSACTION isolation levels.) ）
If you use a non-transactional security table in a transaction, any changes to those tables are immediately stored, regardless of the state of the autocommit mode.
If you publish a ROLLBACK statement after updating one of the transaction tables in the transaction, a er_warning_not_complete_rollback warning appears. Changes to the Transaction security table are rolled back, but there are no changes to the non-transactional security table.
Each transaction is stored in a binary log in a group block, above the commit. Transactions that are rolled back are not counted into the log. (Exception: Changes to a non-transactional table are not rolled back.) If a transaction that is rolled back includes changes to a non-transactional table, the entire transaction is counted into the log with a rollback statement at the end to ensure that changes to those tables are replicated. ）
You can change the isolation level of a transaction by using the set TRANSACTION isolation levels.
Rollback can run slowly. A rollback can also be made when the user does not explicitly require it (for example, when an error occurs). Therefore, when you rollback explicitly and implicitly (ROLLBACK SQL commands), show processlist displays rolling back in the stage column for the connection.
