Python“内存泄漏”当使用线程和 sqlite3 时

Question

我在一个项目中使用 sqlite3 和线程，它给我带来了很多问题。在更好地了解 sqlite3 后，我设法解决了所有这些问题，但我还没有解决内存泄漏。我不知道是否是线程或 sqlite3 或两者导致它。我认为这是两者的结合。我终于成功创建了一个最小的示例来向您展示：

import sqlite3
import threading
import random
import gc
import time

from collections import Counter
import linecache
import os
import tracemalloc

def display_top(snapshot, key_type='lineno', limit=10):
    snapshot = snapshot.filter_traces((
        tracemalloc.Filter(False, "<frozen importlib._bootstrap>"),
        tracemalloc.Filter(False, "<unknown>"),
    ))
    top_stats = snapshot.statistics(key_type)

    print("Top %s lines" % limit)
    for index, stat in enumerate(top_stats[:limit], 1):
        frame = stat.traceback[0]
        # replace "/path/to/module/file.py" with "module/file.py"
        filename = os.sep.join(frame.filename.split(os.sep)[-2:])
        print("#%s: %s:%s: %.1f KiB"
              % (index, filename, frame.lineno, stat.size / 1024))
        line = linecache.getline(frame.filename, frame.lineno).strip()
        if line:
            print('    %s' % line)

    other = top_stats[limit:]
    if other:
        size = sum(stat.size for stat in other)
        print("%s other: %.1f KiB" % (len(other), size / 1024))
    total = sum(stat.size for stat in top_stats)
    print("Total allocated size: %.1f KiB" % (total / 1024))

connection = sqlite3.connect("testdatabase.db", check_same_thread=False)

cursor = connection.cursor()
cursor.execute("DELETE FROM test_table;",);

# This has to be enabled first time running to create the table
# cursor.execute("CREATE TABLE test_table(val1 TEXT, val2 TEXT, val3 TEXT, val4 TEXT, val5 TEXT)")

critical_lock_sqlite = threading.Lock()

def mem_tester(value1,value2,value3,value4,value5):
    try:
        critical_lock_sqlite.acquire(True)
        cursor = connection.cursor()
        if cursor.execute("SELECT val2, val3 FROM test_table WHERE val2=? AND val3=?",
                          (value2, value3)).fetchone():
            del value1, value2, value3, value4, value5
            state_int = 0
        elif not cursor.execute("SELECT val2, val3 FROM test_table WHERE val2=? OR val3=?",
                                (value2, value3)).fetchone():
            state_int = 2
            cursor.execute("INSERT INTO test_table VALUES (?, ?, ?, ?, ?)",
                           (value1, value2, value3, value4, value5))
            connection.commit()
        elif cursor.execute("SELECT val3 FROM test_table WHERE val3=?", (value3,)).fetchone():
            state_int = 1
            cursor.execute("UPDATE test_table SET val2=? WHERE val3=?", (value2, value3))
        elif not cursor.execute("SELECT val2, val3 FROM test_table WHERE val2=? AND val3=?",
                                (value2, value3)).fetchone():
            state_int = 2
            cursor.execute("INSERT INTO test_table VALUES (?, ?, ?, ?, ?)",
                           (value1, value2, value3, value4, value5))
            connection.commit()
        # Unexpected Occurrance
        else:
            state_int = 2
            cursor.execute("INSERT INTO test_table VALUES (?, ?, ?, ?, ?)",
                           (value1, value2, value3, value4, value5))
            connection.commit()
        cursor.close()
        del cursor
    finally:
        critical_lock_sqlite.release()
if __name__ == '__main__':
    tracemalloc.start()
    for i in range(1, 10000):
        threading.Thread(target=mem_tester, args=("test",random.randint(1, 999),''.join(random.choice("ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890") for _ in range(8)),"random","random")).start()
    gc.collect()
    time.sleep(5)
snapshot = tracemalloc.take_snapshot()
display_top(snapshot)

我很难在这里找到问题。内存不断增加，如果我删除 critical_lock_sqlite，它不会增加那么多，但随后我将出现一百万个错误，因为我不允许在不同线程中同时运行命令。有谁能帮我解决这个案子吗？
请注意，此脚本还将显示使用最多内存的位置。在这种情况下，它主要表明正在使用线程。
谢谢你！