How to create pl/sql that run efficiently in background ?

We have application on Oracle 10g where 150+users (from multiple branch) concurrently input transaction. The transaction number is centralized, means all user use same transaction number counter.

To avoid the contention that occured, we think of using pl/sql procedure that run in background to do :
- select transaction that not yet has number

• query the last transaction number counter(a column in a table), increment by 1, update the transaction with the number
• commit
• process another transaction

What is the best approach to do this ?
(means a pl/sql that does not cause high CPU utilization, because it will run all day long to look for unnumbered transaction)

Thank you,
xtanto Received on Mon Feb 02 2009 - 21:03:14 CST