[sqlite] Fw: new message

[ronny.dier...@telenet.be]

Hello!



New message, please read 



ronny.dierckx at telenet.be

[sqlite] Fw: new message

[ronny.dier...@telenet.be]

Hello!



New message, please read 



ronny.dierckx at telenet.be

[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] MultiThread Error 10 on Windows Mobile 6.x

[Joe Mistachkin]

Jim Morris wrote:
>
> Is there a graceful work around?
>
>
>   (3850) SQLITE_IOERR_LOCK
>

Are any of the underlying database files read-only?

--
Joe Mistachkin

[sqlite] MultiThread Error 10 on Windows Mobile 6.x

[Jim Morris]

I have a connection used by the main (UI) thread and recently added a 
background thread, with own connection(no shared cache) to upload 
changes to server on Windows Mobile 6.0/6.5 devices and upgraded to 
SQLite 3.9.2.  The background thread is getting an error 10, extended 
error 3850 as a result of a step when reading a result set in the 
background thread.  I'm unclear on the cause of this error.

Is this likely to be a logic error in my code?   E.g. locking, 
statements not reset, transactions open or an OS related issue on the 
device.  Possibly unrelated, as I recall we needed to go to journal mode 
"PERSIST" years ago to resolve an OS NVFS issue.  Most of the reads are 
not in transactions and most of the writes are in transactions that are 
"BEGIN IMMEDIATE TRANSACTION".  Busy timeout is 30 seconds.

Anyone else still using Windows Mobile 6.x devices?

Is there a graceful work around?


  (3850) SQLITE_IOERR_LOCK

The SQLITE_IOERR_LOCK error code is an extended error code 
 for SQLITE_IOERR 
 indicating an I/O error in 
the advisory file locking logic. Usually an SQLITE_IOERR_LOCK error 
indicates a problem obtaining a PENDING lock 
. However it can 
also indicate miscellaneous locking errors on some of the specialized 
VFSes  used on Macs.

Thanks



On 11/3/2015 10:42 AM, R.Smith wrote:
>
>
> 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] 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.

[sqlite] how to empty a table with sqliteEstudio-2.0.26

[R.Smith]

On 2015-11-03 05:36 AM, Simon Slavin wrote:
> On 3 Nov 2015, at 3:23am, H?ctor Fiandor  wrote:
>
>> I have obtained the sqliteEstudio-2.0.26 and I  need to empty a table. Is
>> possible with this program? If not, how to do?
> I have no idea how that program works.  It is not SQLite, it is a program 
> which uses SQLite.  If you can issue commands in it, you want
>
> DELETE FROM myTable;
>
> which will delete all rows from the table.
>
> Simon.

Just a quick added note for the OP and in case anyone is following along...

Though others exist, Simon's suggestion is the very best solution 
specific to SQLite because the DB engine has optimizations for dealing 
(and efficiently executing) queries specifically formed like "DELETE 
FROM xxx;" to simply clean the table (as opposed to really removing the 
entries one by one).

Note: Using this method, if an auto-incrementing Primary key was used, 
the increment will remain where it was before and will not reset to the 
start. A PK without AUTOINCREMENT should reset. To reset an Auto-Inc 
table:  drop and recreate or change the corresponding value in the 
"sqlite_sequence" table. (Not sure now - this may require schema write 
access).

