Database is locked

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Database is locked

Frank Millman
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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database is locked

Richard Damon
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database is locked

Frank Millman
On 2/28/18 2:53 PM, Richard Damon wrote:

>
> 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.
>
[...]
>
> 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.
>

Of course! I should have thought of that.
I am using python’s asyncio module, so everything is happening in a single thread.
I understand what is happening now. Thanks very much.
Frank
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database is locked

Frank Millman
On 2/28/18 3:18 PM, Frank Millman wrote:

>
> On 2/28/18 2:53 PM, Richard Damon wrote:
>
> >
> > 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.
> >
[...]

> >
> > 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.
> >
>
> Of course! I should have thought of that.
> I am using python’s asyncio module, so everything is happening in a single thread.
> I understand what is happening now. Thanks very much.

>

For the record, in case anyone runs into a similar situation, I found an effective solution.

I try to keep my transactions as short as possible, but with multiple connections running in the same thread it is possible that one of them starts a transaction before another one has committed.

To control that, I created an asyncio.Lock(), which is acquired at the start of the transaction and released after the commit. Problem solved.

I tested with 10 concurrent connections, and they all updated perfectly, with hardly any slowdown.

Frank
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users