mysql lock table lock row statements share of MySQL transactions

 
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 grammars

 
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 statements can start a new transaction. COMMIT can commit the current transaction, making the change permanent. ROLLBACK can roll back the current transaction and cancel its changes. The SET AUTOCOMMIT statement can disable or enable the default autocommit mode for the current connection.
The optional WORK keywords are supported for COMMIT and RELEASE, as well as CHAIN and RELEASE clauses. CHAIN and RELEASE can be used for additional control over transaction completion. The value of the Completion_type system variable determines the nature of the default completion.
The AND CHAIN clause starts a new transaction immediately after the current transaction ends, and the new transaction has the same isolation level as the one that just ended. After terminating the current transaction, the RELEASE clause causes the server to disconnect from the current client. The inclusion of NO keywords can inhibit CHAIN or RELEASE completion. The NO keyword is useful if the completion_type system variable is set to a fixed value of 1 so that linkage or release can be done by default.
By default, MySQL runs in autocommit mode. This means that when you execute a statement to update (modify) a table, MySQL immediately stores the update to disk.
If you are using a transaction-secure storage engine (such as InnoDB, BDB, or NDB cluster), you can disable autocommit mode with the following statement:
SET AUTOCOMMIT=0;
After you disable autocommit mode by setting the AUTOCOMMIT variable to zero, you must use COMMIT to store the changes to disk, or ROLLBACK if you want to ignore the changes made since the transaction began.
If you want to disable autocommit mode for a single 1 series of statements, you can use the START TRANSACTION statement:

 
START TRANSACTION; 
SELECT @A:=SUM(salary) FROM table1 WHERE type=1; 
UPDATE table2 SET summary=@A WHERE type=1; 
COMMIT; 

Using START TRANSACTION, autocommit is still disabled until you end the transaction using COMMIT or ROLLBACK. The autocommit mode is then restored to its original state.
BEGIN and BEGIN WORK are supported as aliases for START TRANSACTION for transaction initialization. START TRANSACTION is the standard SQL syntax and is the recommended way to start an ad-hoc transaction. The BEGIN statement differs from the BEGIN keyword. BEGIN keyword can launch 1 BEGIN... END compound statement. The latter does not start a transaction.
You can also start a transaction as follows:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
The WITH CONSISTENT SNAPSHOT clause is used to start a read of 1 1 for a storage engine with this capability. Currently, this clause applies only to InnoDB. The effect of this clause is the same as publishing an START TRANSACTION, followed by an SELECT effect from any InnoDB table. See section 15.2.10.4, "non-locked reads due to 1."
Starting a transaction causes an implicit UNLOCK TABLES to be executed.
For best results, transactions should be executed using only the tables managed by a single transaction storage engine. Otherwise, the following problems will occur:
If you are using tables from multiple transaction-secure storage engines (such as InnoDB and BDB), and the transaction isolation level is not SERIALIZABLE, it is possible that when one transaction commits, other ongoing transactions using the same table will only change as a result of the first transaction. That is, using a hybrid engine does not guarantee the atomicity of the transaction and will result in a non-1. (if hybrid engine transactions are not frequent, you can set the isolation level to SERIALIZABLE using SET TRANSACTION ISOLATION LEVEL as needed.)
If you use non-transaction-secure tables in a transaction, any changes to those tables are immediately stored, regardless of the state of the autocommit schema.
If you publish an ROLLBACK statement after updating one of the transaction tables in the transaction, an ER_WARNING_NOT_COMPLETE_ROLLBACK warning will appear. Changes to the transaction-safe table are rolled back, but there are no changes to the non-transaction-safe table.
Each transaction is stored in a binary log in one chunk, above COMMIT. Transactions that are rolled back are not logged. (exception: changes to non-transactional tables are not rolled back. If one of the rolled back transactions includes changes to non-transactional tables, the entire transaction is logged using an ROLLBACK statement at the end to ensure that changes to these tables are replicated.
You can change the isolation level of a transaction using SET TRANSACTION ISOLATION LEVEL.
Rollback can run slowly. You can also roll back when the user does not explicitly ask for it (for example, when an error occurs). Therefore, when explicitly and implicitly (ROLLBACK SQL command) is rolled back, SHOW PROCESSLIST displays Rolling back in the Stage column for joins.