Client/Server Best Practices

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Client/Server Best Practices

Rick Kelly
I'm using SQLite3 for the first time and am looking to see if those more experienced can look at my deployment and offer suggestions.

I have a client/server application running on MS Windows communicating over TCP/IP supporting a few dozen clients. Basically, clients send a request to the server and the SQL is built, submitted and results returned.

The database file is located in the same folder as the server. The server is multi-threaded. I'm using library version 3.8.7.4

The database file is opened as follows:

1. sqlite3_enable_shared_cache(%SQLITE_OPEN_SHAREDCACHE)
2. Database is opened with %SQLITE_OPEN_READWRITE
3. PRAGMA's are set

PRAGMA JOURNAL_MODE=WAL;
PRAGMA WAL_AUTOCHECKPOINT=500;
PRAGMA SECURE_DELETE;
PRAGMA SYNCHRONOUS=FULL;
PRAGMA THREADS=0;

All SELECT type requests are wrapped with BEGIN TRANSACTION/COMMIT and UPDATE/INSERT/DELETE transactions are wrapped with BEGIN IMMEDIATE TRANSACTION/COMMIT. The server uses a connection pool and threads request a handle from the pool and return it to the pool immediately afterwards. The largest size of the connection pool and the number of open handles is limited to 5. In my stress testing, I've thrown thousands of requests at the server and the pool usually gets to two and rarely 3.

If a request encounters %SQLITE_BUSY or %SQLITE_LOCKED, I'm retrying up to 5 times with a 100ms sleep in between.

Will this approach minimize %SQLITE_BUSY and %SQLITE_LOCKED situations and provide decent concurrency and long term stability?

I haven't yet tried to serialize update requests so that only one at a time is active.

Rick Kelly
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

Richard Hipp-3
On Thu, Dec 25, 2014 at 7:32 AM, Rick Kelly <[hidden email]> wrote:

> I'm using SQLite3 for the first time and am looking to see if those more
> experienced can look at my deployment and offer suggestions.
>
> I have a client/server application running on MS Windows communicating over
> TCP/IP supporting a few dozen clients. Basically, clients send a request to
> the server and the SQL is built, submitted and results returned.
>

Do I understand correctly:  SQLite is running inside of the single server
process you have built?

In other words, you are not trying to access SQLite database files directly
from the clients across a network?


>
> The database file is located in the same folder as the server. The server
> is
> multi-threaded. I'm using library version 3.8.7.4
>
> The database file is opened as follows:
>
> 1. sqlite3_enable_shared_cache(%SQLITE_OPEN_SHAREDCACHE)
> 2. Database is opened with %SQLITE_OPEN_READWRITE
> 3. PRAGMA's are set
>
> PRAGMA JOURNAL_MODE=WAL;
> PRAGMA WAL_AUTOCHECKPOINT=500;
> PRAGMA SECURE_DELETE;
> PRAGMA SYNCHRONOUS=FULL;
> PRAGMA THREADS=0;
>
> All SELECT type requests are wrapped with BEGIN TRANSACTION/COMMIT and
> UPDATE/INSERT/DELETE transactions are wrapped with BEGIN IMMEDIATE
> TRANSACTION/COMMIT. The server uses a connection pool and threads request a
> handle from the pool and return it to the pool immediately afterwards. The
> largest size of the connection pool and the number of open handles is
> limited to 5. In my stress testing, I've thrown thousands of requests at
> the
> server and the pool usually gets to two and rarely 3.
>
> If a request encounters %SQLITE_BUSY or %SQLITE_LOCKED, I'm retrying up to
> 5
> times with a 100ms sleep in between.
>
> Will this approach minimize %SQLITE_BUSY and %SQLITE_LOCKED situations and
> provide decent concurrency and long term stability?
>
> I haven't yet tried to serialize update requests so that only one at a time
> is active.
>
> Rick Kelly
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Client-Server-Best-Practices-tp79728.html
> Sent from the SQLite mailing list archive at Nabble.com.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

Simon Slavin-3
In reply to this post by Rick Kelly

On 25 Dec 2014, at 12:32pm, Rick Kelly <[hidden email]> wrote:

> If a request encounters %SQLITE_BUSY or %SQLITE_LOCKED, I'm retrying up to 5
> times with a 100ms sleep in between.
>
> Will this approach minimize %SQLITE_BUSY and %SQLITE_LOCKED situations and
> provide decent concurrency and long term stability?

Do not do the retrying inside your own code.  Instead set a busy_timeout and let SQLite handle all retrying itself:

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

SQlite will then take care of all the retrying for you.  A timeout of a minute or two might seem strange, but if you think about what you'd want to happen if the database is busy it starts to make sense.

If SQLite is still reporting SQLITE_BUSY or SQLITE_LOCKED after this you can treat it as an unrecoverable error indicating hardware or network problems.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

Rick Kelly
In reply to this post by Richard Hipp-3
Clients know nothing about the database, they just submit a request and get results. The server is the only process accessing and using SQLite.

Rick Kelly
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

James K. Lowden
In reply to this post by Rick Kelly
On Thu, 25 Dec 2014 05:32:45 -0700 (MST)
Rick Kelly <[hidden email]> wrote:

> All SELECT type requests are wrapped with BEGIN TRANSACTION/COMMIT

That shouldn't be necessary and afaik isn't necessary.  SELECT does not
modify the database.  To "commit a select" is to apply the nonchanges.  

