Craig,
Instead of delaying a second, why not just add a second to the timestamp?
The following should work just fine:
create table tst (ts timestamp primary key, other_stuff varchar(127) not
null);
...
insert into tst
select greatest(now(), max(T.ts) + interval 1 second)
, "value of other_stuff"
from tst T;
I checked that this works in 4.0.16. It even appears to work for inserting
the first row into an empty table. (I think it probably didn't work in
3.x.)
Of course, during a burst, the stored timestamps could be a few seconds
later than the actual time, but, at least the timestamps are unique
and ascending.
You could also keep the timestamp in a separate table and increment it,
using a variable:
update timestamp_table set ts = (@T := greatest(now(), ts + interval 1
second));
Then you can use @T as your generated unique ID in a subsequent
statement, within the same connection. Of course the timestamps
could be out of order, unless you lock your other table or do
everything within a transaction. Maybe this matters, maybe not.
auto_increment is probably still better. With innodb, there is
less locking required.
You can use + 1 instead of + interval 1 second, but it may give
different results some day if MySQL changes the precision of timestamp.
HTH
Bill Easton
> Subject: Re: Unique IDs
> From: Craig Jackson <[EMAIL PROTECTED]>
> To: "Keith C. Ivey" <[EMAIL PROTECTED]>
> Cc: [EMAIL PROTECTED]
> Date: Thu, 12 Feb 2004 11:57:24 -0600
> On Thu, 2004-02-12 at 11:47, Keith C. Ivey wrote:
> > Craig Jackson <[EMAIL PROTECTED]> wrote:
> >
> > > I have a very large web app that uses timestamp for unique IDs.
> > > Everything was rolling fine until we started getting many users per
> > > second, causing some of the unique IDs to not be unique -- users were
> > > being assigned the same timestamp. Since the web app is so large we
> > > don't want to change the method of assigning IDs as it would create a
> > > major project.
> >
> > I don't understand. If you're getting many users per second, and
> > your timestamps have 1-second resolution, how could you possibly
> > solve the problem without changing the method of assigning IDs?
> > Are the "many users per second" periods just short bursts, and you're
> > really only getting several hundred users per day? If so, I guess
> > you could keep waiting a second and trying the insert again, but that
> > could lead to indefinite delays if traffic gets high. I think you've
> > got to bite the bullet and change the unique ID to something that's
> > actually unique -- even an AUTO_INCREMENT would work.
> Thanks for the speedy reply and I have already recommended
> auto_increment for the solution. We do need that quick fix until the
> problem is fixed. How would I go about making Mysql wait one second
> between inserts. We only get about 1000 hits per day, but they tend to
> be concentrated in short time intervals.
> >
> > --
> > Keith C. Ivey <[EMAIL PROTECTED]>
> > Tobacco Documents Online
> > http://tobaccodocuments.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
