Recover the last state of a program
Question: I would like to develop a program, which can recover the last loop in a
program that is using a sqlite3 database.

I have a Raspberry Pi running where the source is Python, the system can have
a power failure and restart for a while.

The program can be initiated from boot but it can be difficult to get back to
the last loop of the program. Let's consider the print function as a Python
syntax with a delay of 5 seconds each, which means there are 4 different
programs running synchronized.

The below program doesn't work as expected, could someone please help me to
solve the puzzle?

    
    
    import thread
    import time
    
    import sqlite3
    
    
    conn = sqlite3.connect('testdatabase.db')
    conn.isolation_level = None
    c = conn.cursor()
    c.execute("SELECT ID from LAST_STATE")
    fetch=c.fetchone()
    
    def morning_u():
        conn = sqlite3.connect('testdatabase.db')
        c = conn.cursor()
        c.execute("UPDATE LAST_STATE SET ID=1")
        conn.commit()
        c.close()
        conn.close()
    
    def noon_u():
        conn = sqlite3.connect('testdatabase.db')
        c = conn.cursor()
        c.execute("UPDATE LAST_STATE SET ID=2")
        conn.commit()
        c.close()
        conn.close()
    
    def afternoon_u():
        conn = sqlite3.connect('testdatabase.db')
        c = conn.cursor()
        c.execute("UPDATE LAST_STATE SET ID=3")
        conn.commit()
        c.close()
        conn.close()
    
    def evening_u():
        conn = sqlite3.connect('testdatabase.db')
        c = conn.cursor()
        c.execute("UPDATE LAST_STATE SET ID=4")
        conn.commit()
        c.close()
        conn.close()
    
    def morning():
            print ("morning")
            time.sleep(5)
            return
    
    def noon():
            print ("noon")
            time.sleep(5)
            return
    
    def afternoon():
            print ("afternoon")
            time.sleep(5)
    
    
    def evening():
            print ("evening")
            time.sleep(5)
            morning_u()
    
    while True:
        if fetch[0] is 1:
            morning()
            noon_u()
        if fetch[0] is 2:
            noon()
            afternoon_u()
        if fetch[0] is 3:
            afternoon()
            evening_u()
        if fetch[0] is 4:
            evening()
            morning_u()
    

Database information

    
    
    conn = sqlite3.connect('testdatabase.db')
    
    conn.execute('''CREATE TABLE LAST_STATE
           (ID INT PRIMARY KEY     NOT NULL);''')
    
    conn.execute("INSERT INTO LAST_STATE (ID) \
          VALUES (1)");
    

Edited program as per comment, please suggest for improvement

    
    
    import datetime
    import time
    import logging
    import sqlite3
    
    
    conn = sqlite3.connect('testdatabase.db')
    conn.isolation_level = "IMMEDIATE"
    c = conn.cursor()
    c.execute("SELECT ID from LAST_STATE")
    state=c.fetchone()
    
     def morning_u():
        c.execute("UPDATE LAST_STATE SET ID=1")
        conn.commit()
    
    def noon_u():
        c.execute("UPDATE LAST_STATE SET ID=2")
        conn.commit()
    
    def afternoon_u():
        c.execute("UPDATE LAST_STATE SET ID=3")
        conn.commit()
    
    def evening_u():
        c.execute("UPDATE LAST_STATE SET ID=4")
        conn.commit()
    
    def morning():
            print ("morning")
            time.sleep(5)
    
    def noon():
            print ("noon")
            time.sleep(5)
    
    def afternoon():
            print ("afternoon")
            time.sleep(5)
    
    def evening():
            print ("evening")
            time.sleep(5)
    
    while True:
        if state[0] is 1:
            morning()
            try:
                noon_u()
            except:
                print ("error1")
    
        if state[0] is 2:
            noon()
            try:
                afternoon_u()
            except:
                print ("error2")
    
        if state[0] is 3:
            afternoon()
            try:
                evening_u()
            except:
                print ("error3")
    
        if state[0] is 4:
            evening()
            try:
                morning_u()
            except:
                print ("error4")
    


Answer: I see two immediate problems that could cause your bug.

First, `evening` calls `morning_u` but no other function does that and it's
redundant with the loop.

Second, you're never updating `fetch` (which is a poor name, consider
something more like `state` or `time_of_day`) in the loop. The loop will keep
doing the same thing over and over again.

Also you're disconnecting and reconnecting to the database with every function
call. This is inefficient, and it could cause concurrency issues. The
functions would be better written to accept an existing database connection.

And, finally, you have `conn.isolation_level = None` which auto-commits, but
you're calling `commit`. The commit is a no-op, but it can fool the reader
into thinking you're using transactions. In general, avoid auto-commit, and
use transactions. Transactions are very important for concurrency. [SQLite
defaults to odd implicit
transactions](https://docs.python.org/2/library/sqlite3.html#sqlite3-controlling-
transactions) which might not do what you want and are hard to puzzle out. I'd
recommend using explicit transactions. Probably
[immediate](https://www.sqlite.org/lang_transaction.html) for the most
predictable behavior, but not exclusive. Set `conn.isolation_level =
"IMMEDIATE"`.

* * *

Design wise, you say you want a crash-recovery system, but you're trying to
use it as a concurrent system. These are two, mostly exclusive things.

Crash-recovery assumes there is only one process which owns the state at any
time. It loads up, it gets the old state, and then it merrily chugs along
assuming it owns the state and changing it as it likes. _Nobody else is
changing the state_. It might even guarantee this with an exclusive lock on
the table.

Concurrency assumes many processes are all updating the state. Since you're
building a state-machine, each change to the state depends on the existing
state. All changes to the state have to be atomic: start a transaction (ie.
get a lock), read the state, update the state, and commit. All uses of the
state also have to be atomic: start a transaction (ie. get a read-lock to
prevent the state from changing), read the state, use the state, end the
transaction.

For crash-recovery you only need to read the state once, at the start of the
program. You only have to write it when you change it.

For concurrency, you have to read the state at the start of the program, and
any time you want to use it. While you're using it, you have to get a shared
lock to make sure it doesn't change in the meantime.

You have the code necessary for crash recovery, all you're missing is making
sure the state (ie. `fetch[0]`) is updated by the state change functions. You
have none of the code for concurrency, but you don't need it.

