Question about threadsafe

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
25 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Question about threadsafe

Nick
I use sqlite 3.16.2 in Android system and I have configured journal_mode=WAL,
threadsafe=1.

My understanding is that:
        WAL => readers and only one writer can run at the same time.
        threadsafe=1 => mutex is used in serialized mode so that two writers is
supported.
Is it correct?

But I ran a simple test:
Two processes will run sqlite3_open() respectively to open the same db. Then
both of the two processes will insert 10000 records(in Transaction) into the
db simultaneously.
But I find that:

Process A begin
Process A insert
            Process B begin
            Process B insert
Process A end
            Process B end

Which I guess the Process B did not sleep at all?
And the count of records is less than 20000 at last.

So I think multiple write threads is not allowed even though I use
threadsafe=1 in wal mode?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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] Question about threadsafe

Hick Gunter
More than one concurrent writer is not supported. WAL mode only allows readers to see the state of the db as it was at the start of their transaction while writers' changes are written to the WAL file.

Threadsafe refers to the interoperation of multiple threads within a single process. Single thread means that only one thread of a process can use SQLite at any time. Multithread means that several threads within a single proces may cal SQLite, but only one thread at a time is allowed per connection. Serilaized means that any thread can call SQLite for any connection at any time, but the second caller will block until the first one returns from the library.

What you tested is multiple processes accessing the same db file. This uses the normal locking protocol. The first process to establish a write lock will proceed with its work until the transaction ends (commit or rollback), releasing the lock that the second process was waiting for. Only then will the second process be able to continue.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nick
Gesendet: Dienstag, 06. Februar 2018 12:52
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] Question about threadsafe

I use sqlite 3.16.2 in Android system and I have configured journal_mode=WAL, threadsafe=1.

My understanding is that:
        WAL => readers and only one writer can run at the same time.
        threadsafe=1 => mutex is used in serialized mode so that two writers is supported.
Is it correct?

But I ran a simple test:
Two processes will run sqlite3_open() respectively to open the same db. Then both of the two processes will insert 10000 records(in Transaction) into the db simultaneously.
But I find that:

Process A begin
Process A insert
            Process B begin
            Process B insert
Process A end
            Process B end

Which I guess the Process B did not sleep at all?
And the count of records is less than 20000 at last.

So I think multiple write threads is not allowed even though I use
threadsafe=1 in wal mode?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: Question about threadsafe

Simon Slavin-3
In reply to this post by Nick
On 6 Feb 2018, at 11:52am, Nick <[hidden email]> wrote:

> But I ran a simple test:
> Two processes will run sqlite3_open() respectively to open the same db. Then
> both of the two processes will insert 10000 records(in Transaction) into the
> db simultaneously.
> But I find that:
>
> Process A begin
> Process A insert
>            Process B begin
>            Process B insert
> Process A end
>            Process B end
>
> Which I guess the Process B did not sleep at all?
> And the count of records is less than 20000 at last.

You should not be able to get less than 20000 rows without either (a) an error result of some kind or (b) a corrupt database.

Are your processes using the same database connection or does each one have its own ?

Are you checking the result codes returned by all the API calls ?

Can you reliably get less than 20000 rows ?

Does the problem go away if you use threadsafe = 2 ?

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: Question about threadsafe

curmudgeon
In reply to this post by Nick
Don’t suppose you used ‘INSERT IGNORE’ and the inserts contained duplicate keys?

From: Nick<mailto:[hidden email]>
Sent: 06 February 2018 11:52
To: [hidden email]<mailto:[hidden email]>
Subject: [sqlite] Question about threadsafe

I use sqlite 3.16.2 in Android system and I have configured journal_mode=WAL,
threadsafe=1.

My understanding is that:
        WAL => readers and only one writer can run at the same time.
        threadsafe=1 => mutex is used in serialized mode so that two writers is
supported.
Is it correct?

But I ran a simple test:
Two processes will run sqlite3_open() respectively to open the same db. Then
both of the two processes will insert 10000 records(in Transaction) into the
db simultaneously.
But I find that:

Process A begin
Process A insert
            Process B begin
            Process B insert
Process A end
            Process B end

Which I guess the Process B did not sleep at all?
And the count of records is less than 20000 at last.

So I think multiple write threads is not allowed even though I use
threadsafe=1 in wal mode?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: [EXTERNAL] Question about threadsafe

Nick
In reply to this post by Hick Gunter
Yep, Hick. We have the same understanding.
But all I found is that process B did not wait for the lock and began to run
directly.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: Question about threadsafe

