Re: InnoDB transactions with Connection Pooling
On Mon, Apr 22, 2002 at 09:02:54AM +0300, Heikki Tuuri wrote: Mark, if you do not explicitly do SET AUTOCOMMIT=0 then MySQL automatically calls COMMIT after every SQL statement. Make that: SET AUTOCOMMIT=1 Heikki is probably low on coffee. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 73 days, processed 1,941,489,847 queries (304/sec. avg) - 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
Re: InnoDB transactions with Connection Pooling
Jeremy, - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, April 22, 2002 9:37 AM Subject: Re: InnoDB transactions with Connection Pooling On Mon, Apr 22, 2002 at 09:02:54AM +0300, Heikki Tuuri wrote: Mark, if you do not explicitly do SET AUTOCOMMIT=0 then MySQL automatically calls COMMIT after every SQL statement. Make that: SET AUTOCOMMIT=1 MySQL has AUTOCOMMIT=1 as the default. Thus if you do not explicitly change the value with SET AUTOCOMMIT=0, then MySQL calls commit after each SQL statement. Heikki is probably low on coffee. :-) Jeremy, it is midnight there, morning here :). Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 73 days, processed 1,941,489,847 queries (304/sec. avg) Regards, Heikki - 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
Re: InnoDB transactions with Connection Pooling
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
