How to handle concurency and atomic DB actions

Hi,

I'm looking a reliable way to deal with concurrent DB access. So I have few questions.

First, I would need to use many sequences (like Oracle) for a same table. So I've created a "counter" table to handle this :

Table counter(sequence_name, sequence_value)
ex: 
  "sequence1" -> 10
  "sequence2" -> 12
  "sequence3" -> 56

Now I have a "contract" table in which I need to store one of the counters value in a field :

Table contract(id, sequence_number, name)

 


How would you do, to ensure these operations are atomic ?

seq = SELECT sequence_value FROM counter.sequence_value WHERE sequence_name = "sequence1"
UPDATE contract SET sequence_number=seq WHERE id=100
UPDATE counter SET sequence_value=seq+1 WHERE sequence_name = "sequence1"

Goal : If two different users update two different contracts at the same time, each contract must store a correct and different seq number.

I was thinking about doing that such of code in a whole PL/SQL (stored proc) using such a mecanism but as Appian transactional mode is set to AUTO_COMMIT I can not deal with transactions :

START TRANSACTION;

SELECT sequence_number INTO @value
FROM counter
WHERE sequence_name = "sequence1"
FOR UPDATE;

UPDATE CONTRACT

UPDATE counter
SET sequence_number = sequence_number + 1
WHERE sequence_name = "sequence1"

COMMIT;

Is this solution conceivable ? I suppose Appian and DB both configured to avoid locks, so I'm not sure the "SELECT FOR UPDATE" instruction would match as the best solution ?

If I create a single process model with these 3 steps, is it handling these concurrent cases better ?

     START => GET_Counter data  => Write to DB entity Contract => Write to DB entity Counter+1 => END

Lastly, I've read over the Appian forum that MNI could help for such a problem ?