[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?

[jh...@gmx.com]

Adding to my own comment, the mechanical properties of the disc. If you're 
reading from a solid state device this does not apply, however with magnetic 
spinning  discs there's a read head that has to move around and if you're 
reading from four different locations in the same file and the files big 
enough, then you'll be generating a lot of motion with the read head. Your OS 
will attempt to collect the reads into as little motion as possible, however 
you also have to wait for the sector the come under the head once the read head 
is in position.

If throughput is the concern and not latency,  then I would suggest you queue 
all of your queries,  otherwise you'll just have to except that with reduced 
latency there is reduced performance.

-Original message-
Sent: Tuesday, 03 November 2015 at 09:28:33
From: jh...@gmx.com
To: "Stephen Chrzanowski" ,"SQLite mailing list" 

Subject: Re: [sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?
To add to what other people are saying here, it is very likely in the 
additional threads result in reduced overall performance because rather than 
having one thread have the areas of disk cache where it's reading, you know 
have multiple threads  each at their own locations in the file such that your 
disk cache is divided among all of your threads so you actually increase 
resource contention. As a result your performance is not just divided by the 
number of friends but is lower than total performance / number of threads.  

It is also true that in most cases the general optimization of read ahead 
caches at the OS level is exactly the opposite behavior was desired for a 
database. When you're doing table stands having A most recently used buffer 
until it fills the buffers with useless data. Ideally you would one day most 
recent in/first out buffer.

-Original message-
Sent: Monday, 02 November 2015 at 05:48:47
From: "Stephen Chrzanowski" 
To: "SQLite mailing list" 
Subject: Re: [sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?
@sanhua> If you can, just for testing, use the SQLite backup mechanism to
copy the database to memory, then run your transactions off the memory
version.  This will get rid of disk IO with the exclusion of the OS
swapping to memory.  This will tell you if you're running into some kind of
thread locking or if you're running into a disk IO issue.

I have absolutely no clue about the back end of iOS (Other than it is linux
based) so I don't know what it offers for threaded operations, or if the
library you're using has threaded capabilities.



On Sun, Nov 1, 2015 at 11:25 PM, Simon Slavin  wrote:

>
> On 2 Nov 2015, at 3:48am, sanhua.zh  wrote:
>
> > I thought it might be storage contention, too.
> > BUT, as the documentation of SQLite said, in ?DELETE? mode, SELECTing do
> read for disk only.
>
> Even reading needs the attention of the disk.  You tell the disk what you
> want to read and it has to find that piece of disk, read it, and give the
> result to you.  It cannot answer four requests at once so while it's
> answering one, the other threads have to wait.
>
> (Yes disk is cached.  But that just moves the problem from the hard disk
> to the piece of software which handles the cache.  It cannot answer four
> questions at once.)
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?

[jh...@gmx.com]

To add to what other people are saying here, it is very likely in the 
additional threads result in reduced overall performance because rather than 
having one thread have the areas of disk cache where it's reading, you know 
have multiple threads  each at their own locations in the file such that your 
disk cache is divided among all of your threads so you actually increase 
resource contention. As a result your performance is not just divided by the 
number of friends but is lower than total performance / number of threads.  

It is also true that in most cases the general optimization of read ahead 
caches at the OS level is exactly the opposite behavior was desired for a 
database. When you're doing table stands having A most recently used buffer 
until it fills the buffers with useless data. Ideally you would one day most 
recent in/first out buffer.

-Original message-
Sent: Monday, 02 November 2015 at 05:48:47
From: "Stephen Chrzanowski" 
To: "SQLite mailing list" 
Subject: Re: [sqlite] Why SQLite take lower performanceinmulti-threadSELECTing?
@sanhua> If you can, just for testing, use the SQLite backup mechanism to
copy the database to memory, then run your transactions off the memory
version.  This will get rid of disk IO with the exclusion of the OS
swapping to memory.  This will tell you if you're running into some kind of
thread locking or if you're running into a disk IO issue.

I have absolutely no clue about the back end of iOS (Other than it is linux
based) so I don't know what it offers for threaded operations, or if the
library you're using has threaded capabilities.



On Sun, Nov 1, 2015 at 11:25 PM, Simon Slavin  wrote:

>
> On 2 Nov 2015, at 3:48am, sanhua.zh  wrote:
>
> > I thought it might be storage contention, too.
> > BUT, as the documentation of SQLite said, in ?DELETE? mode, SELECTing do
> read for disk only.
>
> Even reading needs the attention of the disk.  You tell the disk what you
> want to read and it has to find that piece of disk, read it, and give the
> result to you.  It cannot answer four requests at once so while it's
> answering one, the other threads have to wait.
>
> (Yes disk is cached.  But that just moves the problem from the hard disk
> to the piece of software which handles the cache.  It cannot answer four
> questions at once.)
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to empty a table with sqliteEstudio-2.0.26

[Simon Slavin]

On 3 Nov 2015, at 3:23am, H?ctor Fiandor  wrote:

> I have obtained the sqliteEstudio-2.0.26 and I  need to empty a table. Is
> possible with this program? If not, how to do?

I have no idea how that program works.  It is not SQLite, it is a program which 
uses SQLite.  If you can issue commands in it, you want

DELETE FROM myTable;

which will delete all rows from the table.

Simon.