From: Gleb Paharenko (gleb.paharenkoensita.net)
Date: Tue Oct 04 2005 - 03:32:34 CDT

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

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.
>
>
>
>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
 / /|_/ / // /\ \/ /_/ / /__ 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 ]