A common misconception is that BEGIN TRANSACTION "takes a lock" in some
sense.  It doesn't; it marks a point in logical time that will be
concluded with COMMIT/ROLLBACK.  Locks, if any, are implicit in the
SELECT itself.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

J Decker
On Fri, Jan 2, 2015 at 3:25 PM, James K. Lowden <[hidden email]>
wrote:

> On Thu, 25 Dec 2014 05:32:45 -0700 (MST)
> Rick Kelly <[hidden email]> wrote:
>
> > All SELECT type requests are wrapped with BEGIN TRANSACTION/COMMIT
>
> That shouldn't be necessary and afaik isn't necessary.  SELECT does not
> modify the database.  To "commit a select" is to apply the nonchanges.
>
> A common misconception is that BEGIN TRANSACTION "takes a lock" in some
> sense.  It doesn't; it marks a point in logical time that will be
> concluded with COMMIT/ROLLBACK.  Locks, if any, are implicit in the
> SELECT itself.
>

I think the idea is at begin transaction (selects....) is that the
transaction is left open so that state can continue to be queied while
other transactions are also created and commited or rolled back at the same
time (?)  ... like using a monotone version control database and having a
current 'head' which is what you get when you select...


>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

Keith Medcalf
In reply to this post by James K. Lowden

On Friday, 2 January, 2015 16:26, James K. Lowden <[hidden email]> said:


>On Thu, 25 Dec 2014 05:32:45 -0700 (MST) Rick Kelly <[hidden email]> wrote:

>> All SELECT type requests are wrapped with BEGIN TRANSACTION/COMMIT

>That shouldn't be necessary and afaik isn't necessary.  SELECT does not
>modify the database.  To "commit a select" is to apply the nonchanges.

It does not matter whether it modifies the database.  "reading" the database requires a lock -- a shared lock.  "updating" the database requires a "write" lock, which precludes obtaining a "shared" lock.

>A common misconception is that BEGIN TRANSACTION "takes a lock" in some
>sense.  It doesn't; it marks a point in logical time that will be
>concluded with COMMIT/ROLLBACK.  Locks, if any, are implicit in the
>SELECT itself.

BEGIN TRANSACTION does not acquire a lock -- BEGIN IMMEDIATE does that -- BEGIN TRANSACTION merely turns off autocommit, meaning that the lock will not be released magically, but rather by an explicit COMMIT (which itself does not do anything -- it merely turns autocommit back on so that the next statement will commit the transaction before magically acquiring a new lock).  However, execution of a SELECT statement does cause a lock to be obtained (a shared lock) and a COMMIT does cause that shared lock to be released.  Executing an UPDATE after a SELECT -- in the same connection -- (or while a select is in progress) will escalate the SHARED lock to a WRITE lock.  COMMIT will release "the lock" -- "the lock" is now a WRITE lock, not a shared lock.  Therefore the next _step() will be executing without any lock at all leading to apparently undefined results (really an error should be thrown "SQLITE_DATABASE_UNLOCKED_FROM_UNDER_ME" or a MISUSE error, but is not).

This is inherent in how WAL works.  Just because WAL is not in effect does not alter the fundamental workings of the transaction system.

I do not believe that there is a way to specify "COMMIT BUT MAINTAIN THE SHARED LOCK", (that is, to commit the changes only and un-escalate the lock back to a shared lock) which would be required in order for the loop semantics posited by the OP to work correctly.

---
Theory is when you know everything but nothing works.  Practice is when everything works but no one knows why.  Sometimes theory and practice are combined:  nothing works and no one knows why.




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

Re: Client/Server Best Practices

Simon Slavin-3

On 3 Jan 2015, at 4:41am, Keith Medcalf <[hidden email]> wrote:

> I do not believe that there is a way to specify "COMMIT BUT MAINTAIN THE SHARED LOCK", (that is, to commit the changes only and un-escalate the lock back to a shared lock) which would be required in order for the loop semantics posited by the OP to work correctly.

If it were possible to "COMMIT BUT MAINTAIN THE SHARED LOCK" then a thread could hog the database, never letting other threads/processes do their work.  So I hope it's not possible to do it.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

James K. Lowden
In reply to this post by Keith Medcalf
On Fri, 02 Jan 2015 21:41:02 -0700
"Keith Medcalf" <[hidden email]> wrote:

> On Friday, 2 January, 2015 16:26, James K. Lowden
> <[hidden email]> said:
>
> >On Thu, 25 Dec 2014 05:32:45 -0700 (MST) Rick Kelly
> ><[hidden email]> wrote:
>
> >> All SELECT type requests are wrapped with BEGIN TRANSACTION/COMMIT
>
> >That shouldn't be necessary and afaik isn't necessary.  SELECT does
> >not modify the database.  To "commit a select" is to apply the
> >nonchanges.
>
> It does not matter whether it modifies the database.  "reading" the
> database requires a lock -- a shared lock.  "updating" the database
> requires a "write" lock, which precludes obtaining a "shared" lock.

When I first read your answer my reaction was, "yes, yes, of course".
But I'm not sure where that leaves the OP.  Are you suggesting select
statements work "better" in some sense with autocommit turned off?  

In passing I suggest *requires* is not true in general.  It may be that
SQLite and other implementations use locks to mediate access and
implement ACID semantics.  Locks are just one popular way to accomplish
that, not the only one.  

Your description of transaction implementation is illuminating.  If I
understand correctly, you're describing an odd set of
design choices.  

