I'm not doing any explicit locking-- these are just straight
INSERTs, and there are no gaps in the table (This is MySQL
4.0.20-standard-log, by the way).
As for why, there are several tables which share this PK,
each of which is heavily UPDATEd, and most make use of
INSERT DELAYED. This can't be entirely uncommon: I'm pretty
sure I've seen a similar example in the Cookbook.
----- Original Message -----
To: "Eamon Daly" <edaly@stripped>
Sent: Wednesday, August 24, 2005 12:05 PM
Subject: Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
> "Eamon Daly" <edaly@stripped> wrote on 08/24/2005 12:40:55 PM:
>> We have a table containing just one column that we use for
>> unique IDs:
>> CREATE TABLE `id_sequence` (
>> `id` int(10) unsigned NOT NULL auto_increment,
>> PRIMARY KEY (`id`)
>> ) TYPE=MyISAM
>> Watching 'SHOW FULL PROCESSLIST' and reading the slow query
>> log shows the occasional backlog of locks. Has anyone found
>> significant speed increases or better concurrency by
>> switching over to InnoDB for such a table?
>> Eamon Daly
> So long as you do not have any deletion gaps in your data, there
> shouldn't be any read locks on this table even while you are appending new
> records. Are you locking against reads or writes.
> BTW - is there a great reason why you are generating auto_incremented ID
> values separately from the actual data they identify? I ask this because I
> don't think many people actually USE that kind of table so you may not get
> any responses from your last question.
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine