Re: automatically calculating ID field on INSERT

Hi!

Ruud de Koter wrote:
>
> Hi William,
>
> William Boyle wrote:
> >
> > Unfortunately, this makes the assumption that there has never been a
> > higher id in the table than currently exists, which under some
> > situations could be a mistake. Oracle has sequence numbers which
> > automatically increment, although this feature is not necessarily
> > available on other DBMS products. Without such a facility, this is a
> > difficult problem to solve with a single SQL statement. If you can
> > execute it within a transactional block of statements, then an
> > alternative is to use an ID table which one updates (increment the id
> > value) and extracts the new value before inserting the new row into
> > MyTable. IE:
> >
> > IdTable has 2 columns: tablename (char or varchar) and id (number or
> > int)
> >
> > update IdTable set id = id + 1 where tablename = 'MyTable';
> > insert into MyTable (id, a, b, c, ...)
> > values ((select id from IdTable where tablename='MyTable'),
> > new_a, new_b, newc, ...);
> > commit;
> >
> > William Boyle
>
> Though this approach will work in a lot of situations, is main
> drawback is that in the end it lacks scalability. For every insert
> being done the IdTable will be locked, and other transactions
> will have to wait till the transaction commits. For small to medium
> systems this will be fine, but for real large systems this will
> become a bottleneck.
>
> The logical question now is: what 's the alternative. Well, not an
> easy one to construct in SQL. I 'm afraid that would be something
> distributed yet synchronized. On the other hand, it doesn't hurt to
> know the limitations of a given approach.

Well how about only incrementing the id and getting the new value in one transaction and then do the insert in another. As long as you fetch all the needed ids before you start your transaction, no significant blocking should occur.

Regards,
Heinz Received on Fri Jun 23 2000 - 00:00:00 CEST