SQLite 3 locking

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

SQLite 3 locking

Matthew Ceroni
https://www.sqlite.org/lockingv3.html

I have read through the attached link but what is outlined here doesn't
seem to match with what I am seeing in a production SQLite setup we have.
Specifically the writer starvation part of the document.

We have a DB that is heavily read. Writes happen very infrequently. We were
finding that writes were failing with "Database locked" (SQLITE_BUSY I
believe) from time to time.

To reproduce I execute continuous reads in a tight loop. Then I tried to
execute a write operation (create table) and received database locked.

After a bit of reading and troubleshooting I stumbled across the
busy_timeout option (which defaults to 0). Prior to execute the create
table statement I set PRAGMA busy_timeout=1000 (1 s) and the writes always
succeeded.

So since busy_timeout defaults to 0, all write attempts if a lock can't be
obtained will return SQLITE_BUSY immediately. Where does the PENDING lock
come into play here? I thought the PENDING was meant to be an intermediary
step before EXCLUSIVE. Does the busy_timeout impact the writers attempt to
obtain PENDING? Or does the busy_timeout trigger after X amount of ms
between obtaining PENDING and trying to move to EXCLUSIVE?

Thanks
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: SQLite 3 locking

Simon Slavin-3

On 28 Feb 2017, at 6:39pm, Matthew Ceroni <[hidden email]> wrote:

> After a bit of reading and troubleshooting I stumbled across the
> busy_timeout option (which defaults to 0). Prior to execute the create
> table statement I set PRAGMA busy_timeout=1000 (1 s) and the writes always
> succeeded.

You need to set the busy timeout at every connection to the database, including those which only read and never write.  It’s common to see it done immediately after the sqlite3_open() command.  It is not stored in the database and new connections to the database do not know what it was set to before.

If that doesn’t fix things, as a diagnostic measure, try setting busy_timeout to 30 seconds instead of just 1 second.  Learning whether this does or does not fix the problem will tell you a lot about what’s going on.

Are you using WAL mode ?  If not, you should try it.  Locking works differently in WAL files, as described in

<https://www.sqlite.org/wal.html>

> So since busy_timeout defaults to 0, all write attempts if a lock can't be
> obtained will return SQLITE_BUSY immediately. Where does the PENDING lock
> come into play here? I thought the PENDING was meant to be an intermediary
> step before EXCLUSIVE. Does the busy_timeout impact the writers attempt to
> obtain PENDING? Or does the busy_timeout trigger after X amount of ms
> between obtaining PENDING and trying to move to EXCLUSIVE?

There are two kinds of locks: shared and exclusive.  A pending lock means you have shared, not exclusive, but that you want exclusive.

timeout applies when obtaining both shared and exclusive locks.  Actually, hold on, I’m not sure about this.  Better to try what I wrote already then get an expert to explain things.

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
|  
Report Content as Inappropriate

Re: SQLite 3 locking

Matthew Ceroni
Appreciate the reply. I just found it odd that busy_timeout is set to 0 (so
fail immediately) considering the intermediary PENDING stage. That stage is
essentially useless with the default busy timeout of 0 no?

I will read up on WAL.

On Tue, Feb 28, 2017 at 4:22 PM, Simon Slavin <[hidden email]> wrote:

>
> On 28 Feb 2017, at 6:39pm, Matthew Ceroni <[hidden email]> wrote:
>
> > After a bit of reading and troubleshooting I stumbled across the
> > busy_timeout option (which defaults to 0). Prior to execute the create
> > table statement I set PRAGMA busy_timeout=1000 (1 s) and the writes
> always
> > succeeded.
>
> You need to set the busy timeout at every connection to the database,
> including those which only read and never write.  It’s common to see it
> done immediately after the sqlite3_open() command.  It is not stored in the
> database and new connections to the database do not know what it was set to
> before.
>
> If that doesn’t fix things, as a diagnostic measure, try setting
> busy_timeout to 30 seconds instead of just 1 second.  Learning whether this
> does or does not fix the problem will tell you a lot about what’s going on.
>
> Are you using WAL mode ?  If not, you should try it.  Locking works
> differently in WAL files, as described in
>
> <https://www.sqlite.org/wal.html>
>
> > So since busy_timeout defaults to 0, all write attempts if a lock can't
> be
> > obtained will return SQLITE_BUSY immediately. Where does the PENDING lock
> > come into play here? I thought the PENDING was meant to be an
> intermediary
> > step before EXCLUSIVE. Does the busy_timeout impact the writers attempt
> to
> > obtain PENDING? Or does the busy_timeout trigger after X amount of ms
> > between obtaining PENDING and trying to move to EXCLUSIVE?
>
> There are two kinds of locks: shared and exclusive.  A pending lock means
> you have shared, not exclusive, but that you want exclusive.
>
> timeout applies when obtaining both shared and exclusive locks.  Actually,
> hold on, I’m not sure about this.  Better to try what I wrote already then
> get an expert to explain things.
>
> 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
|  
Report Content as Inappropriate

Re: SQLite 3 locking

Simon Slavin-3

On 1 Mar 2017, at 12:29am, Matthew Ceroni <[hidden email]> wrote:

