> and incrementing it are 2 different actions that need to be
> somehow grouped), so you still need a transaction. But if you go
> for a HEAP table which you build on application start, the
> overhead should be far less.
The atomicity and isolation comes from the application-side lock. In
effect, that function becomes single threaded, and since that's the ONLY
access to the table, there needn't be any transactional logic on the DB
side.
If you use a HEAP table, you'll have to do some trickiness so that if the DB
server ever goes down the table gets rebuilt with the appropriate values for
the different tables. I don't think the overhead of a MyISAM table is going
to be worth that headache, but I don't know that for sure.
barneyb
> -----Original Message-----
> From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 06, 2004 10:42 AM
> To: CF-Talk
> Subject: Re: CF_MAX_ID - MySQL slowww
>
> Barney Boisvert wrote:
> >
> > The easiest solution is to use a file on the file system,
> though that has
> > problems with scalability. A better solution is probably
> to have a table in
> > your DB like this:
> >
> > Create table keyGenerator (
> > table char(30), /* fixed width for speed */
> > currentMaxKey int unsigned not null,
> > primary key (table)
> > );
> >
> > Then make a function stored in the application scope (if
> you're on CFMX)
> > which takes a table name and returns the next available
> key, incrementing
> > the database's value as well. Inside the function use an
> exclusive named
> > lock so you don't get duplicates returned. Do NOT put the
> call to it inside
> > your CFTRANSACTION block. You you need zero concurrency
> for that table, and
> > transactions are designed to do exactly the opposite.
>
> But you need Atomicity and/or Isolation (reading the last value
> and incrementing it are 2 different actions that need to be
> somehow grouped), so you still need a transaction. But if you go
> for a HEAP table which you build on application start, the
> overhead should be far less.
>
> Jochem
>
>
> --
> I don't get it
> immigrants don't work
> and steal our jobs
> - Loesje
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
