Re: Deadlock problem

Chris Dawe <the-labyrinth_at_ntlworld.com> wrote in message news:<rkjb9us8decdo2sf0cd4fru05d22imaujq_at_4ax.com>...  

> Ruud's suggestion of an "id" table is good for scalability - some
> DBMSs are not very good at optimising a MAX(id) from a table and
> actually get more data off disc than is really required.
>
> To minimise locking problems in the "id" table, change the order of
> the statements:
> UPDATE my_ids
> SET next_id = next_id+1
> WHERE category="my_table";
>
> SELECT new_id = next_id-1
> FROM my_ids
> WHERE category="my_table";
> [a]
> INSERT INTO my_table (id, data)
> VALUES (:new_id, :data);
> COMMIT;
>

The big assumption here is that you control all the insert transaction in the database. In general, if there is a redundant data you have to pay for it;-) Received on Tue Mar 19 2002 - 23:52:32 CET