[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

[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] Any database unique ID across multiple connections ?

[ALBERT Aurélien]

@ Stephan Beal

"Every instance of a :memory: db is a unique instance, so you cannot have 
multiple connections to a single :memory: db."

>> I know, this is one of the various reasons that made my solution not really 
>> ideal

@ Simon Slavin

"There's a PRAGMA for storing and retrieving a 'user-version' in the database:



It's not used by SQLite itself, you can use it to store any 32-bit signed 
integer.  So you could store a different number to each of your databases and 
check to see whether the user-version of one connection is the same as that 
from a different connection."

>> That's a good idea, but this 'user-version' pragma is persistent and stored 
>> on disk. So it's difficult to use in my case : if the user quit the 
>> application and restart it without loading the same databases (or in the 
>> same order) assigning 'user-version' id will be really difficult.

In fact, I expected something like a unique memory address (void*) per database.

Thanks.

[sqlite] Any database unique ID across multiple connections ?

[ALBERT Aurélien]

Hi,

I'm using SQLite C API and my application use multiple databases, each database 
having multiple connections.

I need to identify which connection use the same database as another connection.

For the moment, I use the absolute database filename as an "unique database 
identifier", and I store this information with my "sqlite3*" connection handle.

Is there any simpler/safer way to get a unique database identifier across 
multiple connections ? (for example, if one day I need the same about 
":memory:" databases, bad things are gonna to happen)

Maybe there is already a database identifier in the SQLite API and I missed it ?

Thanks.

[sqlite] SQLite in multi-thread application

[ALBERT Aurélien]

Hi,

I'm using SQLite v3.8.8.3 in my muli-threaded application.

SQLite is configured so these asserts are satisfied :

assert(sqlite3_threadsafe() > 0);
assert(sqlite3_config(SQLITE_CONFIG_MULTITHREAD) == SQLITE_OK);

I have multiple connections to the same database file :
-   1 single connection is used by only a single thread
-   1 single thread use only 1 connection
-   All threads (about 4-8 threads) can read simultaneously
-   Using a mutex, only a single thread can write to the database (but 
reads can happen during this time)

But I have sometimes "Database is locked" errors.

Did I miss something in my configuration ?
Did I miss something in my mutex protection ?

Thanks for your help !