[sqlite] Problem with locked database

[ALBERT Aurélien]

Thanks you both.

I'm already using a timeout of 1 minute.

My "Database is locked" errors only happen in the following situation :

- I run an import thread, which does a lot of write
- this thread uses a transaction : BEGIN at import start, COMMIT/ROLLBACK at 
import end
- during the import (which is very long to execute, sometimes several hours), 
another thread execute reads on the same database (different connection) 
because of user actions on the main GUI

I didn't understood the documentation about transactions and transactions mode 
very well, so thanks for clarifying the situation 4/ to me.

So now I will :
 - use WAL mode
 - or don't use a transaction during import (by there is performances loss and 
other drawbacks)
 - or maybe import in a temporary database and the lock the destination 
database just the time of copying back imported data to it
 - or commit the import transaction periodically, maybe every "n" seconds or 
every "n" imported data

I don't really know which strategy is the best, I will experiment (WAL mode 
seems the easier, but there is maybe other drawbacks)



-Message d'origine-
De?: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] De la part de R.Smith
Envoy??: mardi 3 novembre 2015 19:43
??: sqlite-users at mailinglists.sqlite.org
Objet?: Re: [sqlite] Problem with locked database



On 2015-11-03 06:31 PM, ALBERT Aur?lien wrote:
> Hi,
>
> I'm often getting the "Database is locked" error message and I don't really 
> understand what can lead to this.
>
> I'm using these settings :
>- sqlite3_config(SQLITE_CONFIG_MULTITHREAD)
>- multiple threads
>- only one sqlite connection per thread
>- each thread have a different connection instance
>
> Could anyone clarify some things for me ?
>
> 1/ Can different threads perform read at the same time (I think yes)
Yes - at least, SQLite will handle the requests in a way that seems 
simultaneous.

> 2/ Can different threads perform write at the same time (I think no)
Nothing can write simultaneously

> 3/ Can a thread perform read while another single one is performing 
> write ? (I think yes)
Depends on the transaction and Journaling mode. See:
https://www.sqlite.org/lockingv3.html#writing
https://www.sqlite.org/lockingv3.html#rollback
In fact, that entire page should be of interest to you.

Also, WAL journal mode is inherently good at this. See:
https://www.sqlite.org/wal.html#concurrency

> 4/ Can a thread perform read while another thread has started a 
> transaction ? (I think yes)
Only if it isn't an exclusive transaction and has not started a write 
operation, or WAL mode is used, or it is the same connection with 
read_uncommitted turned on... actually, there are a lot of "if"s here.

In general, it is best to view a "Transaction" as a locked state and plan 
accordingly, and if you absolutely need to read simultaneously from elsewhere, 
be prepared to take special steps in your code and read up on the modes.

> My "Database is locked" problem seems to only appears in situation number 4, 
> may I am wrong on this one ? Maybe on others ?

To put this shortly - you cannot do two things at the same time in a database.
Most contentions can be (and are) resolved internally with simple rules, but as 
soon as a more prominent contention appears (and there are many ways this could 
happen) then one reader/writer thread will have to wait a little bit until a 
previous is finished. SQLite's "timeout" setting (as mentioned and linked by 
Simon already) should fix 99% of these without making you add any extra code.

There may be one or two extreme cases or race conditions that requires special 
consideration, but mostly setting the timeout should suffice. If you still get 
locked problems - please post again.

Cheers,
Ryan


___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with locked database

[Simon Slavin]

On 4 Nov 2015, at 8:00am, ALBERT Aur?lien  
wrote:

> - this thread uses a transaction : BEGIN at import start, COMMIT/ROLLBACK at 
> import end
> - during the import (which is very long to execute, sometimes several hours), 
> another thread execute reads on the same database (different connection) 
> because of user actions on the main GUI

If this thread can generate a lot of commands then they all have to be 
committed when you do COMMIT.  This can last a long time and during that time 
other threads will be unable to access the database.  I believe SQLite should 
be issuing SQLITE_BUSY or SQLITE_LOCKED if the timeout fails but it's possible 
something is going wrong.

