自动提交被禁用并且永远不会回滚会发生什么？

发布于 2025-01-05 19:37:19 字数 591 浏览 1 评论 0 原文

所以我继承了一个大型的c++代码库，它做了很多mysql工作。 代码始终禁用自动提交，并且往往具有如下所示的函数：

int function() {
    if(mysql_real_query(conn, ...)) {
        return -1;
    }

    if(mysql_real_query(conn, ...)) {
        return -1;
    }

    mysql_commit(conn);
    return 0;
}

显然，这里的意图是仅在查询成功时才会发生提交。但如果其中之一不是，会发生什么情况呢？最终，mysql连接正确关闭，但代码中没有回滚。

那么当它关闭时，它基本上只会提交任何成功的更改吗？还是会像什么都没发生一样回滚？

我的直觉告诉我，如果第二个查询失败，为了“撤消”成功的第一个查询，进行回滚是有意义的。所以这个功能最终是事务性的。

当然，我发现这段代码本质上是被破坏的，因为如果之前的一些工作失败，后来的其他 mysql 代码可能会进行提交，从而使事情处于“奇怪”的状态。但在我去改变程序的行为之前，我想确保我理解当前的行为是什么。

原文

So I've inherited a large c++ code base which does a lot of mysql work. The code always disables autocommit and tends to have functions which look like this:

int function() {
    if(mysql_real_query(conn, ...)) {
        return -1;
    }

    if(mysql_real_query(conn, ...)) {
        return -1;
    }

    mysql_commit(conn);
    return 0;
}

Obviously, the intention here is that the commit only happens if the queries are successful. But what happens if one of them isn't? Eventually, the mysql connection is properly closed, but there is no rollbacks in the code.

So when it closes, will it basically just commit any changes that were successful? Or will it rollback as if nothing happened?

My gut says that it makes sense to have a rollback if the second query fails in order to "undo" the successful first query. So this function ends up be transactional.

Of course, I find this code to be inherently broken because later other mysql code could do a commit leaving things in a "weird" state if some previous work failed. But before I go and change the behavior of the program, I wanted to make sure I understood what the current behavior was.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

女中豪杰 2025-01-12 19:37:19

正如 Marc B 所说，这实际上很容易测试。我希望有人能够指出权威来源，但测试似乎足够合理：

所以无论如何，我尝试了以下代码：

MYSQL *conn = mysql_init(NULL);
mysql_real_connect(conn, host, use, password, database, 0, NULL, 0);
mysql_autocommit(conn, 0);
mysql_query(conn, "INSERT INTO test VALUES(1)");
mysql_query(conn, "INSERT INTO test VALUES(2)");
mysql_query(conn, "INSERT INTO test VALUES(3)");
mysql_query(conn, "INSERT INTO test VALUES(4)");
mysql_close(conn);

足够简单，关闭自动提交，进行一堆插入，并且从不提交。在这种情况下，结果是查询有效回滚。当我查询数据库时，行不存在。

显然，只需在 mysql_close(conn); 之前添加 mysql_commit(conn); 确实会导致创建行。

As Marc B said, this is actually easy enough to test. I was hoping for someone to be able to point at an authoritative source, but testing seems to be reasonable enough:

So anyway, I tried the following code:

MYSQL *conn = mysql_init(NULL);
mysql_real_connect(conn, host, use, password, database, 0, NULL, 0);
mysql_autocommit(conn, 0);
mysql_query(conn, "INSERT INTO test VALUES(1)");
mysql_query(conn, "INSERT INTO test VALUES(2)");
mysql_query(conn, "INSERT INTO test VALUES(3)");
mysql_query(conn, "INSERT INTO test VALUES(4)");
mysql_close(conn);

simple enough, turn off auto-commit, do a bunch of inserts, and never commit. In this case, result is that the queries are effectively rolled back. When I query the DB, the rows are not there.

Obviously simply adding a mysql_commit(conn); right before the mysql_close(conn); does in fact cause the rows to be created.