Hello.
Among other things you'll probably do, make a bug report at bugs.mysql.com if the problem so easy to repeat. See: http://dev.mysql.com/doc/mysql/en/bug-reports.html Jonathan Stockley wrote: > 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=3Dkb;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. > > > >>=20 > > > >>There is a table called SEQUENCES defined as follows: > > > >>=20 > > > >>CREATE TABLE IF NOT EXISTS Sequences > > > >>( > > > >> tableName VARCHAR(64) NOT NULL PRIMARY KEY, > > > >> id INTEGER UNSIGNED NOT NULL > > > >>) > > > >>=20 > > > >>We then generate the next number for a given table as follows: > > > >>=20 > > > >>UPDATE Sequences SET id=3DLAST_INSERT_ID(id+1) WHERE tableName =3D > > >>'THE_TABLE_NAME' > > > >>=20 > > > >>There are several hundred rows in the Sequences table. > > > >>=20 > > > >>The general flow is that for each row or set of rows to be inserted we > > >>do the following: > > > >>(AUTOCOMMIT is turned OFF). > > > >>=20 > > > >>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 > > > >>=20 > > > >>We are not using LOCK TABLE anywhere and we are using the default > > >>transaction isolation level which I believe is READ-COMMITED. > > > >>=20 > > > >>Every so often we get the 1205 error "lock wait timeout exceeded". > > > >>=20 > > > >>Any ideas where to go with this? How can I find out which session is > > >>holding the lock and what lock it is? > > > >>=20 > > > >>Thanks, > > > >>J > > > > > --=20 > For technical support contracts, goto > https://order.mysql.com/?ref=3Densita > This email is sponsored by Ensita.NET http://www.ensita.net/ > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET > <___/ www.mysql.com > > > > > -- 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]
