Re: [sqlite] async io and locks

[Jim Wilcoxson]

Async I/O fits perfectly with my app: I don't need the durable
guarantee.  But I do need the ability to activate it with a pragma
since it isn't a C app.

Are there plans to make async I/O available via pragma?

Jim

On 6/20/09, Dan  wrote:
>
> I think we have quite different approaches.
>
> The SQLite asynchronous IO backend is queueing IO, not queries. It
> intercepts
> the equivalent of the write() system call. It does not overload query
> processing
> in any way. The things on the write-queue are blobs of data to be
> written
> to specific offsets within the database file, not SQL queries.
>
> It's more complex than that of course. But the point is that the write-
> queue is
> a layer between SQLite and the OS, not between the user and SQLite.
>
> Dan.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] async io and locks

[Dan]

On Jun 20, 2009, at 6:22 AM, Robert Lehr wrote:

> Once again, I am forced to reply to the top-level, outside the thread
> b/c I am not receiving individual replies.  I apologize for the  
> inconvenience.
>
> On Jun 20, 2009, at 12:30 AM, Dan wrote:
>> On Jun 20, 2009, at 12:06 AM, Robert Lehr wrote:
>>> I finally got around to reviewing SQLite's asynchronous I/O
>>> functionality.
>>>
>>>   http://sqlite.org/asyncvfs.html 
>>>
>>> We actually have an C++ wrapper to uses the same concept, a  
>>> background
>>> thread for I/O.
>>
>> You mean you have implemented the callback methods in struct  
>> sqlite3_vfs to read
>> and write asynchronously just as we have? Or some other technique?
>>
>
> Another technique - the C++ __wrapper__, akin to the numerous other C 
> ++ wrappers
> that are being hacked together around the world.  We required  
> asynchrony.  We
> therefore wrote our own wrapper that implements it.
>
>>>
>>> W/rt to the locking policy w/ multiple updates, are there design
>>> reasons for not releasing and re-acquiring a lock between
>>> transactions?  That would facilitate higher concurrency albeit it a
>>> slightly higher cost than the current implementation.
>>> That cost should match the current cost of multiple transactions,
>>> though.
>>
>> It's to handle this:
>>
>>   BEGIN;
>> UPDATE t1 SET  WHERE ;
>>   COMMIT;
>>   BEGIN
>> UPDATE t2 SET  WHERE ;
>>   COMMIT;
>>
>> If the SQLite user starts the second transaction before the  
>> asynchronous thread
>> has had time to commit the first, it will read the database to  
>> figure out the
>> set of rows to apply the  modifications to.  Once that  
>> has happened,
>> the database file cannot be unlocked before the second transaction is
>> committed. Otherwise, some other client might sneak in while the  
>> database was
>> unlocked and modify table t2, changing the set of rows  
>> 
>> selects.
>>
>> Of course, if the asynchronous thread manages to commit the first  
>> transaction to
>> disk before the user has time to execute the second, the database  
>> file will be
>> unlocked between transactions.
>
> Yes, except that I verified that this interpretation is  
> incorrect according
> to the docs (as I interpret them) before I sent my original query.   
> The relevant
> part of the docs follows.
>
>"If an application using asynchronous IO executes transactions
>in quick succession, other database users may be effectively
>locked out of the database. This is because when a BEGIN is
>executed, a database lock is established immediately."
>
> Particularly note that the following denotes:
>
>   * that the lock is acquired independent of any transaction.
>   * that the lock on the database file persists beyond the duration of
> all transactions.
>
>"But when the corresponding COMMIT or ROLLBACK occurs, the
>lock is not released until the relevant part of the
>write-queue has been flushed through. As a result, if a COMMIT
>is followed by a BEGIN before the write-queue is flushed
>through, the database is never unlocked,preventing other
>processes from accessing the database."
>
> Thus the async functionality does NOT simply wrap each update query  
> (UPDATE,
> DELETE) in a transaction.
>
> If it did then I would be sending a completely different query, one  
> pertaining
> to broken semantics (for our purposes) of automatically wrapping my  
> queries in
> nested transactions which would, again, prevent me from adopting  
> SQLite's
> baseline async functionality.
>
> BTW, it occurred to me as I wrote this reply that the fact the SQLite
> distinguishes FETCHES from CREATES, UPDATES and DELETES indicates  
> that it is
> inspecting the queries themselves before deciding to append them to a
> write-queue or not.  It therefore could detect beginnings and ends of
> transactions.  Thus SQLite could enqueue transactions as it  
> currently enqueues
> write queries.


I think we have quite different approaches.

The SQLite asynchronous IO backend is queueing IO, not queries. It  
intercepts
the equivalent of the write() system call. It does not overload query  
processing
in any way. The things on the write-queue are blobs of data to be  
written
to specific offsets within the database file, not SQL queries.

It's more complex than that of course. But the point is that the write- 
queue is
a layer between SQLite and the OS, not between the user and SQLite.

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] async io and locks (CORRECTION)

[Robert Lehr]

(A) I was able to reply inside the thread.  *confused*
 
(B) "that the lock is acquired independent..." is incorrect and irrelevant.
It should be ignored.

-robert

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] async io and locks

[Robert Lehr]

Once again, I am forced to reply to the top-level, outside the thread
b/c I am not receiving individual replies.  I apologize for the inconvenience.

On Jun 20, 2009, at 12:30 AM, Dan wrote:
> On Jun 20, 2009, at 12:06 AM, Robert Lehr wrote:
> > I finally got around to reviewing SQLite's asynchronous I/O
> > functionality.
> >
> >http://sqlite.org/asyncvfs.html 
> >
> > We actually have an C++ wrapper to uses the same concept, a background
> > thread for I/O.
>
> You mean you have implemented the callback methods in struct sqlite3_vfs to 
> read
> and write asynchronously just as we have? Or some other technique?
>

