Database locks

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Database locks

Gerlando Falauto
Hi,

I'm trying to implement a logging system based on SQLite, using python3
package apsw.
There's one process constantly writing and another one reading.
From time to time I get an exception from the writer, complaining the
database is locked.
I'm pretty sure there's no other process writing, and I was under the
impression that readers should not block writers, they should just see a
previous version of the dataset.
Is my assumption wrong?
The package I'm using is based on Sqlite version 3.9.2, so perhaps it's a
bit outdated.

Could anyone please shed some light on this topic?
Thank you!
Gerlando
_______________________________________________
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 locks

Simon Slavin-3
On 7 Aug 2018, at 12:55pm, Gerlando Falauto <[hidden email]> wrote:

> I'm trying to implement a logging system based on SQLite, using python3
> package apsw.
> There's one process constantly writing and another one reading.
> From time to time I get an exception from the writer, complaining the
> database is locked.

Please set a time of at least 10,000 milliseconds for /all/ connections, both reading and writing:

    Connection.setbusytimeout(10000)

<https://rogerbinns.github.io/apsw/connection.html?highlight=timeout#apsw.Connection.setbusytimeout>

If you're already doing this, please post again, telling us whether you're using two separate connections or passing the connection handle from process to process.

Simon.
_______________________________________________
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: [EXTERNAL] Database locks

Hick Gunter
In reply to this post by Gerlando Falauto
Your impression is only correct if you are running in WAL journal_mode. In all other modes, readers will block the writer(s) and yo need to set a busy timeout or a busy handler.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Gerlando Falauto
Gesendet: Dienstag, 07. August 2018 13:55
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Database locks

Hi,

I'm trying to implement a logging system based on SQLite, using python3 package apsw.
There's one process constantly writing and another one reading.
From time to time I get an exception from the writer, complaining the database is locked.
I'm pretty sure there's no other process writing, and I was under the impression that readers should not block writers, they should just see a previous version of the dataset.
Is my assumption wrong?
The package I'm using is based on Sqlite version 3.9.2, so perhaps it's a bit outdated.

Could anyone please shed some light on this topic?
Thank you!
Gerlando
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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 locks

Gerlando Falauto
In reply to this post by Simon Slavin-3
Hi Simon,
than you for your answer.

On Tue, Aug 7, 2018 at 2:09 PM, Simon Slavin <[hidden email]> wrote:

> On 7 Aug 2018, at 12:55pm, Gerlando Falauto <[hidden email]>
> wrote:
>
> > I'm trying to implement a logging system based on SQLite, using python3
> > package apsw.
> > There's one process constantly writing and another one reading.
> > From time to time I get an exception from the writer, complaining the
> > database is locked.
>
> Please set a time of at least 10,000 milliseconds for /all/ connections,
> both reading and writing:
>
>     Connection.setbusytimeout(10000)
>
> <https://rogerbinns.github.io/apsw/connection.html?highlight=timeout#apsw.
> Connection.setbusytimeout>
>

Hmm... are you saying the writer could potentially block for up to 10
seconds?
If that's the case then I should rethink the whole logging process cause it
might end up losing incoming data if waiting for too long.
In any case, I still don't understand whether the reader would block the
writer or not, and in what phase.
A reader could potentially take a long time (even longer than 10 seconds)
to read all the data...


> If you're already doing this, please post again, telling us whether you're
> using two separate connections or passing the connection handle from
> process to process.
>

It's two separate connections. Is that bad or good?

Thank you,
Gerlando
_______________________________________________
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 locks

Simon Slavin-3
On 7 Aug 2018, at 1:22pm, Gerlando Falauto <[hidden email]> wrote:

> Hmm... are you saying the writer could potentially block for up to 10
> seconds?

I should have been clearer.  The 10 second time is purely for diagnostic purposes, to see if the error goes away.  It was chosen to be far longer than any legitimate lock could last.  If you report that it fixes the problem someone can explain what's happening.  If you report that it doesn't fix the problem, we don't have to faff about with "try a longer timeout".

