SGreenunimin.com
Date: Tue Oct 04 2005 - 08:24:37 CDT

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

"Jonathan Stockley" <jonathan.stockleyquest.com> wrote on 10/03/2005
08:55:17 PM:

> 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).
>
>

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

Looking at this set of steps:

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

I wonder if you know before you start how many rows you are going to
insert. I say this because I think your lock contention is on your
sequencing table and that slightly modifying your algorithm can fix your
lock contentions. (I have used similar code in the past but this example
code is not tested)

Assumptions:
1) the rows you want to insert can be prestaged in a temporary table :
tmpInserts
2) there is an auto_increment column in that temporary table that
serializes your new data from 1 to N. (not required but it makes bulk
insertions much easier) : autoid

SELECT numRows := max(autoid)
FROM tmpInserts;

START TRANSACTION;

UPDATE sequences
SET insertbase = id, id = id + numRows;
WHERE tablename = 'targettable';

COMMIT;

/* at this point, you have "reserved" a block
of numbers for your data, it should be safe
to allow other processes to check out their
numbers, too. */

INSERT targettable (idcol, ... columns list ...)
SELECT (autoid + insertBase), ... columns list ...
FROM tmpInserts;

You can wrap the entire thing in an transaction but then you would be in
the same place you are now. It's by holding the lock on your sequences
table for the entire length of time that it takes to peform each insert
that you drive yourself into lock contention. And by "checking out" your
sequencing numbers in bulk and performing your inserts in bulk you use
fewer server-side resources.

just my $.02

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

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