-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 H. Steuer wrote:
Hello MySQL users,I have a weired issue using the MySQL C API and InnoDB tables. An application polls a database every 30 seconds. When the application starts everything seems to be fine. During the running of the application i change some rows, but the application itself doesnt see the changes at all. I tracked down the problem and saw that its only happening if I set autocommit=0. If I run a second mysql shell I can see all changes immediately. Just the application itself doesnt. Here are the important parts of the code without any exception catching. Just wanted to show the steps I did for querying the database.
[snip]
InnoDB takes a consistent 'snapshot' at the beginning of every transaction. This enables the 'I' in the infamous 'ACID' test...which is isolation...Transacations don't 'see' the effects of other transactions until after the others commit. InnoDB runs by default in an 'isolation level' of 'REPEATABLE_READ', which means that the isolation a particular transaction 'sees' remains in effect until that transaction itself has committed. InnoDB accomplishes this through the 'snapshotting' model mentioned above.okay, when running this application does not see the changes to the database - for what reason ever. If I drop the SET AUTOCOMMIT=0 it works fine. When inserting 'mysql_query(conn,"COMMIT;") ' after the mysql_store_result everything seems to work fine, too. But theres only a select statement - nothing that changes data at all. why doesnt the application see the changes without the commit ? I wrote a small test application without any other code, just this simple query - the same result. Can anyone explain this thing to me ? Why is there a COMMIT needed after a select ? When stopping the application and restarting it immediately the changes are visible, too. I dont get any further - hope one of you guys can help. Thanks in advance, Heri
See
http://www.innodb.com/ibman.html#InnoDB_transaction_model
for more detailed information, or consult any handy transaction processing or database textbook ;)
-Mark
- -- 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.org
iD8DBQE96+iotvXNTca6JD8RAhcJAKC/u3ZjbJQYtI6ei94ddaY28jtZPwCguWJW
MDbFde/rPHEy8BGawiVm8Y4=
=1Xjv
-----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
