I'm also quite of this... (developer ;))

And the project is safe in this way, believe me, after more than 8 years with this sequence, no one customer has reported problem (may be some time has occurred) and the simplicity and speed deserve this really little risk (if some error is reported, the user can try "retry" successfully)

Is not a NASA project, and I can live with this risk . Don't deserve a much more hard work, for a quite improbable collision, same fraction of second and same table.

Anyway I'm open to learn an alternative safe, even only for my own knoledge.

--- In firebird-support@yahoogroups.com, "chris.waldmann" <Christian.Waldmann@...> wrote:
>
> As a senior software and database developer, this is my experience:
>
> If anything can go wrong, it will! also known as "Murphy's law"
>
> Happy coding
> Christian
>
> --- In firebird-support@yahoogroups.com, "skander_sp" <skander_sp@> wrote:
> >
> > Is not a problem the concurrence, because this specifica case, only a dozen of user working toghether and not probably they insert in the same milisecond, but thks for the notice.
> >
> > And about the optimized index, I finally undestand, but i hope to get a instant result for last year (actually only used)
> >
> >
> > --- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@> wrote:
> > >
> > > On Tue, Apr 16, 2013 at 3:57 AM, skander_sp <skander_sp@> wrote:
> > >
> > > >
> > > > Simple table
> > > >
> > > > CREATE TABLE ORDENES (
> > > > ID_ORDEN INTEGER NOT NULL /* Primary key - GenId */
> > > > YEAR_ORDEN INTEGER NOT NULL /* Year of Orden */,
> > > > N_ORDEN INTEGER NOT NULL /* Order in the Year */,
> > > > ... /* no matter */
> > > > );
> > > >
> > > > ALTER TABLE ORDENES ADD CONSTRAINT UK_ORDENES UNIQUE (YEAR_ORDEN,
> > > > N_ORDEN)USING DESCENDING INDEX UK_ORDENES_YEAR_N_ORDEN;
> > > >
> > > >
> > > > Now I need to access in the insert trigger, for next order to be assigned
> > > > to N_ORDEN in the YEAR_ORDEN using
> > > >
> > > > select coalesce(max(o.n_orden),0)+1
> > > > from ordenes o
> > > > where o.year_orden=new.year_orden
> > > > into new.n_orden
> > > >
> > > > IT WORK!!!! nice,
> > >
> > >
> > > Not nice at all. It works single user, but it will fail in production, and
> > > you will get violations of your unique constraint. Remember that Firebird
> > > uses MVCC. If two concurrent transactions run the insert trigger they
> > > will generate the same value for new.n_orden.
> > >
> > >
> > >
> > > > BUT NOT OPTIMIZED, in the Performance Analisys it read (indexed) all the
> > > > N_ORDEN in the table, not going to the first (given the descending order of
> > > > the index)
> > > >
> > > > How can optimize the index/query?
> > > > Or simply is not possible doing more?
> > > >
> > > >
> > > Firebird can use an index to optimize MAX, but only if it's the MAX of the
> > > whole key - not a partial key match. If you have a descending index on
> > > year_orden, Firebird will use an index to find the MAX of year_orden by
> > > walking down the left hand side of the index tree. You're asking it to
> > > find a specific year first, then the highest value of n_orden and that
> > > doesn't work. Even if it did, this code doesn't produce good unique
> > > numbers.
> > >
> > > Good luck,
> > >
> > > Ann
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
>