Mark, if you do not explicitly do
SET AUTOCOMMIT=0 then MySQL automatically calls COMMIT after every SQL statement. If you set AUTOCOMMIT=0, then you should yourself call COMMIT after each SELECT so that you do not leave a dangling transaction open in the database and that you get a fresh snapshot of the database in each consistent read. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com ----- Original Message ----- From: "Mark Hazen" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, April 22, 2002 3:35 AM Subject: InnoDB transactions with Connection Pooling > Fellow MySQL gurus, > > I am using Apache::DBI to accomplish connection pooling. I am working with > an InnoDB table that gets updated very frequently. My question is this: > Since my connections are pooled and stay open for days at a time, am I > essentially always going to read from that connection the same "version" of > the database (even from request to request). My guess is yes and that I > would need to do a COMMIT before every request (or after). Maybe someone > can shed some light on this... Example: > > Table innodb_test has 2 rows. > > Connection ID 1, Apache Request 1 > SELECT * FROM innodb_test; > > It spits back 2 rows. > > Then some other thread adds 3 rows to the table, and COMMITs them. > > Connection ID 1, Apache Request 2 (notice that it is the same connection, > just a new web page request) > SELECT * FROM innodb_test; > > My guess is that it would spit back the same 2 rows again and not 5. > > I would need to do a COMMIT either before or after each request. Is this > right? Does anyone have an opinion on whether I should do it after or > before. I would assume after because the request could then already be > served to the user (I don't need up-to-the-picosecond results). > > Thanks! > Mark > > --------------------------------------------------------------------- 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
