Re: [pysqlite] PySqlite threading concurrency issue

I replied yesterday but apparently not to the list. Like to add that the busy handler in SQLite 3.5.10 is improved and solves the problem, at least here. In older 3.5.X versions, it sleeps one full second before each retry.


--- [email protected] wrote:

From: [email protected]
To: [email protected], 	"About pysqlite and APSW." <list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org>
Subject: Re: [pysqlite] PySqlite threading concurrency issue
Date: Sat, 13 Sep 2008 12:05:59 -0500

If this is a GIL issue, can you explain why it still happens if I
modify my example below to use subprocess.Popen() instead of thread?
I was under the impression that the GIL is per-process, so if I spawn
another one, each process would have its own lock and the two should
no longer be interfering with one another.   Thanks for all the help!

from pysqlite2 import dbapi2 as sqlite3
import datetime
import thread, time, os, sys, subprocess
db = 'test.db'

def aa():
    while True:
        conn = sqlite3.connect(db, timeout=10)
        conn.isolation_level = None
        c = conn.cursor()
        c.execute('SELECT a from t1 where b=1')
        print list(c)
        #conn.commit()
        conn.close()
        time.sleep(1)

def bb():
    while True:
        conn = sqlite3.connect(db, timeout=10)
        conn.isolation_level = 'EXCLUSIVE'
        c = conn.cursor()
        c.execute('UPDATE t1 set a=? where b=0', (datetime.datetime.now(),) )
        #conn.commit()
        conn.close()
        time.sleep(.1)

if sys.argv[1] != 'b':
    try:
        os.unlink('test.db')
    except OSError:
        pass

    with sqliteutil.cursor(db, isolation_level = 'EXCLUSIVE', timeout=60) as c:
        c.execute('create table t1(a real, b real)')
        c.executemany('insert into t1 values(?, ?)', ( (0, 0) for i in
xrange(10000)) )
        c.execute('insert into t1 values(0, 1)')
    subprocess.Popen(['python',  'test_lock.py',  'b'])
    aa()
else:
    bb()

$ python test_lock2.py a
[(0.0,)]
[(0.0,)]
[(0.0,)]
[(0.0,)]
[(0.0,)]
Traceback (most recent call last):
  File "test_lock.py", line 59, in <module>
    aa()
  File "test_lock.py", line 14, in aa
    c.execute('SELECT a from t1 where b=1')
pysqlite2.dbapi2.OperationalError: database is locked


On Sat, Sep 13, 2008 at 2:41 AM, Edzard Pasma <[email protected]> wrote:
> Got it, it is the GIL issue reported by Ben Cottrell.
>
> --- [email protected] wrote:
>
> From: [email protected]
> To: list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
> Subject: [pysqlite] PySqlite threading concurrency issue
> Date: Fri, 12 Sep 2008 16:08:04 -0500
>
> Hello all,
>
> I've ran accross a problem when I have multiple threads in python
> attempting to select/update the same database.  I get the dreaded
> "database is locked" error.  The following bit of code reproduces the
> problem fairly reliably (within about 45 seconds on my machine), and I
> was wondering if anyone had any insight into what might be going on?
>
> from pysqlite2 import dbapi2 as sqlite3
> import datetime
> import thread, time, os
> db = 'test.db'
>
> def aa():
>    while True:
>        conn = sqlite3.connect(db, timeout=10)
>        conn.isolation_level = None
>        c = conn.cursor()
>        c.execute('SELECT a from t1 where b=1')
>        print list(c)
>        #conn.commit()
>        conn.close()
>        time.sleep(10)
>
> def bb():
>    while True:
>        conn = sqlite3.connect(db, timeout=10)
>        conn.isolation_level = 'EXCLUSIVE'
>        c = conn.cursor()
>        c.execute('UPDATE t1 set a=? where b=0', (datetime.datetime.now(),) )
>        #conn.commit()
>        conn.close()
>        time.sleep(.1)
>
>
> try:
>    os.unlink('test.db')
> except OSError:
>    pass
>
> with sqliteutil.cursor(db, isolation_level = 'EXCLUSIVE', timeout=60) as c:
>    c.execute('create table t1(a real, b real)')
>    c.executemany('insert into t1 values(?, ?)', ( (0, 0) for i in
> xrange(10000)) )
>    c.execute('insert into t1 values(0, 1)')
>
> thread.start_new_thread(bb, ())
> aa()
>
>
> When I run it, I get the following:
> $  python test_lock.py
> [(0.0,)]
> Traceback (most recent call last):
>  File "test_lock.py", line 59, in <module>
>    aa()
>  File "test_lock.py", line 14, in aa
>    c.execute('SELECT a from t1 where b=1')
> pysqlite2.dbapi2.OperationalError: database is locked
>
>
> Any help would be much appreciated.
>
> Thanks.
> _______________________________________________
> list-pysqlite mailing list
> list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
> http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite
>
>
> _______________________________________________
> list-pysqlite mailing list
> list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
> http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite
>