> BEGIN TRANSACTION does not acquire a lock -- BEGIN IMMEDIATE does
> that -- BEGIN TRANSACTION merely turns off autocommit, meaning that
> the lock will not be released magically, but rather by an explicit
> COMMIT (which itself does not do anything -- it merely turns
> autocommit back on so that the next statement will commit the
> transaction before magically acquiring a new lock).  

I find this very peculiar.  You aren't saying is that

        begin transaction;
        insert into T value (1);
        commit;
        [sqlite3_close]

leaves the database unchanged (because there's no "next statement")?
If not, and there's a power outage between (successful) commit and
closing the connection, what will be the state of the database on
restart?  Is the transaction still open or, if not, is it rolled
forward and completed, or rolled back?  

> However, execution of a SELECT statement does cause a lock to be
> obtained (a shared lock) and a COMMIT does cause that shared lock to
> be released.  

Again, I find this surprising.  I would expect SELECT to

1.  establish a shared lock
2.  select the data
3.  release the lock

whether or not BEGIN is called.  If I understand what commit does per
your description above, in a "transaction", the effect would be

1.  BEGIN TRANSACTION (autocommit off)
2.  SELECT (take shared lock)
3.  data data data
4.  [SELECT  done] (release shared lock)
5.  COMMIT (autocommit on)

which leaves steps #1 and #5 redundant.  

> Executing an UPDATE after a SELECT -- in the same connection -- (or
> while a select is in progress) will escalate the SHARED lock to a
> WRITE lock.  

OK, this is typical.

> COMMIT will release "the lock" -- "the lock" is now a WRITE lock, not
> a shared lock.  Therefore the next _step() will be executing without
> any lock at all leading to apparently undefined results (really an
> error should be thrown "SQLITE_DATABASE_UNLOCKED_FROM_UNDER_ME" or a
> MISUSE error, but is not).

Good to know.  I hope we agree this is unlovely, and intersects with
SQLite's unfortunate property of not isolating SELECT as atomic.  (By
which I mean: if there are 10 rows in a table and connection 1 issues
"SELECT * FROM T" and between calls 4 & 5 to sqlite3_step another
connection 2 issues "DELETE FROM T", the first process may get 4 or 5
rows, or 10, depending on luck of the draw.)  

The right behavior is not a misuse error.  How is it "misuse" for two
connections to read and update the same table in overlapping time?  

The right behavior is to isolate SELECT from UPDATE.  Let the reader see
what was in the database at the time the SELECT was issued, unaffected
by UPDATE.  The COMMIT associated with UPDATE should not affect the
SELECT's lock; rather it should pend until SELECT completes.  Then its
own lock acquires rights to the resources it needs, and is released when
the work is done.  

> This is inherent in how WAL works.  Just because WAL is not in effect
> does not alter the fundamental workings of the transaction system.

Not sure how to parse that.  I think you mean it's inherent in how
transactions work, whether or not WAL is used?  

> I do not believe that there is a way to specify "COMMIT BUT MAINTAIN
> THE SHARED LOCK", (that is, to commit the changes only and
> un-escalate the lock back to a shared lock)

No such syntax is needed if ACID semantics are respected. In SQL we
we neither lock nor release anything, ever, explicitly.  Locks are
implementation artifacts.  COMMIT simply means the data are safely
stored.  

As I said, your description (which I trust is accurate) is very helpful
to someone who wants to understand how SQLite will act on the SQL
provided to it.  But it also protrays problematic choices that stray
from SQL's defined behavior.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

Keith Medcalf
On Wednesday, 7 January, 2015 20:01, James K. Lowden <[hidden email]> said:

>On Fri, 02 Jan 2015 21:41:02 -0700
>"Keith Medcalf" <[hidden email]> wrote:
>> On Friday, 2 January, 2015 16:26, James K. Lowden
>> <[hidden email]> said:
>>
>> >On Thu, 25 Dec 2014 05:32:45 -0700 (MST) Rick Kelly
>> ><[hidden email]> wrote:
>>
>> >> All SELECT type requests are wrapped with BEGIN TRANSACTION/COMMIT
>>
>> >That shouldn't be necessary and afaik isn't necessary.  SELECT does
>> >not modify the database.  To "commit a select" is to apply the
>> >nonchanges.
>>
>> It does not matter whether it modifies the database.  "reading" the
>> database requires a lock -- a shared lock.  "updating" the database
>> requires a "write" lock, which precludes obtaining a "shared" lock.
>
>When I first read your answer my reaction was, "yes, yes, of course".
>But I'm not sure where that leaves the OP.  Are you suggesting select
>statements work "better" in some sense with autocommit turned off?

No.  However, when you perform an update in the same transaction (and connection) as you are performing the select, when the select is not completed yet, and you commit on the connection, it is to be expected that AHWBL.

>In passing I suggest *requires* is not true in general.  It may be that
>SQLite and other implementations use locks to mediate access and
>implement ACID semantics.  Locks are just one popular way to accomplish
>that, not the only one.

>Your description of transaction implementation is illuminating.  If I
>understand correctly, you're describing an odd set of
>design choices.
>
>> BEGIN TRANSACTION does not acquire a lock -- BEGIN IMMEDIATE does
>> that -- BEGIN TRANSACTION merely turns off autocommit, meaning that
>> the lock will not be released magically, but rather by an explicit
>> COMMIT (which itself does not do anything -- it merely turns
>> autocommit back on so that the next statement will commit the
>> transaction before magically acquiring a new lock).

>I find this very peculiar.  You aren't saying is that
>
> begin transaction;
> insert into T value (1);
> commit;
> [sqlite3_close]

