In accounting the transaction table has many records with the same transaction number to identify those credits and debits that must balance.

I need a stored proc that trys to lock if it can a table row holding the next transaction# so if it is not currently locked by someone else I can read, increment, write back the next higher integer.

If it can't lock it must try again, (you get the picture);

Does anyone have a well tested routine for this? Or what are people using for this kind of purpose?

I could create a one to many relation and use the auto-incrementing id of the parent to fill the transactions of the children, but I haven't seen this used in accounting before.

Any input is appreciated.