损害控制：2个以上相互依赖的mySQL查询中的1个失败时... [英] Damage Control: When 1 of 2+ Interdependent mySQL Queries Fail...
问题描述
这个问题让我烦恼了一段时间，我觉得我应该给b
咨询小组....
经常写一个php的时候脚本来处理一些mysql DB
事务，我必须编写代码来执行，比如说插入一个
MySQL数据库，然后检索最后一项的索引，并将新的
插入数据库的另一个表中，该表需要先前获得的
获得的索引。两个查询都必须执行并完成 - 如果查询
一个成功并且查询两个失败，我回滚查询一个的更改。
显然，将会当许多相互依赖的查询涉及到
时 - 在这种情况下回滚是一件令人头疼的事，尽管这是一个必要的问题。我们都知道一个强大的网络系统必须有很多错误
检查，并且数据库的完整性必须全部保护
次。
现在，对于一个我无法找到*雄辩*解决方案的大问题
来......
假设在原始示例，查询一个被执行并且
已完成。但是，就在查询二之前，PHP脚本生成了，b / b
整个darn服务器都关闭了。因此，查询一个是完整的，查询
两个从未发生过，当服务器重启时，数据库就是b $ b腐败了！
一个显而易见的，但恕我直言，笨重的解决方案，是使用一组脚本，每隔几小时或几天就可以运行
通过数据库和
验证一切都有意义 - 例如PROFILES中没有行
，其中有一个全局唯一ID，无法在MEMBERS表中找到（
用户有个人资料，但成员中没有基本帐户信息）。当发现问题时，可以自动更正问题，或者可以向管理员发送
警报。
显然，我的解决方案就是维修站。你会做什么？
>经常写一个php脚本来处理一些mysql DB事务，我必须编写执行代码插入MySQL数据库的代码，然后检索最后一项的索引，然后将新的
插入到DB上的另一个表中需要先前获得的索引。两个查询都必须执行并完成 - 如果查询成功并且查询两个失败，我回滚查询一的更改。
当然，您在这里使用交易。
显然，有时会涉及许多相互依赖的查询 - 回滚此类案件令人头疼，虽然是必要的。我们都知道，一个强大的网络系统必须有很多错误检查，并且数据库的完整性必须在所有时间得到保护。
现在，对于大的问题，我找不到*雄辩*解决方案
假设在原始示例中，查询一个已执行并已完成。但是，就在查询二是由PHP脚本生成之前，整个darn服务器都会关闭。因此，查询一个完成，查询两个从未发生过，当服务器重启时，数据库已损坏！
Isn '* UNCOMMITTED *交易应该回滚，如果它实际上在服务器重启时实际上是把它变成了磁盘吗？
我相信带有InnoDB表的MySQL能够做到这一点。
Gordon L. Burditt
Gordon Burditt写道：< blockquote class =post_quotes>通常在编写php脚本来处理某些mysql DB
事务时，我必须编写代码来执行，例如，插入一个
MySQL DB然后检索最后一项的索引，并在DB上的另一个表中插入新的
插入，该表需要先前获得的索引。两个查询都必须执行并完成 - 如果查询一个成功并且查询两个失败，我回滚查询一个的更改。
当然，你在这里使用交易。
显然，有时会涉及许多相互依赖的查询 - 在这种情况下回滚是一件令人头疼的事，尽管是必要的。我们都知道，一个强大的网络系统必须有很多错误检查，并且数据库的完整性必须在所有时间得到保护。
现在，对于大的问题，我找不到*雄辩*解决方案
假设在原始示例中，查询一个已执行并已完成。但是，就在查询二是由PHP脚本生成之前，整个darn服务器都会关闭。因此，查询一个完成，查询两个从未发生过，当服务器重启时，数据库已损坏！
不是一个* UNCOMMITTED *事务应该回滚，如果它实际上是在服务器重启时首先将它转到磁盘上吗？
我相信带有InnoDB表的MySQL能够这个。
Gordon L. Burditt
完美！这是一个自学成才的网络开发人员的问题 -
您的答案可能是DB 101，但我从未上过这个课程！
谢谢，Gordon 。
-B。
有关事务的帮助，请查看ADOdb或ADOdb lite以及
以下文章中关于交易的部分：
http://www.databasejournal.com/featu...le.php/2234861
Tom
tu ***** @ gmail.com 写道：Gordon Burditt写道：通常在编写php脚本来处理某些mysql DB
事务时，我必须编写代码来执行，例如，插入到MySQL数据库中，然后检索最后一个item的索引，并在DB t上的另一个表中插入新的
帽子需要先前获得的索引。两个查询都必须执行并完成 - 如果查询一个成功并且查询两个失败，我回滚查询一个的更改。
当然，你在这里使用交易。
显然，有时会涉及许多相互依赖的查询 - 在这种情况下回滚是一件令人头疼的事，尽管是必要的。我们都知道，一个强大的网络系统必须有很多错误检查，并且数据库的完整性必须在所有时间得到保护。
现在，对于大的问题，我找不到*雄辩*解决方案
假设在原始示例中，查询一个已执行并已完成。但是，就在查询二是由PHP脚本生成之前，整个darn服务器都会关闭。因此，查询一个完成，查询两个从未发生过，当服务器重启时，数据库已损坏！
不是一个* UNCOMMITTED *事务应该回滚，如果它实际上是在服务器重启时首先将它转到磁盘上吗？
我相信带有InnoDB表的MySQL能够这个。
Gordon L. Burditt
完美！这是一个自学成才的网络开发人员的问题 -
你的答案可能是DB 101，但我从来没有参加过这个课程！
谢谢，戈登。
-B。
This problem has been vexing me for some time and I thought I should
consult the group....
Often times when writing a php script to handle some mysql DB
transactions, I must write code that performs, say, an insert into a
MySQL DB, then retrieves the last item''s index, and makes a new
insertion into another table on the DB that requires the previously
obtained index. Both queries must be executed and completed - if query
one succeeds and query two fails, I roll back the changes of query one.
Obviously, there will be times when many interdependent queries are
involved - rolling back in such cases is a headache, albeit a necessary
one. We all know that a robust web system must have a lot of error
checking, and the intergrity of the databases must be protected at all
times.
Now, for the big problem that I cannot find an *eloquent* solution
to...
Suppose that in the original example, query one is executed and
completed. However, just before query two is made by the PHP script,
the whole darn server goes down. Thus, query one is complete, query
two never took place, and when the server is restarted, the database is
corrupt!
An obvious, but IMHO, clunky, solution, is to use a set of scripts that
can be run every few hours or days that go through the database and
verify that everything makes sense - e.g. there is no row in PROFILES
with a globally unique ID that cannot be found in the MEMBERS table (a
user has a profile but no basic account info in members). As
problems are discovered, they can be automatically corrected, or an
alert can be sent to an admin.
Obviously, my solution is the pits. What would you do?
>Often times when writing a php script to handle some mysql DBtransactions, I must write code that performs, say, an insert into a
MySQL DB, then retrieves the last item''s index, and makes a new
insertion into another table on the DB that requires the previously
obtained index. Both queries must be executed and completed - if query
one succeeds and query two fails, I roll back the changes of query one.
Of course, you are using transactions here.
Obviously, there will be times when many interdependent queries are
involved - rolling back in such cases is a headache, albeit a necessary
one. We all know that a robust web system must have a lot of error
checking, and the intergrity of the databases must be protected at all
times.
Now, for the big problem that I cannot find an *eloquent* solution
to...
Suppose that in the original example, query one is executed and
completed. However, just before query two is made by the PHP script,
the whole darn server goes down. Thus, query one is complete, query
two never took place, and when the server is restarted, the database is
corrupt!
Isn''t an *UNCOMMITTED* transaction supposed to be rolled back, if it
in fact ever made it to disk in the first place, on a server restart?
I believe MySQL with InnoDB tables is capable of this.
Gordon L. Burditt
Gordon Burditt wrote:Often times when writing a php script to handle some mysql DB
transactions, I must write code that performs, say, an insert into a
MySQL DB, then retrieves the last item''s index, and makes a new
insertion into another table on the DB that requires the previously
obtained index. Both queries must be executed and completed - if query
one succeeds and query two fails, I roll back the changes of query one.
Of course, you are using transactions here.Obviously, there will be times when many interdependent queries are
involved - rolling back in such cases is a headache, albeit a necessary
one. We all know that a robust web system must have a lot of error
checking, and the intergrity of the databases must be protected at all
times.
Now, for the big problem that I cannot find an *eloquent* solution
to...
Suppose that in the original example, query one is executed and
completed. However, just before query two is made by the PHP script,
the whole darn server goes down. Thus, query one is complete, query
two never took place, and when the server is restarted, the database is
corrupt!
Isn''t an *UNCOMMITTED* transaction supposed to be rolled back, if it
in fact ever made it to disk in the first place, on a server restart?
I believe MySQL with InnoDB tables is capable of this.
Gordon L. Burditt
Perfect! This is the problem with being a self-trained web developer -
your answer is probably DB 101, but I never took that class!
Thanks, Gordon.
-B.
For help with transactions, check out ADOdb or ADOdb lite along with
the section on transactions in the following article:
http://www.databasejournal.com/featu...le.php/2234861
Tom
tu*****@gmail.com wrote:Gordon Burditt wrote:Often times when writing a php script to handle some mysql DB
transactions, I must write code that performs, say, an insert into a
MySQL DB, then retrieves the last item''s index, and makes a new
insertion into another table on the DB that requires the previously
obtained index. Both queries must be executed and completed - if query
one succeeds and query two fails, I roll back the changes of query one.
Of course, you are using transactions here.Obviously, there will be times when many interdependent queries are
involved - rolling back in such cases is a headache, albeit a necessary
one. We all know that a robust web system must have a lot of error
checking, and the intergrity of the databases must be protected at all
times.
Now, for the big problem that I cannot find an *eloquent* solution
to...
Suppose that in the original example, query one is executed and
completed. However, just before query two is made by the PHP script,
the whole darn server goes down. Thus, query one is complete, query
two never took place, and when the server is restarted, the database is
corrupt!
Isn''t an *UNCOMMITTED* transaction supposed to be rolled back, if it
in fact ever made it to disk in the first place, on a server restart?
I believe MySQL with InnoDB tables is capable of this.
Gordon L. Burditt
Perfect! This is the problem with being a self-trained web developer -
your answer is probably DB 101, but I never took that class!
Thanks, Gordon.
-B.
这篇关于损害控制：2个以上相互依赖的mySQL查询中的1个失败时...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