>leaves the database unchanged (because there's no "next statement")?
>If not, and there's a power outage between (successful) commit and
>closing the connection, what will be the state of the database on
>restart?  Is the transaction still open or, if not, is it rolled
>forward and completed, or rolled back?

I am not certain exactly where the COMMIT performs the commit.  It could very likely commit and then turn autocommit back on.  Whatever so, it makes no difference really.

>> However, execution of a SELECT statement does cause a lock to be
>> obtained (a shared lock) and a COMMIT does cause that shared lock to
>> be released.

>Again, I find this surprising.  I would expect SELECT to
>
>1.  establish a shared lock
>2.  select the data
>3.  release the lock

Except that this is not what the OP is doing.  The OP is performing a COMMIT in the middle of the select running.  He is doing this:

1.  establish the lock
2.  select the first of one of many rows of data
3.  release the lock forcibly (COMMIT)
4.  read the next row of data from the select
5.  release the lock forcibly (COMMIT)
... lather rinse repeat steps 4 and 5 until you reach the end of the selected rows

>whether or not BEGIN is called.  If I understand what commit does per
>your description above, in a "transaction", the effect would be

>1.  BEGIN TRANSACTION (autocommit off)
>2.  SELECT (take shared lock)
>3.  data data data
>4.  [SELECT  done] (release shared lock)
>5.  COMMIT (autocommit on)
>
>which leaves steps #1 and #5 redundant.

>> Executing an UPDATE after a SELECT -- in the same connection -- (or
>> while a select is in progress) will escalate the SHARED lock to a
>> WRITE lock.

>OK, this is typical.

>> COMMIT will release "the lock" -- "the lock" is now a WRITE lock, not
>> a shared lock.  Therefore the next _step() will be executing without
>> any lock at all leading to apparently undefined results (really an
>> error should be thrown "SQLITE_DATABASE_UNLOCKED_FROM_UNDER_ME" or a
>> MISUSE error, but is not).

>Good to know.  I hope we agree this is unlovely, and intersects with
>SQLite's unfortunate property of not isolating SELECT as atomic.  (By
>which I mean: if there are 10 rows in a table and connection 1 issues
>"SELECT * FROM T" and between calls 4 & 5 to sqlite3_step another
>connection 2 issues "DELETE FROM T", the first process may get 4 or 5
>rows, or 10, depending on luck of the draw.)

No, it was the other persons choice to do this.  He could have run the select entirely, then done the updates.  Or he could have chosen to do the updates on another connection (which would be isolated from the connection running the select) using WAL.

>The right behavior is not a misuse error.  How is it "misuse" for two
>connections to read and update the same table in overlapping time?

It is not.  There IS NOT TWO CONNECTIONS.  There is only one connection.  The same connection is interspersing updates while running the select, and attempting to commit the single connection in the middle of the select, but still expecting that the select will "carry on".

>The right behavior is to isolate SELECT from UPDATE.  Let the reader see
>what was in the database at the time the SELECT was issued, unaffected
>by UPDATE.  The COMMIT associated with UPDATE should not affect the
>SELECT's lock; rather it should pend until SELECT completes.  Then its
>own lock acquires rights to the resources it needs, and is released when
>the work is done.

You are correct.  The way to do this is with journal_mode=WAL and two separate connections, one for the select, and one for the update.
In the posited code, the COMMIT is not associated with the UPDATE, it is associated with BOTH the SELECT and the UPDATE because they both occur on the same connection.

>> This is inherent in how WAL works.  Just because WAL is not in effect
>> does not alter the fundamental workings of the transaction system.

>Not sure how to parse that.  I think you mean it's inherent in how
>transactions work, whether or not WAL is used?

Without WAL, readers block writers and writers block readers.  With WAL, readers do not block writers -- they have ADDITIONAL processing that isolates their view of the database to a point in time before the writer obtained its lock.  The implementation does not vary -- the basic processing is the same -- WAL adds additional things, but does not change the fact that a SELECT requires a shared lock -- it merely adds some lookaside semantics.

>> I do not believe that there is a way to specify "COMMIT BUT MAINTAIN
>> THE SHARED LOCK", (that is, to commit the changes only and
>> un-escalate the lock back to a shared lock)

>No such syntax is needed if ACID semantics are respected. In SQL we
>we neither lock nor release anything, ever, explicitly.  Locks are
>implementation artifacts.  COMMIT simply means the data are safely
>stored.

And that the "view state of the database is released".  That is, in an SQLite database you can do:

BEGIN
SELECT ...
SELECT ...
SELECT ...
SELECT ...
COMMIT

