"Jonathan Stockley" <[EMAIL PROTECTED]> 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