Another technique - the C++ __wrapper__, akin to the numerous other C++ wrappers
that are being hacked together around the world.  We required asynchrony.  We
therefore wrote our own wrapper that implements it.

> >
> > W/rt to the locking policy w/ multiple updates, are there design
> > reasons for not releasing and re-acquiring a lock between
> > transactions?  That would facilitate higher concurrency albeit it a
> > slightly higher cost than the current implementation.
> > That cost should match the current cost of multiple transactions,
> > though.
>
> It's to handle this:
>
>BEGIN;
>  UPDATE t1 SET  WHERE ;
>COMMIT;
>BEGIN
>  UPDATE t2 SET  WHERE ;
>COMMIT;
>
> If the SQLite user starts the second transaction before the asynchronous 
> thread
> has had time to commit the first, it will read the database to figure out the
> set of rows to apply the  modifications to.  Once that has 
> happened,
> the database file cannot be unlocked before the second transaction is
> committed. Otherwise, some other client might sneak in while the database was
> unlocked and modify table t2, changing the set of rows 
> selects.
>
> Of course, if the asynchronous thread manages to commit the first transaction 
> to
> disk before the user has time to execute the second, the database file will be
> unlocked between transactions.

Yes, except that I verified that this interpretation is incorrect according
to the docs (as I interpret them) before I sent my original query.  The relevant
part of the docs follows.

"If an application using asynchronous IO executes transactions
in quick succession, other database users may be effectively
locked out of the database. This is because when a BEGIN is
executed, a database lock is established immediately."

Particularly note that the following denotes:

* that the lock is acquired independent of any transaction.
* that the lock on the database file persists beyond the duration of
  all transactions.

"But when the corresponding COMMIT or ROLLBACK occurs, the
lock is not released until the relevant part of the
write-queue has been flushed through. As a result, if a COMMIT
is followed by a BEGIN before the write-queue is flushed
through, the database is never unlocked,preventing other
processes from accessing the database."

Thus the async functionality does NOT simply wrap each update query (UPDATE,
DELETE) in a transaction.

If it did then I would be sending a completely different query, one pertaining
to broken semantics (for our purposes) of automatically wrapping my queries in
nested transactions which would, again, prevent me from adopting SQLite's
baseline async functionality.

BTW, it occurred to me as I wrote this reply that the fact the SQLite
distinguishes FETCHES from CREATES, UPDATES and DELETES indicates that it is
inspecting the queries themselves before deciding to append them to a
write-queue or not.  It therefore could detect beginnings and ends of
transactions.  Thus SQLite could enqueue transactions as it currently enqueues
write queries.

-robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] async io and locks

[Dan]

On Jun 20, 2009, at 12:06 AM, Robert Lehr wrote:

> I finally got around to reviewing SQLite's asynchronous I/O  
> functionality.
>
>http://sqlite.org/asyncvfs.html 
>
> We actually have an C++ wrapper to uses the same concept, a  
> background thread for I/O.

You mean you have implemented the callback methods in struct sqlite3_vfs
to read and write asynchronously just as we have? Or some other  
technique?

> The async functionality included w/ SQLite is not a complete  
> replacement for ours, though, although it would be convenient to do  
> so.  However, we do things differently such that we
> cannot take advantage of SQLite's async functionality.
>
>   * We queue transactions, not individual queries.  Note that,  
> because queue
> transactions_ instead of queries, we lock the database per  
> transaction,
> thus avoiding the reduction in concurrency that is described on  
> the async
> I/O page.
>
>   * Our bg thread implementation retains durability by permitting  
> registration
> of callbacks for queries' succeul completion and aborts.
>
> W/rt to the locking policy w/ multiple updates, are there design  
> reasons for
> not releasing and re-acquiring a lock between transactions?  That  
> would facilitate
> higher concurrency albeit it a slightly higher cost than the current  
> implementation.
> That cost should match the current cost of multiple transactions,  
> though.

It's to handle this:

   BEGIN;
 UPDATE t1 SET  WHERE ;
   COMMIT;
   BEGIN
 UPDATE t2 SET  WHERE ;
   COMMIT;

If the SQLite user starts the second transaction before the asynchronous
thread has had time to commit the first, it will read the database to
figure out the set of rows to apply the  modifications to.  
Once
that has happened, the database file cannot be unlocked before the  
second
transaction is committed. Otherwise, some other client might sneak in  
while
the database was unlocked and modify table t2, changing the set of rows
 selects.

Of course, if the asynchronous thread manages to commit the first  
transaction
to disk before the user has time to execute the second, the database  
file
will be unlocked between transactions.

Dan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] async io and locks

[Robert Lehr]

I finally got around to reviewing SQLite's asynchronous I/O functionality.
 
http://sqlite.org/asyncvfs.html  
 
We actually have an C++ wrapper to uses the same concept, a background thread 
for I/O.
 
The async functionality included w/ SQLite is not a complete replacement for 
ours, though, although it would be convenient to do so.  However, we do things 
differently such that we
cannot take advantage of SQLite's async functionality.

* We queue transactions, not individual queries.  Note that, because 
queue
  transactions_ instead of queries, we lock the database per 
transaction,
  thus avoiding the reduction in concurrency that is described on the 
async
  I/O page.  

* Our bg thread implementation retains durability by permitting 
registration
  of callbacks for queries' succeul completion and aborts.

W/rt to the locking policy w/ multiple updates, are there design reasons for
not releasing and re-acquiring a lock between transactions?  That would 
facilitate
higher concurrency albeit it a slightly higher cost than the current 
implementation.
That cost should match the current cost of multiple transactions, though.

Many thanks.

-robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users