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
