-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Emma Wansbrough wrote:
My code works fine with autcommit on..however it does not support autocommit off. This is quite a big bug, or I have missed something out. Please let me know as I it would be of great interest to me if I was mistaken or correct and you will consider fixing it.
If I switch off auto commit and select rows from a database table in a
loop when I enter new values to the table it will not find them unless I
specifically commit after every select statement. Surely this is select for update( with InnoDB) ? not just an ordinary
select!
This presents awkwardness with multi-threading and I have to do work-around such as not committing if a flag is set which should not be necessary. I have tried afew versions of you JDBC driver and it always performs the same.
You should be able to make simple selects without having to commit, the c api does exactly that!
You will see the exact same behavior with the MySQL command-line client or the C-API if you do the same thing you are doing in Java.
Many people misunderstand how the isolation level of REPEATABLE_READ using consistent snapshotting works with InnoDB.
At the point where you turn off auto_commit in your loop that does SELECTs, InnoDB will make a snapshot of the data at that point in time. Any SELECTs that you do will use that data. Modifications to that data will not be seen until you get a new snapshot by ending the transaction (i.e. calling 'commit'). If you want different transaction semantics, you need to use a different isolation level. See http://www.innodb.com/ibman.html#Implementation for more details....
Here's an example with the MySQL command-line client (you are free to try this yourself):
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 125 to server version: 4.1.0-alpha-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> set autocommit=0; Query OK, 0 rows affected (0.01 sec)
mysql> select * from transExample; +------+ | col1 | +------+ | 1 | +------+ 1 row in set (0.00 sec)
# # At this point, from another connection, # I have inserted the value '2' into the 'transExample' # table...Notice that it doesn't show up, because InnoDB # provides REPEATABLE_READ isolation levels #
mysql> select * from transExample; +------+ | col1 | +------+ | 1 | +------+ 1 row in set (0.00 sec)
mysql> commit; Query OK, 0 rows affected (0.00 sec)
# # Now, this causes InnoDB to generate another 'snapshot' # which means that the new data can be seen... #
mysql> select * from transExample; +------+ | col1 | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
- -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mmma
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.orgiD8DBQE+X4DqtvXNTca6JD8RAhORAJ9niUpZcG544Ejzk3DcT7gP4NKrTACfez8u m8Et5pkAA+hIEtjuri7EYns= =+tU+ -----END PGP SIGNATURE-----
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
