The Problem of Multiple Threads for Sqlite3 - got an unexpected result-CodePudding

i try to test wirting/reading data into sqlite database by using multiple threads.

Sometimes it doesn't seem to get the right result. Is that BUG?

i make two files to test it. the first one is test.py.

import threading
import master

def add():
    for i in range(10):
        num = master.get()
        tmp = num   1
        master.update(tmp)
        print(f"add: {i}, {num}")


def sub():
    for i in range(10):
        num = master.get()
        tmp = num - 1
        master.update(tmp)
        print(f"sub: {i}, {num}")

if __name__ == "__main__":
    subThread01 = threading.Thread(target=add)
    subThread02 = threading.Thread(target=sub)
    subThread01.start()
    subThread02.start()
    subThread01.join()
    subThread02.join()
    print(master.get())

the second file is master.py.

import sqlite3
import threading

lock = threading.Lock()

conn = sqlite3.connect(':memory:', check_same_thread=False)
cur = conn.cursor()

# creat table
cur.execute("""CREATE TABLE IF NOT EXISTS info ( userid INT PRIMARY KEY, data INT );""")
conn.commit()

# insert init data
db = (0, 0)
cur.execute("INSERT INTO info VALUES(?, ?);", db)
conn.commit()

# update data
def update(num):
    with lock:
        db = (num, 0)
        cur.execute("UPDATE info set data = ? where userid = ?;", db)
        conn.commit()

# get data
def get():
    with lock:
        cur.execute(f"SELECT data FROM info where userid = 0;")
        result = cur.fetchone()
        return result[0]

the result I expected was 0 when i run the test.py. but the actual result is random, sometimes -3, sometimes 9, etc.

Where does the problem lie?

CodePudding user response：

This is probably a feature, not a bug.

For the result to be 0, both threads would have to be scheduled for running exactly in sequence. And if you had only two threads, that might work.

However there is at third thread (the main thread). Without extra measures, there is no way to tell which thread will be selected for running after that.

You could however use e.g. a Barrier instead of a Lock to enforce the threads running one after another.

CodePudding user response：

update and get functions are thread-safe but add and sub functions are not. This will create synchronization problems. You should also do thread-safe your add and sub functions like ;

new_lock=threading.Lock()

def add():
    for i in range(10):
        with new_lock:
            num = master.get()
            tmp = num   1
            master.update(tmp)
            print(f"add: {i}, {num}")


def sub():
    for i in range(10):
        with new_lock:
            num = master.get()
            tmp = num - 1
            master.update(tmp)
            print(f"sub: {i}, {num}")