The exception would be some kind of hardware error.  Faulty networking or a fault in your storage device could cause a long delay.

> It's two separate connections. Is that bad or good?

Good.  That's the right way to do it, especially if your program gets more complicated than a single INSERT vs. a single SELECT.

Simon.
_______________________________________________
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 locks

Gerlando Falauto
In reply to this post by Gerlando Falauto
I just realized I'm using default settings... perhaps I should use WAL mode
instead?

Thanks,
Gerlando

On Tue, Aug 7, 2018 at 2:22 PM, Gerlando Falauto <[hidden email]
> wrote:

> Hi Simon,
> than you for your answer.
>
> On Tue, Aug 7, 2018 at 2:09 PM, Simon Slavin <[hidden email]> wrote:
>
>> On 7 Aug 2018, at 12:55pm, Gerlando Falauto <[hidden email]>
>> wrote:
>>
>> > I'm trying to implement a logging system based on SQLite, using python3
>> > package apsw.
>> > There's one process constantly writing and another one reading.
>> > From time to time I get an exception from the writer, complaining the
>> > database is locked.
>>
>> Please set a time of at least 10,000 milliseconds for /all/ connections,
>> both reading and writing:
>>
>>     Connection.setbusytimeout(10000)
>>
>> <https://rogerbinns.github.io/apsw/connection.html?highlight
>> =timeout#apsw.Connection.setbusytimeout>
>>
>
> Hmm... are you saying the writer could potentially block for up to 10
> seconds?
> If that's the case then I should rethink the whole logging process cause
> it might end up losing incoming data if waiting for too long.
> In any case, I still don't understand whether the reader would block the
> writer or not, and in what phase.
> A reader could potentially take a long time (even longer than 10 seconds)
> to read all the data...
>
>
>> If you're already doing this, please post again, telling us whether
>> you're using two separate connections or passing the connection handle from
>> process to process.
>>
>
> It's two separate connections. Is that bad or good?
>
> Thank you,
> Gerlando
>
>
_______________________________________________
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 locks

David Raymond
Correct.

In rollback journal mode when one connection says "I'm ready to write now" it blocks any new transactions from being made, but it can't do anything about existing read transactions. It has to wait for them to finish their reads and end their transactions before it can do any actual writing.

In WAL mode, when one connection says "I want to write now" then as long as no other connection has called dibbs on writing, it can start writing without worrying about existing readers.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Gerlando Falauto
Sent: Tuesday, August 07, 2018 8:54 AM
To: SQLite mailing list
Subject: Re: [sqlite] Database locks

I just realized I'm using default settings... perhaps I should use WAL mode
instead?

Thanks,
Gerlando

On Tue, Aug 7, 2018 at 2:22 PM, Gerlando Falauto <[hidden email]
> wrote:

> Hi Simon,
> than you for your answer.
>
> On Tue, Aug 7, 2018 at 2:09 PM, Simon Slavin <[hidden email]> wrote:
>
>> On 7 Aug 2018, at 12:55pm, Gerlando Falauto <[hidden email]>
>> wrote:
>>
>> > I'm trying to implement a logging system based on SQLite, using python3
>> > package apsw.
>> > There's one process constantly writing and another one reading.
>> > From time to time I get an exception from the writer, complaining the
>> > database is locked.
>>
>> Please set a time of at least 10,000 milliseconds for /all/ connections,
>> both reading and writing:
>>
>>     Connection.setbusytimeout(10000)
>>
>> <https://rogerbinns.github.io/apsw/connection.html?highlight
>> =timeout#apsw.Connection.setbusytimeout>
>>
>
> Hmm... are you saying the writer could potentially block for up to 10
> seconds?
> If that's the case then I should rethink the whole logging process cause
> it might end up losing incoming data if waiting for too long.
> In any case, I still don't understand whether the reader would block the
> writer or not, and in what phase.
> A reader could potentially take a long time (even longer than 10 seconds)
> to read all the data...
>
>
>> If you're already doing this, please post again, telling us whether
>> you're using two separate connections or passing the connection handle from
>> process to process.
>>
>
> It's two separate connections. Is that bad or good?
>
> Thank you,
> Gerlando
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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 locks