and the view of the database seen by this connection will be consistent even though "some other process" modified the tables used in query 3 while query 2 was executing.  Even if those changes are commited by the writer process, the above process will not see them until the COMMIT releases the locks.  I expect other databases do this as well.  In fact I know that they do, because you can perform steps like the above to do master/detail processing and ensure consistent results, even though some other process makes changes (or even deletes the whole master/detail shebang while query 2 is executing.  It is only if you try to update what is now non-existant or changed rows from your view do you get an error that your data is stale.

>As I said, your description (which I trust is accurate) is very helpful
>to someone who wants to understand how SQLite will act on the SQL
>provided to it.  But it also protrays problematic choices that stray
>from SQL's defined behavior.

Not really.  It only shows that updating the database on the same connection as is being used to run a select, and attempting to commit the update before the select is complete results in chaos.  I think you would agree that the sequence:

_prepare('select ...')
do while _step() != DONE
   _step(_prepare('BEGIN'))
   _step(_prepare('UPDATE ...'))
   _step(_prepare('COMMIT'))
   continue

should not be valid.  The BEGIN COMMIT should be moved outside the loop unless the connection on which the select is processed is separate from that on which the BEGIN/UPDATE/COMMIT is performed.




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

Re: Client/Server Best Practices

Kevin Benson
On Wed, Jan 7, 2015 at 11:47 PM, Keith Medcalf <[hidden email]> wrote:

> On Wednesday, 7 January, 2015 20:01, James K. Lowden <
> [hidden email]> said:
> >On Fri, 02 Jan 2015 21:41:02 -0700
> >"Keith Medcalf" <[hidden email]> wrote:
> >> On Friday, 2 January, 2015 16:26, James K. Lowden
> >> <[hidden email]> said:
> >>
> >> >On Thu, 25 Dec 2014 05:32:45 -0700 (MST) Rick Kelly
> >> ><[hidden email]> wrote:
> >>
> >> >> All SELECT type requests are wrapped with BEGIN TRANSACTION/COMMIT
> >>
> >> >That shouldn't be necessary and afaik isn't necessary.  SELECT does
> >> >not modify the database.  To "commit a select" is to apply the
> >> >nonchanges.
> >>
> >> It does not matter whether it modifies the database.  "reading" the
> >> database requires a lock -- a shared lock.  "updating" the database
> >> requires a "write" lock, which precludes obtaining a "shared" lock.
> >
> >When I first read your answer my reaction was, "yes, yes, of course".
> >But I'm not sure where that leaves the OP.  Are you suggesting select
> >statements work "better" in some sense with autocommit turned off?
>
> No.  However, when you perform an update in the same transaction (and
> connection) as you are performing the select, when the select is not
> completed yet, and you commit on the connection, it is to be expected that
> AHWBL.
>
> >In passing I suggest *requires* is not true in general.  It may be that
> >SQLite and other implementations use locks to mediate access and
> >implement ACID semantics.  Locks are just one popular way to accomplish
> >that, not the only one.
>
> >Your description of transaction implementation is illuminating.  If I
> >understand correctly, you're describing an odd set of
> >design choices.
> >
> >> BEGIN TRANSACTION does not acquire a lock -- BEGIN IMMEDIATE does
> >> that -- BEGIN TRANSACTION merely turns off autocommit, meaning that
> >> the lock will not be released magically, but rather by an explicit
> >> COMMIT (which itself does not do anything -- it merely turns
> >> autocommit back on so that the next statement will commit the
> >> transaction before magically acquiring a new lock).
>
> >I find this very peculiar.  You aren't saying is that
> >
> >       begin transaction;
> >       insert into T value (1);
> >       commit;
> >       [sqlite3_close]
>
> >leaves the database unchanged (because there's no "next statement")?
> >If not, and there's a power outage between (successful) commit and
> >closing the connection, what will be the state of the database on
> >restart?  Is the transaction still open or, if not, is it rolled
> >forward and completed, or rolled back?
>
> I am not certain exactly where the COMMIT performs the commit.  It could
> very likely commit and then turn autocommit back on.  Whatever so, it makes
> no difference really.
>
> >> However, execution of a SELECT statement does cause a lock to be
> >> obtained (a shared lock) and a COMMIT does cause that shared lock to
> >> be released.
>
> >Again, I find this surprising.  I would expect SELECT to
> >
> >1.  establish a shared lock
> >2.  select the data
> >3.  release the lock
>
> Except that this is not what the OP is doing.  The OP is performing a
> COMMIT in the middle of the select running.  He is doing this:
>
> 1.  establish the lock
> 2.  select the first of one of many rows of data
> 3.  release the lock forcibly (COMMIT)
> 4.  read the next row of data from the select
> 5.  release the lock forcibly (COMMIT)
> ... lather rinse repeat steps 4 and 5 until you reach the end of the
> selected rows
>
> >whether or not BEGIN is called.  If I understand what commit does per
> >your description above, in a "transaction", the effect would be
>
> >1.  BEGIN TRANSACTION (autocommit off)
> >2.  SELECT (take shared lock)
> >3.  data data data
> >4.  [SELECT  done] (release shared lock)
> >5.  COMMIT (autocommit on)
> >
> >which leaves steps #1 and #5 redundant.
>
> >> Executing an UPDATE after a SELECT -- in the same connection -- (or
> >> while a select is in progress) will escalate the SHARED lock to a
> >> WRITE lock.
>
> >OK, this is typical.
>
> >> COMMIT will release "the lock" -- "the lock" is now a WRITE lock, not
> >> a shared lock.  Therefore the next _step() will be executing without
> >> any lock at all leading to apparently undefined results (really an
> >> error should be thrown "SQLITE_DATABASE_UNLOCKED_FROM_UNDER_ME" or a
> >> MISUSE error, but is not).
>
> >Good to know.  I hope we agree this is unlovely, and intersects with
> >SQLite's unfortunate property of not isolating SELECT as atomic.  (By
> >which I mean: if there are 10 rows in a table and connection 1 issues
> >"SELECT * FROM T" and between calls 4 & 5 to sqlite3_step another
> >connection 2 issues "DELETE FROM T", the first process may get 4 or 5
> >rows, or 10, depending on luck of the draw.)
>
> No, it was the other persons choice to do this.  He could have run the
> select entirely, then done the updates.  Or he could have chosen to do the
> updates on another connection (which would be isolated from the connection
> running the select) using WAL.
>
> >The right behavior is not a misuse error.  How is it "misuse" for two
> >connections to read and update the same table in overlapping time?
>
> It is not.  There IS NOT TWO CONNECTIONS.  There is only one connection.
> The same connection is interspersing updates while running the select, and
> attempting to commit the single connection in the middle of the select, but
> still expecting that the select will "carry on".
>

-- The OP wrote:

On Thu, Dec 25, 2014 at 7:32 AM, Rick Kelly <[hidden email]> wrote:
>*SNIP*
> The database file is located in the same folder as the server. The server
is
> multi-threaded. I'm using library version 3.8.7.4
> The database file is opened as follows:
> 1. sqlite3_enable_shared_cache(%SQLITE_OPEN_SHAREDCACHE)

-- *Presumably* enabling SQLite Shared-Cache Mode likely signals his
intention to HAVE two or more connections, right? If so, with that comes
the Shared-Cache Locking Model at https://www.sqlite.org/sharedcache.html

--
   --
      --
         --Ô¿Ô--
        K e V i N



>
> >The right behavior is to isolate SELECT from UPDATE.  Let the reader see
> >what was in the database at the time the SELECT was issued, unaffected
> >by UPDATE.  The COMMIT associated with UPDATE should not affect the
> >SELECT's lock; rather it should pend until SELECT completes.  Then its
> >own lock acquires rights to the resources it needs, and is released when
> >the work is done.
>
> You are correct.  The way to do this is with journal_mode=WAL and two
> separate connections, one for the select, and one for the update.
> In the posited code, the COMMIT is not associated with the UPDATE, it is
> associated with BOTH the SELECT and the UPDATE because they both occur on
> the same connection.
>
> >> This is inherent in how WAL works.  Just because WAL is not in effect
> >> does not alter the fundamental workings of the transaction system.
>
> >Not sure how to parse that.  I think you mean it's inherent in how
> >transactions work, whether or not WAL is used?
>
> Without WAL, readers block writers and writers block readers.  With WAL,
> readers do not block writers -- they have ADDITIONAL processing that
> isolates their view of the database to a point in time before the writer
> obtained its lock.  The implementation does not vary -- the basic
> processing is the same -- WAL adds additional things, but does not change
> the fact that a SELECT requires a shared lock -- it merely adds some
> lookaside semantics.
>
> >> I do not believe that there is a way to specify "COMMIT BUT MAINTAIN
> >> THE SHARED LOCK", (that is, to commit the changes only and
> >> un-escalate the lock back to a shared lock)
>
> >No such syntax is needed if ACID semantics are respected. In SQL we
> >we neither lock nor release anything, ever, explicitly.  Locks are
> >implementation artifacts.  COMMIT simply means the data are safely
> >stored.
>
> And that the "view state of the database is released".  That is, in an
> SQLite database you can do:
>
> BEGIN
> SELECT ...
> SELECT ...
> SELECT ...
> SELECT ...
> COMMIT
>
> and the view of the database seen by this connection will be consistent
> even though "some other process" modified the tables used in query 3 while
> query 2 was executing.  Even if those changes are commited by the writer
> process, the above process will not see them until the COMMIT releases the
> locks.  I expect other databases do this as well.  In fact I know that they
> do, because you can perform steps like the above to do master/detail
> processing and ensure consistent results, even though some other process
> makes changes (or even deletes the whole master/detail shebang while query
> 2 is executing.  It is only if you try to update what is now non-existant
> or changed rows from your view do you get an error that your data is stale.
>
> >As I said, your description (which I trust is accurate) is very helpful
> >to someone who wants to understand how SQLite will act on the SQL
> >provided to it.  But it also protrays problematic choices that stray
> >from SQL's defined behavior.
>
> Not really.  It only shows that updating the database on the same
> connection as is being used to run a select, and attempting to commit the
> update before the select is complete results in chaos.  I think you would
> agree that the sequence:
>
> _prepare('select ...')
> do while _step() != DONE
>    _step(_prepare('BEGIN'))
>    _step(_prepare('UPDATE ...'))
>    _step(_prepare('COMMIT'))
>    continue
>
> should not be valid.  The BEGIN COMMIT should be moved outside the loop
> unless the connection on which the select is processed is separate from
> that on which the BEGIN/UPDATE/COMMIT is performed.
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

Keith Medcalf
On Wednesday, 7 January, 2015 22:57, Kevin Benson <[hidden email]> said:

>-- The OP wrote:

>On Thu, Dec 25, 2014 at 7:32 AM, Rick Kelly <[hidden email]> wrote:
>>*SNIP*
>> The database file is located in the same folder as the server. The
>server
>is
>> multi-threaded. I'm using library version 3.8.7.4
>> The database file is opened as follows:
>> 1. sqlite3_enable_shared_cache(%SQLITE_OPEN_SHAREDCACHE)
>
>-- *Presumably* enabling SQLite Shared-Cache Mode likely signals his
>intention to HAVE two or more connections, right? If so, with that comes
>the Shared-Cache Locking Model at https://www.sqlite.org/sharedcache.html

You are right Kevin, and I am guilty of mixing two questions -- this one about implementing your own Client/Server protocol, and one regarding interspersing an UPDATE/COMMIT inside a SELECT on the same connection.

In reply to the original post, there is only one connection from the shared cache to the database, and it is this connection where WAL applies.  Per table locking and updating with the shared cache is an effect of the shared cache, not WAL.  You current configuration seems to be working for you.

Unless you have changed the default, all usage of connections from the application to shared cache and shared cache to database are serialized automatically to ensure that concurrency issues do not arise.




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

Re: Client/Server Best Practices

Rick Kelly
Thanks for all the sage observations. As a server with a connection pool, I'm avoiding some of issues brought up. Every thread has their own connection handle and when done, it goes back into the pool so no sharing. I spent some hours reading everything I could find about this product and concurrency issues and my goal is ultimately to avoid busy's and lockout's. This library is quite fast and performance isn't going to be an issue. After I put the backup api in the server code base, I'll hit it with several thousand threads, mixed reads and writes, as fast as I can create them both with the backup api active and without and see what the session log records.

Rick Kelly
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

James K. Lowden
In reply to this post by Keith Medcalf
On Wed, 07 Jan 2015 21:47:24 -0700
"Keith Medcalf" <[hidden email]> wrote:

> >As I said, your description (which I trust is accurate) is very
> >helpful to someone who wants to understand how SQLite will act on
> >the SQL provided to it.  But it also protrays problematic choices
> >that stray from SQL's defined behavior.
>
> Not really.  It only shows that updating the database on the same
> connection as is being used to run a select, and attempting to commit
> the update before the select is complete results in chaos.  I think
> you would agree that the sequence:
>
> _prepare('select ...')
> do while _step() != DONE
>    _step(_prepare('BEGIN'))
>    _step(_prepare('UPDATE ...'))
>    _step(_prepare('COMMIT'))
>    continue
>
> should not be valid.  The BEGIN COMMIT should be moved outside the
> loop unless the connection on which the select is processed is
> separate from that on which the BEGIN/UPDATE/COMMIT is performed.

Yes, I agree that shouldn't be valid.  Or, if it is, then it should
work correctly!  ;-)  

