What is the impact of ODBC connections to MySQL databases

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 te开发者_开发知识库sting 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.

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.