Heikki, Thanks very muich for the explanation. That's an interesting question for the connection modules in Apache/PHP/DBI etc. Perhaps this is a problem with the way the connections are opened by those programs. I hadn't thought of that, so it would definitely be a good thing to test before we say the bug is in Mysql. I already looked for LOCK TABLES in all of our code, and it's not there - anywhere.
Thanks for your help in clearing this up, Cheers, John Heikki Tuuri wrote: > Hi! > > It is a bug if the sleeping connection is in the auto-commit mode. But we > need more information of the problem. If you encounter it, please send the > exact sequence of SQL commands which leads to the problem. > > You may also test > > SET AUTOCOMMIT=1 > > explicitly in your program. > > Note that LOCK TABLES switches auto-commit off until the table locks have > been released. > > Regards, > > Heikki > > ........... > 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 > > > > > --------------------------------------------------------------------- > 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