Nick
In reply to this post by Simon Slavin-3
>  (a) an error result of some kind or (b) a corrupt database.
I did not see any info about errmsg.

>  Are your processes using the same database connection or does each one
> have its own ?
Two processes have two sqlite3_open(). So each one has its own.

>  Are you checking the result codes returned by all the API calls ?
Yes. I use speedtest1.c as model code.
      speedtest1_exec("BEGIN");
      speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2,?3); --  %d times",
n);
      for(i=1; i<=n; i++){
        rc = sqlite3_bind_int64(g.pStmt, 1, i);
        rc = sqlite3_bind_int(g.pStmt, 2, i);
        rc = sqlite3_bind_text(g.pStmt, 3, "hello", -1, SQLITE_STATIC);
        speedtest1_run();
      }
      speedtest1_exec("COMMIT");
And I have checked rc = SQLITE_OK.

>  Can you reliably get less than 20000 rows ?
Yes, always less than 20000.
Process A inserts 1-10000 and process B inserts 10001-20000. I found that
the first few rows is missing in the result. I mean there is no 10001-10xxx.

>  Does the problem go away if you use threadsafe = 2 ?
The problem is still here.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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] Question about threadsafe

Hick Gunter
In reply to this post by Nick
If you wrap your INSERTS into a single transaction, then one process wins and the loser waits until the winner is finished.

If each INSERT is ist own (automatic) transaction, the processes will win/loose on each single INSERT, giving the appearance of two simultaneous writers, while actually they are always serializing.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Nick
Gesendet: Mittwoch, 07. Februar 2018 03:25
An: [hidden email]
Betreff: Re: [sqlite] [EXTERNAL] Question about threadsafe

Yep, Hick. We have the same understanding.
But all I found is that process B did not wait for the lock and began to run directly.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: [EXTERNAL] Question about threadsafe

Nick
Yes. But

Process A begin
Process A insert
            Process B begin
            Process B insert
Process A end
            Process B end

In fact, begin means "BEGIN" and end means "COMMIT".
So I think the result is strange.

And I guess the difference between Serilaized and Multithread is that if it
is allowed to shared the structure sqlite3 *db (together with prepared
statement) among threads. If I use Serilaized mode, then I could run
sqlite3_open(db) for only one time and all the threads could use the unique
"db". Is it right?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: Question about threadsafe

Kees Nuyt
In reply to this post by Nick
On Tue, 6 Feb 2018 19:33:10 -0700 (MST), Nick
<[hidden email]> wrote:

>>  (a) an error result of some kind or (b) a corrupt database.
>
> I did not see any info about errmsg.

Your code doesn't check the returncode of the sqlite3_* calls.

>>  Are your processes using the same database connection or does each one
>> have its own ?
>
> Two processes have two sqlite3_open(). So each one has its own.
>
>>  Are you checking the result codes returned by all the API calls ?
>
> Yes. I use speedtest1.c as model code.
>      speedtest1_exec("BEGIN");
>      speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2,?3); --  %d times",
> n);
>      for(i=1; i<=n; i++){
>        rc = sqlite3_bind_int64(g.pStmt, 1, i);
>        rc = sqlite3_bind_int(g.pStmt, 2, i);
>        rc = sqlite3_bind_text(g.pStmt, 3, "hello", -1, SQLITE_STATIC);
>        speedtest1_run();
>      }
>      speedtest1_exec("COMMIT");
>
> And I have checked rc = SQLITE_OK.

I assume speedtest1_run() calls sqlite3_step(), but does it
check the return code? I suspect _step() returns SQLITE_BUSY
sometimes.

>
>>  Can you reliably get less than 20000 rows ?
>
> Yes, always less than 20000.
> Process A inserts 1-10000 and process B inserts 10001-20000. I found that
> the first few rows is missing in the result. I mean there is no 10001-10xxx.
>
>>  Does the problem go away if you use threadsafe = 2 ?
>
> The problem is still here.

Because the test uses two processes, every process is
single-threaded, no risc of concurrency between threads.

PRAGMA busy_timeout might be helpful.


--
Regards,
Kees Nuyt
_______________________________________________
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] Re: Question about threadsafe

Hick Gunter
IF the error codes are checked AND two writers are simultaneously able to write to the db file, THEN the file locking is probably broken in the file system layer (e.g. network file systems), which SQLite can do nothing about.

