John, MySQL runs by default in the auto-commit mode. In the auto-commit mode you do not need to write the COMMIT after each statement.
But if you do SET AUTOCOMMIT=0, then you have to call commit explicitly. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -----Original Message----- From: John Kemp <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Wednesday, January 09, 2002 10:38 PM Subject: Re: InnoDB : Lock wait timeout exceeded; Try restarting transaction >Heikki, > >Hmm. That's interesting. So if you do a single command, say > >INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ; > >You actually need to write (I'm not sure of the exact transactional >syntax for Mysql) - > >BEGIN ; --begin a transaction >INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ; >COMMIT ; -- end a transaction > >Even for a single statement with a single row update/insert? > >That's not something that is generally necessary with other RDBMSs. > >Can you confirm that? > >Thanks, > >John > >Heikki Tuuri wrote: > >> Hi! >> >> Looks like your are not committing your transactions. Every UPDATE and >> INSERT automatically sets row level locks, which are only removed when you >> do a COMMIT or ROLLBACK. >> >> Or you have set innodb_lock_wait_timeout too small in my.cnf. >> >> InnoDB does not set table level locks. Only LOCK TABLES sets table level >> locks. >> >> You can use innodb_lock_monitor to make the mysqld server program print >> (somewhat cryptic) information about who has locks and where. See the InnoDB >> manual at http://www.innodb.com/ibman.html >> >> 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 >> >> .......... >> >> Hi SAm, >> >> I actually had a similar problem myself, but was unable to prove it was >> the persistent connection itself causing this. I'm wondering if this >> means that INNODB thinks that a connection that is now 'sleeping' (ie. >> where a connection was created, used, but is now unused but still open) >> might be locking the whole table erroneously for some reason? Which >> version are you using? I could not figure out why Innodb would think the >> table was locked, other than if someone specifically said 'LOCK TABLE' >> in a query, which wasn't the case. >> >> Any thoughts? >> >> John >> >> >> >> Sam Lam wrote: >> >> >>>I recently switched to InnoDB & persistent connections from PHP. >>> >>>Lately I've been getting these errors "Lock wait timeout exceeded; Try >>>restarting transaction" on an UPDATE on table. The system is in >>>development so there is at most one other user ( a back end Perl script). >>> >>>When I switched PHP back to non-persistent connections I stopped getting >>>that error. >>> >>>How does one use persistent PHP connections & InnoDB to avoid this error ? >>> >>> >> >> >> >> >> --------------------------------------------------------------------- >> 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 >> >> . >> >> > --------------------------------------------------------------------- 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
