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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
