[Flask] Getting old value from DB

[spaceman]

Cameron Simpson wrote:
> On 22Jun2019 08:48, shanmugavel Subramani <srshanmugavel at gmail.com> wrote:
> >I have below DB setup in my code. There are few APIs in my code and few
> >controllers which will serve templates and corresponding form POST
> >handlers. The problem is when i call /details?stu_id=abcd the handler
> >decorator gets invoked and I have a db query to check if the given stu_id
> >is present in my table(Student). I am randomly getting the passed id is not
> >exists in my table error. But when i checked manually its present. after
> >few refreshes its working fine. I dont get this problem in the APIs
> 
> Is it possible that the new stu_id row is created by a transaction which 
> has not been committed? I don't see any explicit transaction stuff in 
> the code you quoted, but since flask is multithreaded it is possible for 
> the creating transaction to be still open (uncommitted) when your query 
> is made. Depending on what is going on.
> 
> I'm just guessing here as its not clear from the code, but (a) 
> transactions do conceal their sid effects until closed/committed and (b) 
> transactions are often implicitly used if you don't manage them yourself 
> and (c) they will produce effects like this as a consequence.

Adding to what Cameron said that this something to do transactions
(although as far I am aware at least with Flask-SQLAlchemy autocommit
is enabled by default). I had a similar problem where changes to the
database would not show up between threads.

The problem was solved for me by fiddling with the transaction isolation
level with MySQL (it exists in other DBs as well). I had to set mine to
read-commited to get data that was committed to be readable. There are
few different levels, I suggest you do some research to find the one
that works for you. The default for MariaDB as far as I remember wasn't
sane (especially for guy coming from PHP).

I am not a database expert, I am sure there someone around here who
can fill you in as to what "Transaction Isolation" is and why it is
necessary.

Regards,
spaceman