Mysql commit and transaction

Question

I have a question about mysql transactions and transactions.

I have a couple of php statements that execute mysql queries.

Am I just saying the following?

mysql_query("START TRANSACTION"); //more queries here mysql_query("COMMIT"); 

What exactly will this do? How does this help? For updates, deletions, and inserts, I also found this to block other requests:

 mysql_query("LOCK TABLES t1 WRITE, t2 WRITE"); //more queries here mysql_query("UNLOCK TABLES t1, t2"); 

Will it block other requests regardless of nature or just write / select?

Another question: let's say that one request is launched and blocks other requests. Another request tries to access the locked data - and he sees that it is blocked. How does this happen? Wait until the data is unlocked and re-execute the request? Does it just fail and needs to be repeated? If so, how can I check?

Thanks a lot!

Dennis

Answer 1

In InnoDB, you do not need to explicitly start or complete transactions for single requests unless you change the default autocommit setting, which is "on". If autocommit is enabled, InnoDB automatically encloses each SQL query in a transaction, which is equivalent to START TRANSACTION; query; COMMIT; START TRANSACTION; query; COMMIT; .

If you explicitly use START TRANSACTION in InnoDB with autocommit enabled, then any queries executed after the START TRANSACTION statement will either be executed or all of them will fail. This is useful in banking environments, for example: if I transfer $ 500 to your bank account, this operation should be successful only if the amount has been deducted from my bank balance and added to yours. So in this case, you run something like

 START TRANSACTION; UPDATE customers SET balance = balance - 500 WHERE customer = 'Daan'; UPDATE customers SET balance = balance + 500 WHERE customer = 'Dennis'; COMMIT; 

This ensures that either both requests succeed, or neither, but not one. There are several more examples of using transactions in this post .

In InnoDB, you very rarely have to block entire tables; InnoDB, unlike MyISAM, supports row-level locking. This means that clients do not need to lock the entire table, making other clients wait. Clients should only block the rows they need, allowing other clients to continue accessing the rows they need.

You can learn more about InnoDB transactions here . Your deadlock questions are answered in sections 14.2.8.8 and 14.2.8.9 of the documents. If the query fails, your MySQL driver will return an error message indicating the reason; your application should then reissue the requests, if necessary.

Finally, in your sample code, you used mysql_query . If you are writing new code, stop using the old, slow and obsolete mysql_ library for PHP and use mysqli_ or PDO instead :)