Thanks for the clarification.  The salient point, as you emphasized, is

> There is only one connection.  

I tend to forget that SQLite behaves oddly sometimes when misused.  We
had a discussion months ago about approximately the same thing, where
threads were sharing a connection.  

Sybase works similarly.  The server insists that each query be handled
in its entirety before it accepts the next.  Attempting to issue an
UPDATE per your example above would result in the dread "pending
results" error.  

Along the same lines, since you mentioned it,

>  in an SQLite database you can do:
>
> BEGIN
> SELECT ...
> SELECT ...
> SELECT ...
> SELECT ...
> COMMIT
>
> and the view of the database seen by this connection will be
> consistent even though "some other process" modified the tables used
> in query 3 while query 2 was executing.  Even if those changes are
> commited by the writer process, the above process will not see them
> until the COMMIT releases the locks.  I expect other databases do
> this as well.  

In other DBMSs that I know of -- and afaik standard SQL -- BEGIN
TRANSACTION has no effect on SELECT. Each SELECT is atomic, of course,
but there's no way to hook two SELECTs together to make them see one
database state. That's what JOIN is for.  :-)  

A common error among inexperienced programmers is to assume that BEGIN
TRANSACTION works as you describe above: that it isolates SELECT from
competing updates.  But a transaction is not a lock!  A better mental
model is the one defined in the standard: BEGIN TRANSACTION defines a
unit of work that either will or will not be applied to the database in
its entirety (depending on COMMIT or ROLLBACK, of course).  It has a
role to play in maintaining database integrity.  It really has nothing
to do with isolation.  