Simon Slavin-3
In reply to this post by Gerlando Falauto
On 7 Aug 2018, at 1:54pm, Gerlando Falauto <[hidden email]> wrote:

> I just realized I'm using default settings... perhaps I should use WAL mode
> instead?

How important is it to you that SELECT gets up-to-date information ?  If a read happens at the same time as a write:

Normal mode: SELECT waits until the write is finished, then reads
   WAL mode: SELECT immediately returns data from before the write

(the above is simplified for short one-write one-read situations).

Simon.
_______________________________________________
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 locks

Gerlando Falauto
On Tue, Aug 7, 2018 at 3:28 PM, Simon Slavin <[hidden email]> wrote:

> On 7 Aug 2018, at 1:54pm, Gerlando Falauto <[hidden email]>
> wrote:
>
> > I just realized I'm using default settings... perhaps I should use WAL
> mode
> > instead?
>
> How important is it to you that SELECT gets up-to-date information ?  If a
> read happens at the same time as a write:
>
> Normal mode: SELECT waits until the write is finished, then reads
>    WAL mode: SELECT immediately returns data from before the write
>
> (the above is simplified for short one-write one-read situations).
>


I don't think it's really that important, especially because writes should
be much quicker than reads (I should do some benchmarking though).
Ideally I would want a mixture of both modes, i.e. readers to wait until
all writers are done, but not the other way around -- but I guess that
can't be done.

What might happen in my case is that reads could take a very long time to
consume/process all the data (30s for instance), and I believe the lock is
held until all data is consumed.
I believe in that case the read would see all the data up to the point in
time when the SELECT was first issued, ignoring whatever happened later, is
that right?

Thank you,
Gerlando
_______________________________________________
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 locks

Simon Slavin-3
On 7 Aug 2018, at 3:04pm, Gerlando Falauto <[hidden email]> wrote:

> What might happen in my case is that reads could take a very long time to
> consume/process all the data (30s for instance), and I believe the lock is
> held until all data is consumed.
> I believe in that case the read would see all the data up to the point in
> time when the SELECT was first issued, ignoring whatever happened later, is
> that right?

If your writes are fast, and your reads are slow, you should not use WAL mode.  Just set a good long timeout on both connections and let SQLite do its thing.  A sixty second timeout should be about the minimum you should set, since you wouldn't want your program to crash for anything less than that.

Simon.
_______________________________________________
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 locks

David Raymond
I think what he's saying is "my reads are slow, and I don't want them to block my writes" which would mean he <should> use WAL mode.

i.e. the long reads are going to cause a traffic jam of backed up writes in rollback journal mode, but will be ok in WAL mode.



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Tuesday, August 07, 2018 12:56 PM
To: SQLite mailing list
Subject: Re: [sqlite] Database locks

On 7 Aug 2018, at 3:04pm, Gerlando Falauto <[hidden email]> wrote:

> What might happen in my case is that reads could take a very long time to
> consume/process all the data (30s for instance), and I believe the lock is
> held until all data is consumed.
> I believe in that case the read would see all the data up to the point in
> time when the SELECT was first issued, ignoring whatever happened later, is
> that right?

If your writes are fast, and your reads are slow, you should not use WAL mode.  Just set a good long timeout on both connections and let SQLite do its thing.  A sixty second timeout should be about the minimum you should set, since you wouldn't want your program to crash for anything less than that.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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 locks

Keith Medcalf