In Journal mode, the first process to acquire a RESERVED lock will continue, and the other one will have to wait or return SQLITE_BUSY. When the process is ready to actually write to the file (as opposed to in-memory copies of pages), it needs to acquire a PENDING and then an EXCLUSIVE lock.

In WAL mode, only one process may append data to the WAL file at one time.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Kees Nuyt
Gesendet: Mittwoch, 07. Februar 2018 10:55
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] Question about threadsafe

On Tue, 6 Feb 2018 19:33:10 -0700 (MST), Nick <[hidden email]> wrote:

>>  (a) an error result of some kind or (b) a corrupt database.
>
> I did not see any info about errmsg.

Your code doesn't check the returncode of the sqlite3_* calls.

>>  Are your processes using the same database connection or does each
>> one have its own ?
>
> Two processes have two sqlite3_open(). So each one has its own.
>
>>  Are you checking the result codes returned by all the API calls ?
>
> Yes. I use speedtest1.c as model code.
>      speedtest1_exec("BEGIN");
>      speedtest1_prepare("INSERT INTO t1 VALUES(?1,?2,?3); --  %d
> times", n);
>      for(i=1; i<=n; i++){
>        rc = sqlite3_bind_int64(g.pStmt, 1, i);
>        rc = sqlite3_bind_int(g.pStmt, 2, i);
>        rc = sqlite3_bind_text(g.pStmt, 3, "hello", -1, SQLITE_STATIC);
>        speedtest1_run();
>      }
>      speedtest1_exec("COMMIT");
>
> And I have checked rc = SQLITE_OK.

I assume speedtest1_run() calls sqlite3_step(), but does it check the return code? I suspect _step() returns SQLITE_BUSY sometimes.

>
>>  Can you reliably get less than 20000 rows ?
>
> Yes, always less than 20000.
> Process A inserts 1-10000 and process B inserts 10001-20000. I found
> that the first few rows is missing in the result. I mean there is no 10001-10xxx.
>
>>  Does the problem go away if you use threadsafe = 2 ?
>
> The problem is still here.

Because the test uses two processes, every process is single-threaded, no risc of concurrency between threads.

PRAGMA busy_timeout might be helpful.


--
Regards,
Kees Nuyt
_______________________________________________
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
|

GMail blocking

R Smith-2
This is an FYI to GMail users only - if you don't use GMail, skip this
thread.

I've had a problem since using GMail as my delivery address for sqlite,
that near 30%  of all forum posts got channeled to spam and the Spam
folder wasn't automatically synced. The fix turned out to be quite
straight-forward, in case anyone else is affected by it:

In GMail, click the gear icon --> Settings --> Filters and Blocked
addresses.

-> Create a new filter.

In the Filter values box, put only the sqlite address
([hidden email]) in the "From:" line.

-> Create Filter with this search

-> In the displayed list of filter options, tick the box next to:  [ ]
Never send it to Spam

-> Create the Filter.


That's all there is to it.
Ryan






_______________________________________________
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: GMail blocking

Stephen Chrzanowski
I ran into this a couple of years ago.  To add to this, the only thing to
know about is that GMail (un)helpfully warns you that this was not sent to
junk because you marked it as not-junk.

Just marking mail as "Not Junk" doesn't help.

On Wed, Feb 7, 2018 at 6:18 AM, R Smith <[hidden email]> wrote:

> This is an FYI to GMail users only - if you don't use GMail, skip this
> thread.
>
> I've had a problem since using GMail as my delivery address for sqlite,
> that near 30%  of all forum posts got channeled to spam and the Spam folder
> wasn't automatically synced. The fix turned out to be quite
> straight-forward, in case anyone else is affected by it:
>
> In GMail, click the gear icon --> Settings --> Filters and Blocked
> addresses.
>
> -> Create a new filter.
>
> In the Filter values box, put only the sqlite address (
> [hidden email]) in the "From:" line.
>
> -> Create Filter with this search
>
> -> In the displayed list of filter options, tick the box next to:  [ ]
> Never send it to Spam
>
> -> Create the Filter.
>
>
> That's all there is to it.
> Ryan
>
>
>
>
>
>
> _______________________________________________
> 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: [EXTERNAL] Question about threadsafe

Keith Medcalf
In reply to this post by Nick

How did you start your processes?  ie, is each process doing an sqlite3_open() or are you forking and passing the same pointer to multiple processes?

What filesystem is the file located on?  Is it a local filesystem or a network filesystem?

Threadsafe is for protection against multiple entrance for threads WITHIN a single process.  
It does noting with respect to single threads in multiple processeses.

---
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 Nick
>Sent: Tuesday, 6 February, 2018 19:25
>To: [hidden email]
>Subject: Re: [sqlite] [EXTERNAL] Question about threadsafe
>
>Yep, Hick. We have the same understanding.
>But all I found is that process B did not wait for the lock and began
>to run
>directly.
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>_______________________________________________
>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: Question about threadsafe

Nick
In reply to this post by Nick
I ran several multi-threads tests these days and I want to get a confirmation
that my understanding is correct.
I use WAL mode and I think whether or not use the same connection with
THREADSAFE=1, 2 is the key to my question.

Mode 1, threadsafe=2 + multiple threads use the same connection:
It is not threadsafe;

Mode 2, threadsafe=2 + each thread runs a sqlite3_open():
It is threadsafe which means reading and writing can proceed concurrently,
but only one writer at a time. PRAGMA busy_timeout() may avoid “db is
locked” when writing.

Mode 3, threadsafe=1 + each thread runs a sqlite3_open():
Same with mode 2, as threadsafe=1 is only supported the ability of a handle
to be used
by more than one thread.

Mode 4, threadsafe=1 + multiple threads use the same connection:
Reading and writing can proceed concurrently; Two writers can start at the
same time and sqlite will make them Serialized(but how? guess some threads
will be blocked and retry, but I can not find it in the source code).


In general, WAL make reading and writing concurrent - not just serial, but
writing and writing can only be serial. So writers should use busy_timeout()
to retry(Mode 2), or, use the same connection and the RETRY operation will
be done by sqlite(Mode 4).

I think sqlite is threadsafe means the integrity of database is guaranteed.
And there will not be any crash or corruption‎ if applications use sqlite
the way like mode 2 and 4 above.

Is it right?

Thanks.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: Question about threadsafe

Keith Medcalf

THREADSAFE is about re-entrancy control.  It has nothing to do with connections or transactions or how many/which threads can make sqlite3 calls (except in the case of THREADSAFE=0).

TRANSACTIONS are an attribute of a CONNECTION.  All operations on a CONNECTION (and statements derived/prepared from the same CONNECTION) occur within the same transaction.  This is not affected by the THREADSAFE setting (which is a re-entrance control).

The re-entrancy requirements are that only ONE call may be active inside the sqlite3 library on each connection (or object derived from a connection, such as a statement) at a time.  This means that if you derive/prepare 10 statements from the same CONNECTION, you may only step ONE of these statements at a time.  Ever.  No matter what.

THREADSAFE=0 means that there is only a single thread calling the sqlite3 library functions.  There can never therefore NEVER be multiple entrances into the sqlite3 library since only ONE thread is making calls into the library from a process (ie, the main thread).  If you lie about this and violate the rules, AHWBL.

THREADSAFE=1 means that the sqlite3 library enforces the re-entrancy requirements via mutexes attached to the CONNECTION object.  This means that the library will serialize access to the sqlite3 engine for you so that only one call (entrance) per connection is permitted to proceed.  Other entrances (calls) will wait until the in-progress call is complete before proceeding.

THREADSAFE=2 means that the sqlite3 library WILL NOT ENFORCE the re-entrancy requirements via mutexes attatched to the CONNECTION object.  The limitation of only ONE entrance per connection object at one time is still in effect however, so if you violate the rules then AHWBL.

If you share a CONNECTION amongst threads, then everything done on those threads occurs within the context of a single transaction.  If you use THREADSAFE=1, then sqlite3 will ensure that your threads will not attempts to concurrently access the same connection at the same time.  If you use THREADSAFE=2, the sqlite3 will not do this and *YOU* must ensure that you do not attempt to call an sqlite3 function on the same connection at the same time.  If you do not ensure this, AHWBL.

Therefore IF you use THREADSAFE=2 *AND* open a new connection dedicated to each thread, THEN you can be sure that you are meeting the entrance requirements provided that only calls against that connection (or objects derived therefrom) are made on the thread which owns that connection, and from NO OTHER THREAD.

Regardless of the THREADSAFE setting, transactions are an attribute of a CONNECTION and have nothing whatsoever to do with threads.  

In any journal mode other than WAL, there can be either multiple reader CONNECTIONS or ONE writer CONNECTION.  Readers block writers and the writer will block readers.

In WAL mode, readers do not block writer CONNECTIONS and writers do not block reader CONNECTIONS.  You may have multiple CONNECTIONS reading and one connection WRITING at any given time.  Database WRITES will not be visible to OTHER CONNECTIONS until (a) the writer commits and (b) the reader starts a new transaction after the WRITE is committed (whether an implicit or explicit transaction).


---
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 Nick
>Sent: Monday, 12 February, 2018 01:15
>To: [hidden email]
>Subject: Re: [sqlite] Question about threadsafe
>
>I ran several multi-threads tests these days and I want to get a
>confirmation
>that my understanding is correct.
>I use WAL mode and I think whether or not use the same connection
>with
>THREADSAFE=1, 2 is the key to my question.
>
>Mode 1, threadsafe=2 + multiple threads use the same connection:
>It is not threadsafe;
>
>Mode 2, threadsafe=2 + each thread runs a sqlite3_open():
>It is threadsafe which means reading and writing can proceed
>concurrently,
>but only one writer at a time. PRAGMA busy_timeout() may avoid “db is
>locked” when writing.
>
>Mode 3, threadsafe=1 + each thread runs a sqlite3_open():
>Same with mode 2, as threadsafe=1 is only supported the ability of a
>handle
>to be used
>by more than one thread.
>
>Mode 4, threadsafe=1 + multiple threads use the same connection:
>Reading and writing can proceed concurrently; Two writers can start
>at the
>same time and sqlite will make them Serialized(but how? guess some
>threads
>will be blocked and retry, but I can not find it in the source code).
>
>
>In general, WAL make reading and writing concurrent - not just
>serial, but
>writing and writing can only be serial. So writers should use
>busy_timeout()
>to retry(Mode 2), or, use the same connection and the RETRY operation
>will
>be done by sqlite(Mode 4).
>
>I think sqlite is threadsafe means the integrity of database is
>guaranteed.
>And there will not be any crash or corruption‎ if applications use
>sqlite
>the way like mode 2 and 4 above.
>
>Is it right?
>
>Thanks.
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>_______________________________________________
>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: Question about threadsafe

Nick
Thank you Keith. And there are something I want to make sure.

>THREADSAFE=1 means that the sqlite3 library enforces the re-entrancy
requirements via mutexes attached to the CONNECTION object.  This means that
the library will serialize access to the sqlite3 engine for you so that only
one call (entrance) per connection is permitted to proceed.  
Yes. That's the reason why I think my applications can use "threadsafe=1 +
share one connection" directly and sqlite will meeting the entrance
requirements by itself.

>Other entrances (calls) will *wait* until the in-progress call is complete
before proceeding.
I see the word "wait". But could you give me any advises to find the code
when sqlite waiting or retrying?

>THREADSAFE=2 means that the sqlite3 library WILL NOT ENFORCE the
re-entrancy requirements via mutexes attatched to the CONNECTION object.
Yes. So I can not use "threadsafe=2 + share one connection" in my apps, as I
may have more than one call at a time.

>The limitation of only ONE entrance per connection object at one time is
still in effect however, so if you violate the rules then AHWBL.
I want to make sure that it is not OK to read and write concurrently within
the same transaction(two thread share one connection), but it is OK to read
and write concurrently if I have two different connection and use WAL. Is it
correct?

>Therefore IF you use THREADSAFE=2 *AND* open a new connection dedicated to
each thread, THEN you can be sure that you are meeting the entrance
requirements provided that only calls against that connection (or objects
derived therefrom) are made on the thread which owns that connection, and
from NO OTHER THREAD.
Sorry, I still can not understand. Um, or, is it OK to use "threadsafe=2 and
2 connections" in my apps if the 2 threads may write at the same time?

thanks.







--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: Question about threadsafe

Simon Slavin-3
On 13 Feb 2018, at 3:49am, Nick <[hidden email]> wrote:

> is it OK to use "threadsafe=2 and
> 2 connections" in my apps if the 2 threads may write at the same time?

Yes.

<https://sqlite.org/compile.html#threadsafe>

"When compiled with SQLITE_THREADSAFE=2, SQLite can be used in a multithreaded program so long as no two threads attempt to use the same database connection (or any prepared statements derived from that database connection) at the same time."

If your threads are using different database connections it's fine.  If one connection has a lock on the database SQLite will cause the other one to wait until the database is free again.  You should set the timeout to a minute or two using

<https://sqlite.org/c3ref/busy_timeout.html>

        int sqlite3_busy_timeout(sqlite3*, int ms);

or

<https://sqlite.org/pragma.html#pragma_busy_timeout>

        PRAGMA busy_timeout = milliseconds;

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: Question about threadsafe

Nick
>> is it OK to use "threadsafe=2 and
>> 2 connections" in my apps if the 2 threads may write at the same time?

>Yes.

So I think "threadsafe=2 + more than 1 connection + busy_handler" is a good
way to use.

Another possible way is "threadsafe=1 and share 1 connection", but if thread
1 begins a transaction, then the SQL of thread 2 will also be executed
within the transaction I guess. That may cause some unpredictable problems.

BTW, if I use "threadsafe=0 and more than 1 connection", there will not be
"database is locked" any more even if two threads writing at the same time,
as mutex is disabled on core. Is it correct?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: Question about threadsafe

Simon Slavin-3
On 13 Feb 2018, at 9:14am, Nick <[hidden email]> wrote:

> So I think "threadsafe=2 + more than 1 connection + busy_handler" is a good
> way to use.

This is the normal way to use SQLite.

> Another possible way is "threadsafe=1 and share 1 connection", but if thread
> 1 begins a transaction, then the SQL of thread 2 will also be executed
> within the transaction I guess. That may cause some unpredictable problems.

SQLite does not know about your threads.  It expects each connection to be used only in sequences which make sense.

> BTW, if I use "threadsafe=0 and more than 1 connection", there will not be
> "database is locked" any more even if two threads writing at the same time,
> as mutex is disabled on core. Is it correct?

threadsafe=0 disables mutexes / locking.  This makes SQLite faster.  But you should also think of it as using other tricks to speed up SQLite.  And these tricks are unsafe if you try to use SQLite from two threads at once.

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: Question about threadsafe

Keith Medcalf
In reply to this post by Nick

THREADSAFE has NOTHING to do with transactions.  Repeat, there is no value to which you can set the THREADSAFE constant which has any effect whatsoever on transactions.

Transactions are commenced ON A CONNECTION with either (a) implicitly as required if you do not do it yourself (known as "magical mystery mode" or "hope and pray" mode) or (b) when one of the BEGIN transaction statements is PREPARED and STEPped to completion on a CONNECTION.  That connection and all threads and statements associated with or prepared from that CONNECTION are now part of the transaction on that CONNECTION.  The transaction is ended when the "last active statement" is reset on the connection (for transactions that are implicitly commenced by sqlite3 and not explicitly by you) or when you prepare and step to completion a COMMIT or ROLLBACK statement on the CONNECTION.

Nothing in the above paragraph is affected by the THREADSAFE setting, the phase of the moon, or the depth of the snow on the ground.



The THREADSAFE setting determines the "sloppiness" of the programming style you use to interact with the sqlite3 library.  

THREADSAFE=0 means that your slopiness does not matter because the program is SINGLE-THREADED and you will make calls only from a SINGLE (MAIN) thread.

THREADSAFE=1 means that you are likely sloppy and that sqlite3 itself will ensure that you do not, through your slopiness, cause AHTBL.

THREADSAFE=2 means that you are extra careful to make sure that you NEVER EVER have the possibility of multiple entrances to the sqlite3 library on the same CONNECTION (which would require multiple threads, or multiple fibres, or just an OS that plays dipsy poodle (such as Windows)).  In case you are not sufficiently careful in your design and programming, sqlite3 WILL NOT take precautions to prevent you from killing yourself, your data, your application, or your database file (in other words, sqlite3 will NOT prevent AHTBL if you happen to be in actuality sloppy in your design or implementation)

Nothing in the above 4 paragraphs is affected by the TRANSACTION state of a CONNECTION, the phase of the moon, or the depth of the snow on the ground.


---
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 Nick
>Sent: Tuesday, 13 February, 2018 02:14
>To: [hidden email]
>Subject: Re: [sqlite] Question about threadsafe
>
>>> is it OK to use "threadsafe=2 and
>>> 2 connections" in my apps if the 2 threads may write at the same
>time?
>
>>Yes.
>
>So I think "threadsafe=2 + more than 1 connection + busy_handler" is
>a good
>way to use.
>
>Another possible way is "threadsafe=1 and share 1 connection", but if
>thread
>1 begins a transaction, then the SQL of thread 2 will also be
>executed
>within the transaction I guess. That may cause some unpredictable
>problems.
>
>BTW, if I use "threadsafe=0 and more than 1 connection", there will
>not be
>"database is locked" any more even if two threads writing at the same
>time,
>as mutex is disabled on core. Is it correct?
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>_______________________________________________
>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
12