Thanks for the help. I finally got to the bottom of the problem. It seems that on Windows, by default, if the network cable is momentarily disconnected, Windows shuts down all TCP connection within the box and basically disables the the network interface. However the MySQL server running on another host (or any other server for that matter) is blissfully unaware that this has happened so the MySQL Server end of the connection continues to hold the X lock on the record (remember I'm using innodb tables). When the network cable gets reinserted the interface comes back up. However when you rerun the application it will get 1205 errors when it tries to do the same work as the orphaned MySQL server connection is still holding the X lock. The only way out of this is to kill the orphaned session.
The issue with windows is described here: http://www.support.microsoft.com/default.aspx?scid=kb;en-us;239924 However similar problems will occur if a client host crashed (loss of power etc). Any suggestions as to how to mitigate this? Jo -----Original Message----- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 04, 2005 1:33 AM To: mysql@lists.mysql.com Subject: Re: Lock wait timeout exceeded Hello. >Any ideas where to go with this? How can I find out which session is >holding the lock and what lock it is? SHOW INNODB STATUS and, probably, SHOW PROCESSLIST can give you some additional information about what's going on. Use: show variables like 'tx_isolation'; to find out the transaction isolation level. See: http://dev.mysql.com/doc/mysql/en/InnoDB_Monitor.html http://dev.mysql.com/doc/mysql/en/show-processlist.html "Jonathan Stockley" <[EMAIL PROTECTED]> wrote: >Hi, > >We're having a problem with "lock wait timeout exceeded" errors. We are >exclusively using innodb tables apart from the mysql database. The >problem seems to be with the way we are simulating sequences. > > > >There is a table called SEQUENCES defined as follows: > > > >CREATE TABLE IF NOT EXISTS Sequences > >( > > tableName VARCHAR(64) NOT NULL PRIMARY KEY, > > id INTEGER UNSIGNED NOT NULL > >) > > > >We then generate the next number for a given table as follows: > > > >UPDATE Sequences SET id=LAST_INSERT_ID(id+1) WHERE tableName = >'THE_TABLE_NAME' > > > >There are several hundred rows in the Sequences table. > > > >The general flow is that for each row or set of rows to be inserted we >do the following: > >(AUTOCOMMIT is turned OFF). > > > >1. begin transaction >2. get next sequence number for given target table using above >UPDATE statement. >3. insert row into target table >4. if more rows to insert go to step 2 >5. commit transaction > > > >We are not using LOCK TABLE anywhere and we are using the default >transaction isolation level which I believe is READ-COMMITED. > > > >Every so often we get the 1205 error "lock wait timeout exceeded". > > > >Any ideas where to go with this? How can I find out which session is >holding the lock and what lock it is? > > > >Thanks, > >J -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