Interesting discussion, Keith.  Thanks for the clarification.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Client/Server Best Practices

Keith Medcalf

On Friday, 9 January, 2015 16:43, James K. Lowden <[hidden email]> said:
>On Wed, 07 Jan 2015 21:47:24 -0700 "Keith Medcalf" <[hidden email]> wrote:

>Along the same lines, since you mentioned it,

>>  in an SQLite database you can do:

>> BEGIN
>> SELECT ...
>> SELECT ...
>> SELECT ...
>> SELECT ...
>> COMMIT

>> and the view of the database seen by this connection will be
>> consistent even though "some other process" modified the tables used
>> in query 3 while query 2 was executing.  Even if those changes are
>> commited by the writer process, the above process will not see them
>> until the COMMIT releases the locks.  I expect other databases do
>> this as well.

>In other DBMSs that I know of -- and afaik standard SQL -- BEGIN
>TRANSACTION has no effect on SELECT. Each SELECT is atomic, of course,
>but there's no way to hook two SELECTs together to make them see one
>database state. That's what JOIN is for.  :-)

Yes, it is a part of the SQL Standard isolation levels in excess of the default default of READ COMMITTED.  Different vendors call the isolation levels by different names -- MS SQL Server calls them Read Uncomitted, Read Committed, Repeatable Read, Snapshot, and Serializable; DB/2 calls them No Commit (similar to autocommit), Uncommitted Read, Cursor Stability, Read Stability, Repeatable Read.  DB/2 Repeatable Read = MS Serializable (which is the SQL Standard Serializable isolation level).  Cursor Stability = Read Committed.

These describe the isolation to be maintained be simultaneous Units of Work.  The standard Read Committed/Cursor Stability level does not isolate the unit of work from changes made (and committed) by other units of work.  The higher levels do.  For example, if a unit of work is executed with Read Committed the two select statements may see changes to the database which were committed on another unit of work.  With Serializable / Repeatable Read, those changes will not be visible.  In fact, the database will not permit the updates to rows that have been read at the Serializable / Repeatable Read level.

http://publib.boulder.ibm.com/iseries/v5r1/ic2924/info/db2/rbafzmstisol.htm
http://msdn.microsoft.com/en-us/library/ms173763.aspx