Make sure to wrap your transactions in BEGIN / COMMIT.  Use BEGIN IMMEDIATE for transactions that you know are going to write, and plain BEGIN for read-only transactions.  You can omit the explicit BEGIN / COMMIT if each transaction consists of only a single statement since autocommit will do a BEGIN / COMMIT around the statement automagically.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of David Raymond
>Sent: Tuesday, 7 August, 2018 11:13
>To: SQLite mailing list
>Subject: Re: [sqlite] Database locks
>
>I think what he's saying is "my reads are slow, and I don't want them
>to block my writes" which would mean he <should> use WAL mode.
>
>i.e. the long reads are going to cause a traffic jam of backed up
>writes in rollback journal mode, but will be ok in WAL mode.
>
>
>
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Simon Slavin
>Sent: Tuesday, August 07, 2018 12:56 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] Database locks
>
>On 7 Aug 2018, at 3:04pm, Gerlando Falauto
><[hidden email]> wrote:
>
>> What might happen in my case is that reads could take a very long
>time to
>> consume/process all the data (30s for instance), and I believe the
>lock is
>> held until all data is consumed.
>> I believe in that case the read would see all the data up to the
>point in
>> time when the SELECT was first issued, ignoring whatever happened
>later, is
>> that right?
>
>If your writes are fast, and your reads are slow, you should not use
>WAL mode.  Just set a good long timeout on both connections and let
>SQLite do its thing.  A sixty second timeout should be about the
>minimum you should set, since you wouldn't want your program to crash
>for anything less than that.
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
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 locks

Keith Medcalf
In reply to this post by Gerlando Falauto

By "constantly writing" I presume you mean "periodically writing".  For example doing one independent INSERT every millisecond where there is no dependency from between inserts is "periodic writing".  "Constantly writing", from a database perspective, means a single transaction that never ends ...


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Gerlando Falauto
>Sent: Tuesday, 7 August, 2018 05:55
>To: SQLite mailing list
>Subject: [sqlite] Database locks
>
>Hi,
>
>I'm trying to implement a logging system based on SQLite, using
>python3
>package apsw.
>There's one process constantly writing and another one reading.
>From time to time I get an exception from the writer, complaining the
>database is locked.
>I'm pretty sure there's no other process writing, and I was under the
>impression that readers should not block writers, they should just
>see a
>previous version of the dataset.
>Is my assumption wrong?
>The package I'm using is based on Sqlite version 3.9.2, so perhaps
>it's a
>bit outdated.
>
>Could anyone please shed some light on this topic?
>Thank you!
>Gerlando
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
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 locks

Rowan Worth-2
In reply to this post by David Raymond
On 7 August 2018 at 21:25, David Raymond <[hidden email]> wrote:

> Correct.
>
> In rollback journal mode when one connection says "I'm ready to write now"
> it blocks any new transactions from being made, but it can't do anything
> about existing read transactions. It has to wait for them to finish their
> reads and end their transactions before it can do any actual writing.
>

To clarify, the "I'm ready to write now" phase only happens at the end of a
transaction. Each SQL command implies different lock requests:

BEGIN -> is a no-op, it just opens a transaction
BEGIN IMMEDIATE -> says "I want to write at some point in the future" (aka
RESERVED lock)
SELECT -> says "I want to read now" (aka SHARED lock)
INSERT/UPDATE/DELETE -> says "I want to write at some point in the future"
(aka RESERVED lock)
COMMIT (with a RESERVED lock) -> says "I'm ready to write now" (aka PENDING
lock)

Obtaining a RESERVED lock will fail if any other process already has the
RESERVED lock - ie. there can only be one writer. But RESERVED does not
block SHARED; other processes can still read.

It's only when the writer is ready to commit and obtains the PENDING lock
that attempts to obtain a SHARED lock will fail. This is to prevent writer
starvation eg. in the case of constant read queries. Once all pending read
transactions are finished, the writer obtains the EXCLUSIVE lock and
actually updates the main database file. Then all locks are relinquished
and everything is fair game again.


So in general the writer doesn't need to block readers for very long. The
exception is a large write transaction, which can blow sqlite's memory
cache and cause it to obtain PENDING+EXCLUSIVE before COMMIT happens (see
the cache_size pragma).

More problematic is that a long read transaction ends up blocking other
readers if a writer wants to COMMIT in the same period. I *think* WAL mode
can help with that, but I don't have much experience with it.

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