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.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

"Eamon Daly" <[EMAIL PROTECTED]> 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: <[EMAIL PROTECTED]>
> To: "Eamon Daly" <[EMAIL PROTECTED]>
> Cc: <mysql@lists.mysql.com>
> Sent: Wednesday, August 24, 2005 12:05 PM
> Subject: Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
> 
> 
> > "Eamon Daly" <[EMAIL PROTECTED]> 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 
> 

Reply via email to