From: Gleb Paharenko (gleb.paharenkoensita.net)
Date: Thu Oct 06 2005 - 13:52:52 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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:gleb.paharenkoensita.net]=20
> Sent: Tuesday, October 04, 2005 1:33 AM
> To: mysqllists.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" <jonathan.stockleyquest.com> 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
> / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenkoensita.net
> /_/ /_/\_, /___/\___\_\___/ 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
 / /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenkoensita.net
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
       <___/ www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]