On 2/28/18 6:59 AM, Frank Millman wrote:
Hi all
I am using Python 3.6.0 and sqlite3 3.20.1. I am getting the message ‘database
is locked’ which, from reading the docs, I think is coming from an SQL_BUSY
error.
It does not behave in the way I expect. I tested using two concurrent
connections – call them conn_1 and conn_2.
1. conn_1 performs an INSERT.
2. conn_2 performs an INSERT.
3. conn_1 sleeps for 1 second, then performs a COMMIT.
4. conn_2 sleeps for 1 second, then performs a COMMIT.
After step 2, the database is locked, which I understand. I don’t know which
connection is holding the lock, but I don’t think that matters.
What I expected to happen was that, after one second, whichever connection was
holding the lock would COMMIT, which would free up the other connection, which
would then complete normally.
AFAICT, what happens in practice is that the lock is held for the default
timeout of 5 seconds, then the connection which did *not* hold the lock fails
with ‘database is locked’, and the connection which *did* hold the lock
completes normally.
Is there any way to get the behaviour that I am looking for?
Thanks
Frank Millman
Your description, seeming to imply a total order, has an issue. If this
is happening in a single thread, then if it uses a second connection to
try and insert, that will block and the thread will never get to step 3,
so the commit will not happen. You have a dead lock.
If steps 1 and 3 are in one thread, and 2 and 4 are in a different
thread then this should normally work. The only possible issue would be
if the Python wrapper for sqlite doesn't let the first thread run when
the second thread blocks waiting to get access to perform the insert.
The description here would be described as (note, T1 and T2 are the two
threads, and ordering between them is generally weak except as enforced
by locks)
T1-1 conn_1 performs an INSERT
T1-2 conn_1 sleeps for 1 second
T2-1 conn_2 attempts to perfom an INSERT, but blocks
T1-3 conn_1 wakes up and performs a commit
T2-1a conn_2 wakes up and performs an INSERT
T2-2 conn_2 sleeps for 1 second
T2-3 conn_2 wakes up and performs a commit
Note, it is was just a single thread, then your description forces what
I call T2-1 to complete (in T2-1a) before you get to T1-2, and thus
before T1-3, but by the lock, T1-3 must complete before you get to
T2-1a, so you deadlock.
--
Richard Damon
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