It is especially likely that something is going wrong if your database is held 
remotely.  For instance, on a disk mounted over the network using SMB or CIFS 
rather than a disk inside the computer running all your threads.

> - or commit the import transaction periodically, maybe every "n" seconds or 
> every "n" imported data

This is a common solution: commit either every "n" seconds (best if there can 
be user delays) or every "n" commands.

Another is that instead of issuing your commands to the database you store them 
somewhere ... a text file or an array of strings ... and then execute them all, 
inside one transaction, when it's time to COMMIT.

Which one of the above you use should depend on what you prefer to happen if 
the system crashes while writes are pending: do some of them or forget them all.

Simon.

[sqlite] Problem with locked database

[R.Smith]

On 2015-11-03 06:31 PM, ALBERT Aur?lien wrote:
> Hi,
>
> I'm often getting the "Database is locked" error message and I don't really 
> understand what can lead to this.
>
> I'm using these settings :
>- sqlite3_config(SQLITE_CONFIG_MULTITHREAD)
>- multiple threads
>- only one sqlite connection per thread
>- each thread have a different connection instance
>
> Could anyone clarify some things for me ?
>
> 1/ Can different threads perform read at the same time (I think yes)
Yes - at least, SQLite will handle the requests in a way that seems 
simultaneous.

> 2/ Can different threads perform write at the same time (I think no)
Nothing can write simultaneously

> 3/ Can a thread perform read while another single one is performing write ? 
> (I think yes)
Depends on the transaction and Journaling mode. See:
https://www.sqlite.org/lockingv3.html#writing
https://www.sqlite.org/lockingv3.html#rollback
In fact, that entire page should be of interest to you.

Also, WAL journal mode is inherently good at this. See:
https://www.sqlite.org/wal.html#concurrency

> 4/ Can a thread perform read while another thread has started a transaction ? 
> (I think yes)
Only if it isn't an exclusive transaction and has not started a write 
operation, or WAL mode is used, or it is the same connection with 
read_uncommitted turned on... actually, there are a lot of "if"s here.

In general, it is best to view a "Transaction" as a locked state and 
plan accordingly, and if you absolutely need to read simultaneously from 
elsewhere, be prepared to take special steps in your code and read up on 
the modes.

> My "Database is locked" problem seems to only appears in situation number 4, 
> may I am wrong on this one ? Maybe on others ?

To put this shortly - you cannot do two things at the same time in a 
database.
Most contentions can be (and are) resolved internally with simple rules, 
but as soon as a more prominent contention appears (and there are many 
ways this could happen) then one reader/writer thread will have to wait 
a little bit until a previous is finished. SQLite's "timeout" setting 
(as mentioned and linked by Simon already) should fix 99% of these 
without making you add any extra code.

There may be one or two extreme cases or race conditions that requires 
special consideration, but mostly setting the timeout should suffice. If 
you still get locked problems - please post again.

Cheers,
Ryan

[sqlite] Problem with locked database

[ALBERT Aurélien]

Hi,

I'm often getting the "Database is locked" error message and I don't really 
understand what can lead to this.

I'm using these settings :
  - sqlite3_config(SQLITE_CONFIG_MULTITHREAD)
  - multiple threads
  - only one sqlite connection per thread
  - each thread have a different connection instance

Could anyone clarify some things for me ?

1/ Can different threads perform read at the same time (I think yes)
2/ Can different threads perform write at the same time (I think no)
3/ Can a thread perform read while another single one is performing write ? (I 
think yes)
4/ Can a thread perform read while another thread has started a transaction ? 
(I think yes)

My "Database is locked" problem seems to only appears in situation number 4, 
may I am wrong on this one ? Maybe on others ?

Thanks.

[sqlite] Problem with locked database

[Simon Slavin]

On 3 Nov 2015, at 4:31pm, ALBERT Aur?lien  
wrote:

> I'm using these settings :
>  - sqlite3_config(SQLITE_CONFIG_MULTITHREAD)
>  - multiple threads
>  - only one sqlite connection per thread
>  - each thread have a different connection instance

You must set a timeout for each connection you open.  Use one of these:





I recommend you set a timeout of at least 20 seconds.

Simon.