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)
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;
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 ?
Discussion posts and replies are publicly visible
You have some interesting requirements here. Did you think about a stored procedure?
Hi Stefan,Yes it was the first solution I've suggested above (PL/SQL)
When calling a stored procedure, I assume that Appian will not do any transaction control. This makes only sense for simple inserts and updates.
Yes I've verified, our Appian DB is set in AUTO COMMIT mode.- If my stored Proc, does just this : SELECT Counter, UPDATE contract, UPDATE Counter,
I'm afraid that 2 different contracts may stored an identical "sequence_number".
- So I was thinking about using "SELECT FOR UPDATE" command to book the counter.
In that case, I wast asking me how would an Appian process would act ?
Does the concurrent process will wait for this resource (and retry?) ?
Does it would throw an error ?
Appian does not know about any concurrency in process models. I do not have any good ideas, but this seems like a problem to be solved inside the database.
Thank you Stefan, maybe I have to make some concurrency tests, to see how Appian deals with this kind of stored procedure.