If you have any deletion gaps in your data, the SQL engine puts a full
TABLE LOCK on your table while performing inserts to a MyISAM table. You
don't have to do it, the engine does it for you.
As to your application design, all I am going to say is that if it works
for you, that's fine by me. I probably wouldn't pick that design for
myself but you seem to have it working so I will "butt out".
As to relieving your locking contentions on this table, switching to
InnoDB may help but you should try the OPTIMIZE TABLE command first to
collapse out any deletion gaps. The only way you will know for sure is to
benchmark both methods. Stick with what works best for your data, on your
hardware, and under your loading.
Unimin Corporation - Spruce Pine
"Eamon Daly" <edaly@stripped> wrote on 08/24/2005 01:49:07 PM:
> 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.
> Eamon Daly
> ----- Original Message -----
> From: <SGreen@stripped>
> To: "Eamon Daly" <edaly@stripped>
> Cc: <mysql@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
> >> 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
> > records. Are you locking against reads or writes.
> > BTW - is there a great reason why you are generating auto_incremented
> > values separately from the actual data they identify? I ask this
> > don't think many people actually USE that kind of table so you may not
> > any responses from your last question.
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine