Walt / Heikki Yes, I agree - with autocommit on, you do not need to supply COMMIT, and yes, that's the same as other RDBMSs. What I'm pointing out though is that I thought Heikki was suggesting that we need to supply the BEGIN/COMMIT for a single command even if autocommit was on, in order to not get the error "Lock wait timeout exceeded; Try restarting transaction". If that's the case, that would indicate that Mysql behaviour would be different to what I would expect.
With auto-commit ON, and no explicit "LOCK TABLES' mentioned, I would not expect to get any error about a lock wait timeout unless the same ROW were being selected as were being updated, inserted or deleted. With a sleeping connection being the second potential row-locker, I would not expect to see this error ever. What I'm saying is that this looks like a bug. Sam mentioned that he only had one other connection when he got this error, and that this connection was a sleeping, persistent connection. The behaviour I saw was the same. A sleeping connection was supposedly locking a row needed (for a delete in my case). I don't think a sleeping connection (ie. one not actually running a query) should ever lock out a running query. I hope that clears up the confusion. John Weaver, Walt wrote: > John wrote: > >>That's not something that is generally necessary with other RDBMSs. >> > > I disagree. I would imagine all transaction-oriented RDBMS's work this way. > Oracle certainly does. You need to do an explicit commit or rollback to > release the lock. (or close the cursor, etc.) > > Unless, of course, autocommit is on. > > --Walt Weaver > Bozeman, Montana > > -----Original Message----- > From: John Kemp [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 09, 2002 1:40 PM > To: Heikki Tuuri > Cc: [EMAIL PROTECTED] > 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 > > --------------------------------------------------------------------- > 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