> Appreciate the reply. I just found it odd that busy_timeout is set to 0 (so
> fail immediately) considering the intermediary PENDING stage. That stage is
> essentially useless with the default busy timeout of 0 no?

Only if its first attempt at grabbing the lock fails.  Which, even when you have many threads/processes accessing the database is rare.

But yes, a value of 0 for that setting does look a little strange.  But I can’t think of a default which would work well for all setups, and having the default be zero forces programmers to learn about the setting and think about what the best value would be for their setup.

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
|  
Report Content as Inappropriate

Re: SQLite 3 locking

Matthew Ceroni
Once PENDING is obtained, what time out value controls how long to wait to
get EXCLUSIVE?

On Feb 28, 2017 4:33 PM, "Simon Slavin" <[hidden email]> wrote:

>
> On 1 Mar 2017, at 12:29am, Matthew Ceroni <[hidden email]> wrote:
>
> > Appreciate the reply. I just found it odd that busy_timeout is set to 0
> (so
> > fail immediately) considering the intermediary PENDING stage. That stage
> is
> > essentially useless with the default busy timeout of 0 no?
>
> Only if its first attempt at grabbing the lock fails.  Which, even when
> you have many threads/processes accessing the database is rare.
>
> But yes, a value of 0 for that setting does look a little strange.  But I
> can’t think of a default which would work well for all setups, and having
> the default be zero forces programmers to learn about the setting and think
> about what the best value would be for their setup.
>
> 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
|  
Report Content as Inappropriate

Re: SQLite 3 locking

Simon Slavin-3

On 1 Mar 2017, at 12:40am, Matthew Ceroni <[hidden email]> wrote:

> Once PENDING is obtained, what time out value controls how long to wait to
> get EXCLUSIVE?

There is only the one timeout value for each connection: the one you set.  Each attempt to escalate the lock level can take up to the timeout value before it returns an error code.  I think.  You’re at the limits of my knowledge here.

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
|  
Report Content as Inappropriate

Re: SQLite 3 locking

Matthew Ceroni
Appreciate it. Have a much better picture now.

Thanks

On Feb 28, 2017 4:48 PM, "Simon Slavin" <[hidden email]> wrote:

>
> On 1 Mar 2017, at 12:40am, Matthew Ceroni <[hidden email]> wrote:
>
> > Once PENDING is obtained, what time out value controls how long to wait
> to
> > get EXCLUSIVE?
>
> There is only the one timeout value for each connection: the one you set.
> Each attempt to escalate the lock level can take up to the timeout value
> before it returns an error code.  I think.  You’re at the limits of my
> knowledge here.
>
> 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
|  
Report Content as Inappropriate

Re: SQLite 3 locking

Domingo Alvarez Duarte
Hello !

On this snippet you can play with the parameters and find a value that
can give a good result for a workload:

https://gist.github.com/mingodad/79225c88f8dce0f174f5

I did it to test sqlite3 wall mode but it also work without it and with
disk/memory databases.

Cheers !


On 28/02/17 21:56, Matthew Ceroni wrote:

> Appreciate it. Have a much better picture now.
>
> Thanks
>
> On Feb 28, 2017 4:48 PM, "Simon Slavin" <[hidden email]> wrote:
>
>> On 1 Mar 2017, at 12:40am, Matthew Ceroni <[hidden email]> wrote:
>>
>>> Once PENDING is obtained, what time out value controls how long to wait
>> to
>>> get EXCLUSIVE?
>> There is only the one timeout value for each connection: the one you set.
>> Each attempt to escalate the lock level can take up to the timeout value
>> before it returns an error code.  I think.  You’re at the limits of my
>> knowledge here.
>>
>> 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
|  
Report Content as Inappropriate

Re: SQLite 3 locking

Rowan Worth-2
In reply to this post by Matthew Ceroni
On 1 March 2017 at 02:39, Matthew Ceroni <[hidden email]> wrote:

>
> So since busy_timeout defaults to 0, all write attempts if a lock can't be
> obtained will return SQLITE_BUSY immediately. Where does the PENDING lock
> come into play here? I thought the PENDING was meant to be an intermediary
> step before EXCLUSIVE. Does the busy_timeout impact the writers attempt to
> obtain PENDING? Or does the busy_timeout trigger after X amount of ms
> between obtaining PENDING and trying to move to EXCLUSIVE?
>

The writer will be able to grab the PENDING lock immediately, because it
already has the RESERVED lock. The PENDING lock will cause any subsequent
read transactions to fail (with SQLITE_BUSY), but doesn't affect active
read transactions. Thus escalating to EXCLUSIVE can still fail with
SQLITE_BUSY when active readers are present.

Usually this happens when committing a transaction (unless the transaction
exceeds sqlite's memory cache causing it to start writing the DB
mid-transaction). If COMMIT fails with SQLITE_BUSY, the transaction
*remains open*. The application can retry the COMMIT at a later stage (when
hopefully the readers have finished).

So even with a busy_timeout of zero PENDING serves a purpose. And even with
a non-zero busy_timeout COMMIT can fail with SQLITE_BUSY (if there is an
active read transaction which runs for longer than the specified timeout).


It looks like the timer is reset for each call to sqlite3_step.


Note that when waiting for a lock via busy_timeout, the thread is
essentially uninterruptible (sqlite3_interrupt has no effect).

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