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,
Jo
