mysql提交和事务 [英] mysql commit and transaction

问题描述

我对mysql提交和事务有疑问.

I have a question regarding mysql commits and transactions.

我有几个执行mysql查询的php语句.

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

我只说以下话吗?

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

这到底会做什么?它有什么帮助?对于更新，删除和插入，我还发现这阻止了其他查询的读取:

What exactly would this do? How does it help? For updates, deletes and insertions I also found this to block other queries from reading:

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

这会阻止其他任何查询，无论其性质如何还是仅写/选择?

Would this block other queries whatever nature or only writes/selects?

另一个问题:假设一个查询正在运行，并阻止其他查询.另一个查询尝试访问被阻止的数据-并且看到它被阻止了.如何进行?它是否等待直到再次解除阻止数据并重新执行查询?它是否会失败并且需要重复?如果是这样，我该如何检查?

Another question: Say one query is running and blocks other queries. Another query tries to access blocked data - and it sees that it is blocked. How does it proceed? Does it wait until the data is unblocked again and re-execute the query? Does it just fail and needs to be repeated? If so, how can I check?

非常感谢！

丹尼斯

推荐答案

在InnoDB中，如果您未更改自动提交的默认设置("on")，则无需显式启动或结束单个查询的事务.如果启用了autocommit，则InnoDB会自动将每个SQL查询封装在事务中，这等效于START TRANSACTION; query; COMMIT;.

In InnoDB, you do not need to explicitly start or end transactions for single queries if you have not changed the default setting of autocommit, which is "on". If autocommit is on, InnoDB automatically encloses every single SQL query in a transaction, which is the equivalent of START TRANSACTION; query; COMMIT;.

如果您在InnoDB中显式地使用START TRANSACTION并启用了自动提交功能，则在START TRANSACTION语句之后执行的所有查询都将全部执行，否则全部将失败.例如，这在银行业务环境中很有用:如果我将500美元转入您的银行帐户，则只有在从我的银行余额中减去该笔金额并将其添加到您的帐户中之后，该操作才能成功.因此，在这种情况下，您将运行类似

If you explicitly use START TRANSACTION in InnoDB with autocommit on, then any queries executed after a START TRANSACTION statement will either all be executed, or all of them will fail. This is useful in banking environments, for example: if I am transferring $500 to your bank account, that operation should only succeed if the sum has been subtracted from my bank balance and added to yours. So in this case, you'd 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 queries will run successfully, or none, but not just one. This post has some more on when you should use transactions.

在InnoDB中，您很少需要锁定整个表.与MyISAM不同，InnoDB支持行级锁定.这意味着客户端不必锁定整个表，而迫使其他客户端等待.客户端应该只锁定他们实际需要的行，从而允许其他客户端继续访问他们需要的行.

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

您可以在此处了解更多有关InnoDB事务的信息. 14.2.8.8 部分中回答了有关死锁的问题. a>和文档的 14.2.8.9 .如果查询失败，则您的MySQL驱动程序将返回一条错误消息，说明原因；然后，如果需要，您的应用应重新发出查询.

You can read more about InnoDB transactions here. Your questions about deadlocking are answered in sections 14.2.8.8 and 14.2.8.9 of the docs. If a query fails, your MySQL driver will return an error message indicating the reason; your app should then reissue the queries if required.

最后，在示例代码中，您使用了mysql_query.如果您正在编写新代码，请停止使用旧的，缓慢且已弃用的PHP mysql_库，而改为使用mysqli_或PDO:)

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

这篇关于mysql提交和事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！