Concurrent Sqlite3: database table is locked
<p>I would like to produce the following set up in Python 3.4, SQLite3 v.3.8.11:</p>

<p><strong>(1) Create an in-memory shared-cache SQLite3 database:</strong></p>

<p><strong>(2) Create one connection that only writes to this DB from one thread</strong></p>

<p><strong>(3) Create multiple connections that concurrently read from this DB from various other threads</strong></p>

<p>This is what I have created to test this:</p>

<pre><code>import time
import zmq
import random
from threading import Thread
import sqlite3


def producer(context):
    zmq_socket = context.socket(zmq.PUB)
    zmq_socket.bind("inproc://test_pub")

    while True:
        msg = random.random()
        zmq_socket.send(str(msg).encode())
        wait_time = random.uniform(0, 0.05)
        time.sleep(wait_time)



def subscriber_writer(context):

    # Create database connection for writing to memory
    write_con = sqlite3.connect('file::memory:?cache=shared', uri=True)
    cursor = write_con.cursor()

    zmq_socket = context.socket(zmq.SUB)
    zmq_socket.connect("inproc://test_pub")
    zmq_socket.setsockopt(zmq.SUBSCRIBE, b'')

    while True:
        msg = float(zmq_socket.recv().decode())
        cursor.execute('UPDATE TEST SET Value=? WHERE Key="Val"', [msg])
        write_con.commit()


def consumer(context):

    # Create database connection for reading from memory in read-only mode
    read_con = sqlite3.connect('file::memory:?cache=shared&amp;mode=ro', uri=True)
    cursor = read_con.cursor()

    while True:
        cursor.execute('SELECT Value FROM TEST WHERE Key="Val"')
        row = cursor.fetchone()
        result = row[0]
        print(str(result))
        wait_time = random.uniform(0, 0.05)
        time.sleep(wait_time)


def main():

    # Create context
    context = zmq.Context()

    # Create database
    con = sqlite3.connect('file::memory:?cache=shared', uri=True)

    # Create db table
    cursor = con.cursor()
    cursor.execute('CREATE TABLE TEST(Key TEXT, Value NUMERIC)')
    cursor.execute('INSERT INTO TEST VALUES (?,?)', ["Val", 0.00])
    con.commit()

    Thread(target=subscriber_writer, args=(context,)).start()
    Thread(target=producer, args=(context,)).start()
    Thread(target=consumer, args=(context,)).start()

if __name__ == '__main__':
    main()
</code></pre>

<p>This works for a while .....but then I get the following error:</p>

<pre><code>...
0.2504188310554989
0.2504188310554989
0.8038719720740617
0.42408896748682956
0.21361498908206744
0.3404497358396832
0.010459475861968603
0.5070540941748318
0.5070540941748318
0.23151535812095037
0.636881359928549
0.4184038089576615
0.9920311052908629
Exception in thread Thread-3:
Traceback (most recent call last):
  File "E:\Python34-64\lib\threading.py", line 911, in _bootstrap_inner
    self.run()
  File "E:\Python34-64\lib\threading.py", line 859, in run
    self._target(*self._args, **self._kwargs)
  File "test.py", line 43, in consumer
    cursor.execute('SELECT Value FROM TEST WHERE Key="Val"')
sqlite3.OperationalError: database table is locked: TEST
</code></pre>

<p><strong>How can I make this work?</strong></p>

<p>As a side note, <strong>CREATING ONLY CONNECTION WITH</strong> <code>check_same_thread=False</code> and sharing this across the whole process works even when the wait times are eliminated....<strong>is this advisable to do instead</strong>? See below:</p>

<pre><code>import time
import zmq
import random
from threading import Thread
import sqlite3


def producer(context):
    zmq_socket = context.socket(zmq.PUB)
    zmq_socket.bind("inproc://test_pub")

    while True:
        msg = random.random()
        zmq_socket.send(str(msg).encode())
        # wait_time = random.uniform(0, 0.05)
        # time.sleep(wait_time)



def subscriber_writer(context, con):

    zmq_socket = context.socket(zmq.SUB)
    zmq_socket.connect("inproc://test_pub")
    zmq_socket.setsockopt(zmq.SUBSCRIBE, b'')

    cursor = con.cursor()

    while True:
        msg = float(zmq_socket.recv().decode())
        cursor.execute('UPDATE TEST SET Value=? WHERE Key="Val"', [msg])


def consumer(context, con):

    cursor = con.cursor()

    while True:
        cursor.execute('SELECT Value FROM TEST WHERE Key="Val"')
        row = cursor.fetchone()
        result = row[0]
        print(str(result))
        # wait_time = random.uniform(0, 0.05)
        # time.sleep(wait_time)


def main():

    # Create context
    context = zmq.Context()

    # Create database
    con = sqlite3.connect('file::memory:?cache=shared', uri=True, isolation_level=None, check_same_thread=False)

    # Create db table
    cursor = con.cursor()
    cursor.execute('CREATE TABLE TEST(Key TEXT, Value NUMERIC)')
    cursor.execute('INSERT INTO TEST VALUES (?,?)', ["Val", 0.00])

    Thread(target=subscriber_writer, args=(context, con)).start()
    Thread(target=producer, args=(context,)).start()
    Thread(target=consumer, args=(context, con)).start()

if __name__ == '__main__':
    main()
</code></pre>