-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Daniel Gaddis wrote:
> Can you help set me straight?
>
> I recently upgraded from coldfusion 5 to coldfusion mx 7. In the process
> I also switched from odbc to jdbc connections to mysql
> (4.0.21-nt-max-log).
>
> From looking at the query log, I'm now getting...
>
> SHOW VARIABLES
>
> ...once per connection
>
> followed by...
> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
>
> -> followed by one or more...
> ^ SELECT
> ^
> ^ followed by...
> ^ SET autocommit=1
> ^ SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
> ^
> <- this pattern repeats until the final Quit for the connection.
>
> I can include actual entries from the query log if you would like to see
> them.
>
> Is SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED applicable to
> MyISAM tables? I am not using transactions and InnoDB or BDB tables.
> Since I am not using transactions is this statement irrelevant (and
> unneeded)?
>
> I'm also a little surprised by the SET autocommit=1. The manual says...
>
> The other non-transactional storage engines in MySQL Server (such as
> MyISAM) follow a different paradigm for data integrity called ``atomic
> operations.'' In transactional terms, MyISAM tables effectively always
> operate in AUTOCOMMIT=1 mode. Atomic operations often offer comparable
> integrity with higher performance.
>
> and...
>
> By default, MySQL runs with autocommit mode enabled. This means that as
> soon as you execute a statement that updates (modifies) a table, MySQL
> stores the update on disk.
>
> and...
>
> Note that if you are not using transaction-safe tables, any changes are
> stored at once, regardless of the status of autocommit mode.
>
> So it would seem that the SET autocommit=1 commands being seen in the
> query log are not needed. I checked and autocommit is enabled (not that
> it would seem to matter since I'm using MyISAM tables)...
>
> mysql> SELECT @@AUTOCOMMIT;
> +--------------+
> | @@autocommit |
> +--------------+
> | 1 |
> +--------------+
>
> Am I right that neither
>
> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
> SET autocommit=1
>
> are appropriate/needed for MyISAM tables?
>
> If that is true, then I guess my next step is to try and find out why
> coldfusion mx 7 is issuing them to MySQL when using MyISAM tables.
Daniel,
All of those items are _required_ by the JDBC specification. Some of
them are being set by the JDBC driver when you create a new connection
(i.e. "SET autocommit=1"), others by your connection pool (most likely,
for things like SET SESSION TRANSACTION ISOLATION LEVEL, as the JDBC
spec requires connections newly created or being taken from a pool to be
in a certain state).
Also, notice that unless your application is under _extreme_ load, none
of these SET queries will are likely to have an impact on the
performance of your application.
There's not an easy way to _not_ do them, as software built on top of
JDBC expects those values to be set correctly.
If you're using a newer version of our JDBC driver (3.1.x), you can
always add "useLocalSessionState=true" to avoid having to do _some_ of
these queries to the database. You'll also want to have your connection
pool hold on to connections for some short amount of time so there's a
potential for re-use, instead of creating a new connection every time.
The "show variables" query is used by the JDBC driver to configure
various internal things based on what version of MySQL the driver is
connected to, and how you've chosen to configure the server. It can't be
avoided. Once again, if you're using version 3.1.x of the driver, you
can put "cacheServerConfiguration=true" in your JDBC URL, and the values
from this query will be cached, however if you reconfigure your database
server, you'll need to restart your appserver to pick up the new values.
-Mark
- --
Mark Matthews
MySQL AB, Software Development Manager - Connectivity
www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCzXXntvXNTca6JD8RAq/IAKCQUdX3XxdWGt4232QL1DIGHDwXUACfSzr1
+W6uZKDy+35vYvPBDmrBsJw=
=ZtjH
-----END PGP SIGNATURE-----
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
