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
