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]