>A common error among inexperienced programmers is to assume that BEGIN
>TRANSACTION works as you describe above: that it isolates SELECT from
>competing updates.  But a transaction is not a lock!  A better mental
>model is the one defined in the standard: BEGIN TRANSACTION defines a
>unit of work that either will or will not be applied to the database in
>its entirety (depending on COMMIT or ROLLBACK, of course).  It has a
>role to play in maintaining database integrity.  It really has nothing
>to do with isolation.

Indeed it does.  It is up to the programmer to set the isolation levels required and then set the transaction (unit of work) boundaries correctly.  Most only know or use the default Read Committed / Cursor Stability level which is usually the default mode, which works as you describe.

SQLite in standard journal mode uses "read committed/cursor stability" isolation between connections to the same database.  I have double checked, and if you surround multiple selects with explicit begin/end, then that unit of work cannot be interrupted by updates and hence you then have "Repeatable Read" isolation.  Whether or not this is intended it must work this way because the introduction of a unit of work means that there is intent to update at some point and the database, within the unit of work, must maintain cursor stability (at least) until an actual update occurs which will escalate the shared lock to an exclusive lock.  In fact, if you do this, you will be unable to commit the update transaction until the other unit of work is completed (even if it only contains select statements).

In WAL mode the transaction mode is always snapshot between units of work on separate connections, but only one connections' unit of work can convert to "update" at a time.  Other connections doing just selects between a BEGIN COMMIT will continue to see the same snapshot even if another connection commits database changes.  It appears that the concurrent snapshot can also escalate to an update lock is released by another and sometimes it cannot -- if it is the head of the uncommitted WAL, it can, if it is not, it cannot.

>Interesting discussion, Keith.  Thanks for the clarification.

---
Theory is when you know everything but nothing works.  Practice is when everything works but no one knows why.  Sometimes theory and practice are combined:  nothing works and no one knows why.




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

Re: Client/Server Best Practices

James K. Lowden
On Sat, 10 Jan 2015 00:58:25 -0700
"Keith Medcalf" <[hidden email]> wrote:

> > there's no way to hook two SELECTs together to make them see one
> >database state. That's what JOIN is for.  :-)
>
> Yes, it is a part of the SQL Standard isolation levels in excess of
> the default default of READ COMMITTED.  Different vendors call the
> isolation levels by different names -- MS SQL Server calls them Read
> Uncomitted, Read Committed, Repeatable Read, Snapshot, and
> Serializable; DB/2 calls them No Commit (similar to autocommit),
> Uncommitted Read, Cursor Stability, Read Stability, Repeatable Read.
> DB/2 Repeatable Read = MS Serializable (which is the SQL Standard
> Serializable isolation level).  Cursor Stability = Read Committed.

Keith, I just want to thank you for relieving me of my
misunderstanding.  I think some years ago I must have decided that for
my purposes Repeatable Read & friends don't exist because of their
effect on performance and concurrency.  I've always thought the
standard was paradoxical in the sense that the more you need isolation
(because of concurrent updates), the less useful it is to the extent
that restricts the system's abiltity to support concurrent updates.  

To bring it back to SQLite, we have two logging modes and two
isolation levels.  IIUC, given two connections it can be boiled down
thusly:

mode transaction isolation
Journal implicit Read Committed
WAL   implicit Read Committed
journal explicit Repeatable Read
WAL   explicit Repeatable Read

with the difference being concurrency:

* in journal mode a writer is blocked by a reader, whereas
* in WAL mode a writer can commit while a reader continues to work,
in isolation, unaffected.  

--jkl

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

Re: Client/Server Best Practices

Richard Hipp-3
On 1/16/15, James K. Lowden <[hidden email]> wrote:

> On Sat, 10 Jan 2015 00:58:25 -0700
> "Keith Medcalf" <[hidden email]> wrote:
>
>> > there's no way to hook two SELECTs together to make them see one
>> >database state. That's what JOIN is for.  :-)
>>
>> Yes, it is a part of the SQL Standard isolation levels in excess of
>> the default default of READ COMMITTED.  Different vendors call the
>> isolation levels by different names -- MS SQL Server calls them Read
>> Uncomitted, Read Committed, Repeatable Read, Snapshot, and
>> Serializable; DB/2 calls them No Commit (similar to autocommit),
>> Uncommitted Read, Cursor Stability, Read Stability, Repeatable Read.
>> DB/2 Repeatable Read = MS Serializable (which is the SQL Standard
>> Serializable isolation level).  Cursor Stability = Read Committed.
>
> Keith, I just want to thank you for relieving me of my
> misunderstanding.  I think some years ago I must have decided that for
> my purposes Repeatable Read & friends don't exist because of their
> effect on performance and concurrency.  I've always thought the
> standard was paradoxical in the sense that the more you need isolation
> (because of concurrent updates), the less useful it is to the extent
> that restricts the system's abiltity to support concurrent updates.
>
> To bring it back to SQLite, we have two logging modes and two
> isolation levels.  IIUC, given two connections it can be boiled down
> thusly:
>
> mode transaction isolation
> Journal implicit Read Committed
> WAL   implicit Read Committed
> journal explicit Repeatable Read
> WAL   explicit Repeatable Read

I think the isolation in SQLite is always Serializable.

Except if you have two connections in the same process in shared cache
mode and they set "PRAGMA read_uncommitted=ON" then the isolation is
Read Uncommitted.  But otherwise, isolation in SQLite is always
Serializable.


>
> with the difference being concurrency:
>
> * in journal mode a writer is blocked by a reader, whereas
> * in WAL mode a writer can commit while a reader continues to work,
> in isolation, unaffected.
>
> --jkl
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users