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

问题描述

我有一个关于 MySQL 提交和事务的问题.我有几个执行 MySQL 查询的 PHP 语句.我只说以下吗?

I have a question regarding MySQL commits and transactions. I have a couple of PHP statements that execute MySQL queries. Do I just say the following?

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 中，如果没有更改 autocommit 的默认设置，即on"，则不需要显式启动或结束单个查询的事务.如果启用自动提交，InnoDB 会自动将每个 SQL 查询包含在事务中，这相当于 START TRANSACTION;询问;提交；.

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

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 和 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屋！