ODBC 连接对 MySQL 数据库有什么影响
我们有一个使用 MySQL 数据库的在线购物车，并希望使用 Access 2007 通过链接表连接到数据库以创建各种自定义报告。不需要写访问权限。
我目前正在使用旧的 MySQL 数据库进行测试，并使用保持打开状态的全职 ODBC DSN 连接。要编写报告和查询，我需要对数据库的完全访问权限，如果我关闭连接，Access 会抱怨，并且无法按预期获取任何表信息。
对此的一种解决方案是复制活动生产数据库并在其上进行所有开发，然后将 DSN 切换到活动数据库并打开和关闭每个报告/查询的连接。
我的总体问题是：开发/生产数据库方法是否必要？活动购物车 MySQL 数据库上持续打开的连接会出现哪些问题（如果有）？
=艾伦·R.
We have an online shopping cart that uses a MySQL database, and would like to use Access 2007 to connect to the db via Linked Tables to create various custom reports. No write access will be needed.
I'm currently testing with an old MySQL database and using a full-time ODBC DSN connection which is left open. To write reports and queries I need full access to the database and if I close the connection Access complains and I can't get any table info, as expected.
One solution to this is to duplicate the active production database and do all development on it, and then switch the DSN to the active database and open and close the connection for each report/query.
My overall question is: Is the devel/production database approach necessary? What are the problems (if any) of a constantly open connection on an active shopping cart MySQL database?
=Alan R.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
连接不是线程安全的。让多个用户共享一个连接对我来说就像是自找麻烦。
数据库对其可以接受的同时连接数有限制。同时用户给开发人员带来了额外的负担，要求他们在事务之间指定适当的隔离。您需要平衡响应能力和准确性；对隔离和 ACID 进行一些研究。
如果必须保持连接打开，则扩展到更多用户的唯一方法是复制数据库。这带来了确保所有实例之间数据同步的麻烦。
仅当必须执行以下操作时才应保持连接打开：打开连接、执行操作以及关闭连接。
购物车的情况很有趣。另一种方法是让中间层建立会话并在中间层建立事务，而不是始终保持连接打开。仅当完整事务完成且用户点击“购买”按钮时才持久保存到数据库。
Connections are not thread-safe. Having multiple users share a connection sounds like asking for trouble to me.
Databases have a limit on the number of simultaneous connections they can accept. And simultaneous users place additional burdens on developers to specify proper isolation between transactions. You need to balance responsiveness with accuracy; do some research on isolation and ACID.
If you must keep the connection open, the only way to scale to more users is to duplicate the database. This introduces its own headaches to ensure that data is in synch between all instances.
You should keep connections open only as long as you have to: Open the connection, perform the operation, and close the connection.
The shopping cart situation is an interesting one. Rather than keeping the connection open the whole time, another approach would be for the middle tier to establish a session and build up the transaction in the middle tier. Only persist to the database when the full transaction is complete and the user hits the "purchase" button.