Regarding DB Transaction, and commit and Rollback
Application Type
Mobile, Reactive

Hi,

We have an common block, where we capture data and write to server Entity using CreateandUpdate  EntityAction. But there is a problem sometimes the captured data is not getting write to server table. To elaborate I am writing the steps we are following to.


  1. First we are capturing the  user input records on block.
  2. Assigning the all input value to the respective key, that means we are having input records in key -value pair (one field(key) mapped to one value)
  3. Now on save click, we are passing the block records list to server action to write it into the server table.
  4. Server action is having for-each loop iterate though record and createORUpdate Recrods one by one to server entity and after each record creation/updation, we have kept CommitTrasactions.
  5. The table where we are writing the user input value is very busy table, there are possibilities multiple user at same time will be making records as there are many users using the application
  6. We observed the sometimes User input is not getting inserted to server Entity, this is happening more once more people are using the application 
  7. Just Before writing to server, we have  generated log and we are getting  all the data in logs but in Server Entity its not getting printed.

Can someone please let me know, why data is not getting persisted to server entity when it is printing to log. Is there any solution to overcome this issue, when user is creating records to same Entity parallelly in one by one.


Many Thanks

Dileep

Have you checked if there are any errors in the Error Log in Service Center? Or the Event Logging on the Application Server. 

or does the cycle loop contains a maximum nr of occurences? 


Hi Hans,

There is no error log, we have handled exception and printed the log under exception handler, still no clue-we got.


Dileep

Hi,


Can you share some prints or the OML with us?

Thanks!


Best regards,

Ricardo Pereira

Hi Ricardo,

For more understanding three actions are there to perform the above steps.

  1. On CW module at block save button on click,  making records using key value and sending back to parent Screen through even as below


2-  On Screen handler looping the records and inserting into DB table as below


Now final  Create or update Wrapper , post one record, making CommitTrasaction, still  sometimes data is not getting persisted in Server DB once more users are using parallelly.



Dileep

Hi Dileep,

Is your DB isolation level - Read Committed or Read Uncomitted?

Is 'Abort Transactions' set to Yes or No in all your exception handling?

Regards,

AJ

Hi AJ,

Sorry, I didn't get you exactly as I am little weak on that side, it would be very helpful if you can elaborate little bit or send us any link or oml regarding your points.

Also Abort Trasactions ,I have not used to anywhere


Dileep

No worries, 

For the exception handling I was referring to this setting but I do not see any exception handling in the screenshots you just shared so probably a moot question at this point.

As for the DB isolation setting question, I was referring to settings best explained in this article.

Based on your screenshots now I have another question, why do you choose to explicitly commit as shown below, because the End node will do a commit implicitly for you anyway. Could you remove/disable the commit and test things out?

Thanks for the Clarifications AJ,

Actually few days back only we had kept CommitTrasactions, before that it was without explicit commit, still the few data was getting lost

We thought data are getting Rolledback,so we kept CommitTrasactions at the end of the node.

I see, so when you had no explicit commit, did you see any error logs? If that was not investigated, I would suggest going back to that logic and confirm if any errors show up.

There was no error or any sign of stress in DB log as well as in Outsystems Log.

FYI, we are using SQL server which is read Uncommitted.

Hi Dileep,

A few questions

* In initial post you say you loop on server side, but from screenprints it seems you loop on client side, doing maybe 20 server calls instead of 1.  Is there a reason you do it like this ?

* can you show details of the If node inside the loop ?

* are there no records created, or only a few but not all ?  Is there any cosistency in your test results ?

* what happens if you go through all the steps in debug mode ?


Dorine 

Hi Dorine,

As mentioned earlier and if you see the above screenshot, the  loop in server side.

  • In IF node just checking, the value is blank not writing to the server but we have printed the value in log, its showing there but in DB is not writing.
  • There are some cases, where few records are getting missed, but sometimes all.
  • This is happening in Production only with actual personas where we cannot debug, we have generated logs that is coming but DB its not writing.
  •  This is not happening in lower Env (DEV, QA, Preprod), so we are not able to reproduce this in lower environment.

Dileep




Ok,

so it's not the IF condition, fine.  Too bad you can not debug.  If it always works as expected in lower env, than this must be related to timeouts / database too busy or something like that, rather than faulty code, I guess.

But I must ask again, what do you mean when you say the loop is server side.  The screen print looks like client side to me, i.e. the nodes (such as end node) are just a green border with empty inside, on server side I would expect the nodes to be filled with green.  

Maybe performance problems in production arise from many server calls happening closely together ??  But regardless of whether this is the cause, I think it's not a very good idea, unless maybe if only very few of large list are actually populated, but then still, you could use a filter and send filtered list to server.

Dorine

client node

server node



EDIT : Oh, another question, is the id autonumber, or could there be a problem there, where same id is used several times in some way ?

EDIT : Oh, and another question, where exactly do you log anything ?  Could you maybe add a log right before or even inside the loop ?  Are you sure that there are actually as many values in the loop to be saved as you think ?

Hi Dorine,

Yes we have used ID as Auto number

and we have created log just before the action we are using to create/update the records.

No clue so far...:(


Dileep



Where exactly is the log created, I don´ t see any in your screenprints.  What are you logging, details of all the records that should be created ?

Hi Dileep,

Could you share the entity relationship diagram OR just screenshots of the entities and their attributes? Without it, the picture is a bit muddled in my head. 

Assuming that you have sufficient exception handling in place, as for errors not showing up in lower environments, that maybe because the # of test users may not have been as high as in production. We have no details on that.

When you say data is not being inserted, maybe its because the inserts are actually ending up updating records? Dorine hinted as much too. Is there a way for you to verify if that is happening by looking at timestamps?

Regards,

AJ

Hi Dileep,

we all have some question :) of course to understand the issue and to help you. 

- option 1. Could it be that 'some records' are overwritten by having the same Id? 

- option 2. You might want to add some client side logging and client side exception handlers to improve tracing the root cause. 

If you are able to share a bit more of your code we might be able to help you more. e.g what's the content of the If, is there a maximum defined in the loop cycle?

regards Hans

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.