Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

Ok. Thank you for your explanation everyone and for being patient with me.
I look forward to Sqlite4 in whatever features you implement :)


On Mon, Nov 11, 2013 at 8:17 PM, Richard Hipp  wrote:

> On Mon, Nov 11, 2013 at 1:40 AM, Raheel Gupta  wrote:
>
> >
> > I guess a Row level locking could be difficult but a Page Level locking
> > could be not that difficult.
> >
>
> In an anomaly-free system, page level locking is not difficult.  The
> difficulty comes when you have to recover from an application crash (due to
> a bug or a "kill -9" or a power loss or some other cause) that occurs in
> the middle of updating the page.
>
> In a client/server database engine, you have a central server that can keep
> track of page (or row) locks efficiently.  Doing the same in a serverless
> system like SQLite is rather more difficult and (as far as I can determine)
> requires debilitating run-time overhead.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Richard Hipp]

On Mon, Nov 11, 2013 at 1:40 AM, Raheel Gupta  wrote:

>
> I guess a Row level locking could be difficult but a Page Level locking
> could be not that difficult.
>

In an anomaly-free system, page level locking is not difficult.  The
difficulty comes when you have to recover from an application crash (due to
a bug or a "kill -9" or a power loss or some other cause) that occurs in
the middle of updating the page.

In a client/server database engine, you have a central server that can keep
track of page (or row) locks efficiently.  Doing the same in a serverless
system like SQLite is rather more difficult and (as far as I can determine)
requires debilitating run-time overhead.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[RSmith]

On 2013/11/11 08:40, Raheel Gupta wrote:

I guess a Row level locking could be difficult but a Page Level locking
could be not that difficult.

ATM "db level locking" :
If DB locked throw busy error
In not locked lock db, let the writer do its thing

For Page level locking (I think you could allow something like) :
Let writer write / modify pages IF not locked
ONLY If writer comes across a locked page wait for it to be released

In this way, multiple threads could do writes. Again I am not an expert but
from my little understanding this might not remove the leaness. You are the
expert.

And even I agree that Sqlite must be "lite"


It's rare to see a drum banged this hard, so let me commend you for persistance and explain a bit more what Simon and others were 
trying to enlighten you about.


SQLite does not have row-level locking because when it was conceived at the onset, and then again every time it was updated to a 
newer major version (We are at 3 now), the people who designed it sat around their desks and brainstormed about which features can 
be included, which cannot and why. Whether it be space costs or costs paid to the processing cycles or for whatever reason, they 
have decided every time that row-level locking comes at too high a price for this kind of database - much in the same way the 
F1-McLaren racing team does not add airconditioning or CD-players to their cars, even though some F1 drivers would no-doubt enjoy a 
bit of luxury.  This is not dissimilar to MySQL ISAM tables which, for much the same reasons do not offer row-level locking. Anyone 
who uses MySQL seriously would have their large Databases contain a mix of ISAM and Inno Tables - whichever tool is best for the job.


SQLite is a tool, one of the many available, one of the best, but it has a niche and a specific job. If you truly cannot live 
without row-level locking, you could well use MySQL or PostGRES etc - these are equally free. But if you need speed, local-file 
access and embeddibility (if that's a word) then you need SQLite.


I know it is easy to sit on the sideline and imagine that row-locking would be easy, or as your newest post suggests, page-level 
locking - but you have an entire working and tested structure designed in ways that specifically is NOT row/page-locking, to even 
simply change that to be able to do so, then test it and risk all the backward compatibility issues that surround it, and then 
explain to everyone how it all works a bit slower now, but at least you can lock pages... just won't work. It's like trying to 
convince the mentioned F1 team that they should really run Diesel-Engines because it is more efficient (which isn't a lie), or 
simply add 36-inch wheels to their cars, like buses have, because it lasts longer (again, not a lie).  I am using wayward examples 
to try and make it clear how your suggestions look to the engineers making the engine.


Tell you what though, if you can download a copy of the code, and implement working and stable row or page-level locking with minor 
increases in size and negligible decreases in performance, I am willing to bet they will consider implementing it (at least in 
SQLIte4) and probably make you famous for it - but I must confess, there is not a lot of anticipation.


Please know I'm not scolding in any way (if it might seem like it), only telling you this because we all fall into the trap of 
thinking things can work better easily - me too.

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

Re: [sqlite] Row Level Locking as in InnoDB

[Sylvain Pointeau]

Hello,

If I would have one wish, it would not be the row level locking but the
merge syntax, so usefulf to update, insert or update in 1 command (no
insert or replace is not an equivalent), and in general it would be good to
implement the sql 2003.

Just a wish.

Best regards,
Sylvain

Le lundi 11 novembre 2013, Raheel Gupta a écrit :

> @simon
>
> I guess a Row level locking could be difficult but a Page Level locking
> could be not that difficult.
>
> ATM "db level locking" :
> If DB locked throw busy error
> In not locked lock db, let the writer do its thing
>
> For Page level locking (I think you could allow something like) :
> Let writer write / modify pages IF not locked
> ONLY If writer comes across a locked page wait for it to be released
>
> In this way, multiple threads could do writes. Again I am not an expert but
> from my little understanding this might not remove the leaness. You are the
> expert.
>
> And even I agree that Sqlite must be "lite"
>
>
>
> On Sun, Nov 10, 2013 at 8:39 PM, Simon Slavin 
> >
> wrote:
>
> >
> > On 10 Nov 2013, at 12:05pm, Raheel Gupta >
> wrote:
> >
> > >>> I can't think of any other single feature that would remove the
> "lite"
> > >
> > > I am not a database expert. If you say so, it must be the case.
> > > But if there is a way to implement concurrent writers in SQLite
> > maintaining
> > > the "lite" in SQLite, I would be the most happiest person here :)
> >
> > The main reason you seem to prefer SQLite to other databases is that it's
> > faster.  Adding row-level locking to SQLite would slow it down a lot.
>  As a
> > very simplified explanation, for one SELECT instead of
> >
> > try to lock the database
> > check to see that the lock on the database is yours
> > FOR EACH ROW:
> > figure out where the row's data is
> > read the data
> > unlock the database
> >
> > you have to do
> >
> > FOR EACH ROW:
> > figure out where the row's data is
> > try to lock the row
> > check to see that the lock on the row is yours
> > read the data
> > release the row
> >
> > If your SELECT returns 10 rows you end up doing 50 operations instead of
> > 23.  Which would mean that SQLite was half the speed, and no longer had
> any
> > advantages for you, so you would use something else.
> >
> > Locking is the single hardest thing to get right when writing a DBMS.
> >  SQLite gets a lot of its tininess and speed by implementing the simplest
> > fastest method of locking possible.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org 
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

@simon

I guess a Row level locking could be difficult but a Page Level locking
could be not that difficult.

ATM "db level locking" :
If DB locked throw busy error
In not locked lock db, let the writer do its thing

For Page level locking (I think you could allow something like) :
Let writer write / modify pages IF not locked
ONLY If writer comes across a locked page wait for it to be released

In this way, multiple threads could do writes. Again I am not an expert but
from my little understanding this might not remove the leaness. You are the
expert.

And even I agree that Sqlite must be "lite"



On Sun, Nov 10, 2013 at 8:39 PM, Simon Slavin  wrote:

>
> On 10 Nov 2013, at 12:05pm, Raheel Gupta  wrote:
>
> >>> I can't think of any other single feature that would remove the "lite"
> >
> > I am not a database expert. If you say so, it must be the case.
> > But if there is a way to implement concurrent writers in SQLite
> maintaining
> > the "lite" in SQLite, I would be the most happiest person here :)
>
> The main reason you seem to prefer SQLite to other databases is that it's
> faster.  Adding row-level locking to SQLite would slow it down a lot.  As a
> very simplified explanation, for one SELECT instead of
>
> try to lock the database
> check to see that the lock on the database is yours
> FOR EACH ROW:
> figure out where the row's data is
> read the data
> unlock the database
>
> you have to do
>
> FOR EACH ROW:
> figure out where the row's data is
> try to lock the row
> check to see that the lock on the row is yours
> read the data
> release the row
>
> If your SELECT returns 10 rows you end up doing 50 operations instead of
> 23.  Which would mean that SQLite was half the speed, and no longer had any
> advantages for you, so you would use something else.
>
> Locking is the single hardest thing to get right when writing a DBMS.
>  SQLite gets a lot of its tininess and speed by implementing the simplest
> fastest method of locking possible.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[John McKown]

Just a personal observation from the peanut gallery (my uninformed
opinion). I like SQLite pretty much as is. When I use it, I want reliable
(ACID), fast, and SQL compliant. I use SQLite more like an "embeded" or
"single user" SQL engine. I don't use it for a really hairy data base
application. Yes, I know that it can indeed do that work. But if I need
multi-system, multi-user, redundant RDMS, I use PostgreSQL. It is "heavy"
but works well for me. I don't want to replace PostgreSQL with SQLite, in
general. Again, I know that there are massive SQLite DBs out there. But are
they being hit by 100+ users concurrently? I truly don't know.

But if it is up for a vote, I vote "stay lean".


On Sun, Nov 10, 2013 at 9:09 AM, Simon Slavin  wrote:

>
> On 10 Nov 2013, at 12:05pm, Raheel Gupta  wrote:
>
> >>> I can't think of any other single feature that would remove the "lite"
> >
> > I am not a database expert. If you say so, it must be the case.
> > But if there is a way to implement concurrent writers in SQLite
> maintaining
> > the "lite" in SQLite, I would be the most happiest person here :)
>
> The main reason you seem to prefer SQLite to other databases is that it's
> faster.  Adding row-level locking to SQLite would slow it down a lot.  As a
> very simplified explanation, for one SELECT instead of
>
> try to lock the database
> check to see that the lock on the database is yours
> FOR EACH ROW:
> figure out where the row's data is
> read the data
> unlock the database
>
> you have to do
>
> FOR EACH ROW:
> figure out where the row's data is
> try to lock the row
> check to see that the lock on the row is yours
> read the data
> release the row
>
> If your SELECT returns 10 rows you end up doing 50 operations instead of
> 23.  Which would mean that SQLite was half the speed, and no longer had any
> advantages for you, so you would use something else.
>
> Locking is the single hardest thing to get right when writing a DBMS.
>  SQLite gets a lot of its tininess and speed by implementing the simplest
> fastest method of locking possible.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
This is clearly another case of too many mad scientists, and not enough
hunchbacks.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Simon Slavin]

On 10 Nov 2013, at 12:05pm, Raheel Gupta  wrote:

>>> I can't think of any other single feature that would remove the "lite"
> 
> I am not a database expert. If you say so, it must be the case.
> But if there is a way to implement concurrent writers in SQLite maintaining
> the "lite" in SQLite, I would be the most happiest person here :)

The main reason you seem to prefer SQLite to other databases is that it's 
faster.  Adding row-level locking to SQLite would slow it down a lot.  As a 
very simplified explanation, for one SELECT instead of

try to lock the database
check to see that the lock on the database is yours
FOR EACH ROW:
figure out where the row's data is
read the data
unlock the database

you have to do

FOR EACH ROW:
figure out where the row's data is
try to lock the row
check to see that the lock on the row is yours
read the data
release the row

If your SELECT returns 10 rows you end up doing 50 operations instead of 23.  
Which would mean that SQLite was half the speed, and no longer had any 
advantages for you, so you would use something else.

Locking is the single hardest thing to get right when writing a DBMS.  SQLite 
gets a lot of its tininess and speed by implementing the simplest fastest 
method of locking possible.

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

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

Raheel Gupta wrote:

Look at the performance difference between BDB and SQLite3 here

http://symas.com/mdb/microbench/#sec1

I did, and I really cant comment on that. The results are of 2012 and its
almost 2013. You should update the page with a newer result set.


Or you could just download the code and run it yourself.


I can't think of any other single feature that would remove the "lite"


I am not a database expert. If you say so, it must be the case.
But if there is a way to implement concurrent writers in SQLite maintaining
the "lite" in SQLite, I would be the most happiest person here :)


You seem to enjoy asking a lot of others, without regard to cost.

--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

>> Look at the performance difference between BDB and SQLite3 here
http://symas.com/mdb/microbench/#sec1

I did, and I really cant comment on that. The results are of 2012 and its
almost 2013. You should update the page with a newer result set.

>> I can't think of any other single feature that would remove the "lite"

I am not a database expert. If you say so, it must be the case.
But if there is a way to implement concurrent writers in SQLite maintaining
the "lite" in SQLite, I would be the most happiest person here :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Ryan Johnson]

On 08/11/2013 5:07 AM, Raheel Gupta wrote:

No. It's not even feature-frozen yet, as far as we know. And whenever it

is, it's incredibly unlikely to have row level locking.

Please add row-level locking if possible.
I can't think of any other single feature that would remove the "lite" 
from sqlite3 more thoroughly than adding support for row-level locking 
(or any other fine-grained concurrency control scheme).


I'm not an official sqlite3 dev, but I've seen the underbellies of 
enough database engines to be pretty confident saying this. A good 
implementation of fine-grained concurrency control and reentrancy adds a 
drastic amount of complexity and overhead to a database engine, and 
usually slows it down quite a bit unless you're willing to sacrifice 
features or generality.


Ryan

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

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

Raheel Gupta wrote:

Hi,


You use BDB SQL or BDB KV ?

I built BDB 6.0.20 with --enable-sql_compat
It made a libsqlite3.so in the .libs folder which I linked with my QT C++
Application.


You must try it with SQLightning too, https://gitorious.org/mdb/

sqlightning

I tried to build it, but it says lmdb.h missing. Will check it as well.


You must copy (or symlink) the lmdb source code into the SQLightning source 
directory.



On Fri, Nov 8, 2013 at 4:12 PM, Aris Setyawan  wrote:


This is the BDB SQL doc I found.


http://docs.oracle.com/cd/E17076_02/html/bdb-sql/dbfeatures.html#bulkloading

If you insert record in bulk, you can use PRAGMA TXN_BULK for optimization.



I tested TXN_BULK, still pretty slow. Nearly 2.4 times.
Also the space wastage is pretty high. I set a page size of 64K in this and
the space wastage is very high as the DBNAME.db-journal folder has too many
files.
DBNAME.db is 448MB
DBNAME.db-journal is 161MB.

Which is after inserting 10 rows of 4K = 400MB.

In SQLITE 3.7.17 I get only 6.69% wastage of space.
I wish BDB worked faster to see my alternatives.


BDB is inherently a very slow codebase. Look at the performance difference 
between BDB and SQLite3 here http://symas.com/mdb/microbench/#sec1 - on random 
writes BDB KV is only 8% faster than SQLite. If you add the SQL parsing 
overhead on top of that, that 8% performance margin is erased. If you use 
batched random writes, SQLite is already 2% faster than BDB KV, so BDB SQL can 
only be slower than SQLite.


Whatever other differences there may be, there is no performance benefit to 
using BDB as a backend for SQLite. In most cases there is a performance loss.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

>
> This is the BDB SQL doc I found.
>
>
> http://docs.oracle.com/cd/E17076_02/html/bdb-sql/dbfeatures.html#bulkloading
>
> If you insert record in bulk, you can use PRAGMA TXN_BULK for optimization.
>
I tested TXN_BULK, still pretty slow. Nearly 2.4 times.
Also the space wastage is pretty high. I set a page size of 64K in this and
the space wastage is very high as the DBNAME.db-journal folder has too many
files.
DBNAME.db is 448MB
DBNAME.db-journal is 161MB.

Which is after inserting 10 rows of 4K = 400MB.

In SQLITE 3.7.17 I get only 6.69% wastage of space.
I wish BDB worked faster to see my alternatives.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

Hi,

>> You use BDB SQL or BDB KV ?
I built BDB 6.0.20 with --enable-sql_compat
It made a libsqlite3.so in the .libs folder which I linked with my QT C++
Application.

>> You must try it with SQLightning too, https://gitorious.org/mdb/
sqlightning

I tried to build it, but it says lmdb.h missing. Will check it as well.



On Fri, Nov 8, 2013 at 4:12 PM, Aris Setyawan  wrote:

> This is the BDB SQL doc I found.
>
>
> http://docs.oracle.com/cd/E17076_02/html/bdb-sql/dbfeatures.html#bulkloading
>
> If you insert record in bulk, you can use PRAGMA TXN_BULK for optimization.
>
>
>
> On 11/8/13, Aris Setyawan  wrote:
> > You use BDB SQL or BDB KV ?
> >
> > You must try it with SQLightning too,
> https://gitorious.org/mdb/sqlightning
> >
> >
> > On 11/8/13, Aris Setyawan  wrote:
> >>> For a single threaded application BDB is very bad after I tested.
> >>> It takes nearly 2.5 times the amount of time and CPU to do a
> transaction
> >>> of
> >>> 40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of
> 4
> >>> K)
> >>> in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did
> this
> >>> in
> >>> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts.
> >>> That
> >>> is what I found.
> >>
> >> Have you consult this to the BDB forum?
> >> BDB doesn't have SQL parsing overhead, so it will be faster in general.
> >>
> >> On 11/8/13, Raheel Gupta  wrote:
> > If you have many core of processors [and big RAM], then I recommend
> >>> BDB Sql over Sqlite. Because you can have many processes or threads to
> >>> write to a database concurrently.
> >>>
> >>> For a single threaded application BDB is very bad after I tested.
> >>> It takes nearly 2.5 times the amount of time and CPU to do a
> transaction
> >>> of
> >>> 40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of
> 4
> >>> K)
> >>> in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did
> this
> >>> in
> >>> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts.
> >>> That
> >>> is what I found.
> >>>
> >>>
> >>>
> >>> On Fri, Nov 8, 2013 at 12:03 AM, Simon Slavin 
> >>> wrote:
> >>>
> 
>  On 7 Nov 2013, at 6:31pm, Raheel Gupta  wrote:
> 
>  > Any idea when will SQLite4 be released as stable ?
> 
>  No.  It's not even feature-frozen yet, as far as we know.  And
> whenever
>  it
>  is, it's incredibly unlikely to have row level locking.
> 
>  Simon.
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> >>> ___
> >>> sqlite-users mailing list
> >>> sqlite-users@sqlite.org
> >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>>
> >>
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

This is the BDB SQL doc I found.

http://docs.oracle.com/cd/E17076_02/html/bdb-sql/dbfeatures.html#bulkloading

If you insert record in bulk, you can use PRAGMA TXN_BULK for optimization.



On 11/8/13, Aris Setyawan  wrote:
> You use BDB SQL or BDB KV ?
>
> You must try it with SQLightning too, https://gitorious.org/mdb/sqlightning
>
>
> On 11/8/13, Aris Setyawan  wrote:
>>> For a single threaded application BDB is very bad after I tested.
>>> It takes nearly 2.5 times the amount of time and CPU to do a transaction
>>> of
>>> 40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of 4
>>> K)
>>> in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did this
>>> in
>>> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts.
>>> That
>>> is what I found.
>>
>> Have you consult this to the BDB forum?
>> BDB doesn't have SQL parsing overhead, so it will be faster in general.
>>
>> On 11/8/13, Raheel Gupta  wrote:
> If you have many core of processors [and big RAM], then I recommend
>>> BDB Sql over Sqlite. Because you can have many processes or threads to
>>> write to a database concurrently.
>>>
>>> For a single threaded application BDB is very bad after I tested.
>>> It takes nearly 2.5 times the amount of time and CPU to do a transaction
>>> of
>>> 40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of 4
>>> K)
>>> in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did this
>>> in
>>> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts.
>>> That
>>> is what I found.
>>>
>>>
>>>
>>> On Fri, Nov 8, 2013 at 12:03 AM, Simon Slavin 
>>> wrote:
>>>

 On 7 Nov 2013, at 6:31pm, Raheel Gupta  wrote:

 > Any idea when will SQLite4 be released as stable ?

 No.  It's not even feature-frozen yet, as far as we know.  And whenever
 it
 is, it's incredibly unlikely to have row level locking.

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

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

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

You use BDB SQL or BDB KV ?

You must try it with SQLightning too, https://gitorious.org/mdb/sqlightning


On 11/8/13, Aris Setyawan  wrote:
>> For a single threaded application BDB is very bad after I tested.
>> It takes nearly 2.5 times the amount of time and CPU to do a transaction
>> of
>> 40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of 4
>> K)
>> in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did this
>> in
>> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts. That
>> is what I found.
>
> Have you consult this to the BDB forum?
> BDB doesn't have SQL parsing overhead, so it will be faster in general.
>
> On 11/8/13, Raheel Gupta  wrote:
 If you have many core of processors [and big RAM], then I recommend
>> BDB Sql over Sqlite. Because you can have many processes or threads to
>> write to a database concurrently.
>>
>> For a single threaded application BDB is very bad after I tested.
>> It takes nearly 2.5 times the amount of time and CPU to do a transaction
>> of
>> 40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of 4
>> K)
>> in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did this
>> in
>> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts. That
>> is what I found.
>>
>>
>>
>> On Fri, Nov 8, 2013 at 12:03 AM, Simon Slavin 
>> wrote:
>>
>>>
>>> On 7 Nov 2013, at 6:31pm, Raheel Gupta  wrote:
>>>
>>> > Any idea when will SQLite4 be released as stable ?
>>>
>>> No.  It's not even feature-frozen yet, as far as we know.  And whenever
>>> it
>>> is, it's incredibly unlikely to have row level locking.
>>>
>>> Simon.
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

> For a single threaded application BDB is very bad after I tested.
> It takes nearly 2.5 times the amount of time and CPU to do a transaction of
> 40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of 4 K)
> in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did this in
> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts. That
> is what I found.

Have you consult this to the BDB forum?
BDB doesn't have SQL parsing overhead, so it will be faster in general.

On 11/8/13, Raheel Gupta  wrote:
>>> If you have many core of processors [and big RAM], then I recommend
> BDB Sql over Sqlite. Because you can have many processes or threads to
> write to a database concurrently.
>
> For a single threaded application BDB is very bad after I tested.
> It takes nearly 2.5 times the amount of time and CPU to do a transaction of
> 40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of 4 K)
> in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did this in
> QT C++. Overall BDB SQL interface is slower than Sqlite for inserts. That
> is what I found.
>
>
>
> On Fri, Nov 8, 2013 at 12:03 AM, Simon Slavin  wrote:
>
>>
>> On 7 Nov 2013, at 6:31pm, Raheel Gupta  wrote:
>>
>> > Any idea when will SQLite4 be released as stable ?
>>
>> No.  It's not even feature-frozen yet, as far as we know.  And whenever
>> it
>> is, it's incredibly unlikely to have row level locking.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

>> No. It's not even feature-frozen yet, as far as we know. And whenever it
is, it's incredibly unlikely to have row level locking.

Please add row-level locking if possible.


On Fri, Nov 8, 2013 at 12:03 AM, Simon Slavin  wrote:

>
> On 7 Nov 2013, at 6:31pm, Raheel Gupta  wrote:
>
> > Any idea when will SQLite4 be released as stable ?
>
> No.  It's not even feature-frozen yet, as far as we know.  And whenever it
> is, it's incredibly unlikely to have row level locking.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

>> If you have many core of processors [and big RAM], then I recommend
BDB Sql over Sqlite. Because you can have many processes or threads to
write to a database concurrently.

For a single threaded application BDB is very bad after I tested.
It takes nearly 2.5 times the amount of time and CPU to do a transaction of
40MB Data. E.g. If SQLIte did the 40MB data transaction (1 rows of 4 K)
in 1 second, BDB was taking 2.5 seconds and more CPU as well. I did this in
QT C++. Overall BDB SQL interface is slower than Sqlite for inserts. That
is what I found.



On Fri, Nov 8, 2013 at 12:03 AM, Simon Slavin  wrote:

>
> On 7 Nov 2013, at 6:31pm, Raheel Gupta  wrote:
>
> > Any idea when will SQLite4 be released as stable ?
>
> No.  It's not even feature-frozen yet, as far as we know.  And whenever it
> is, it's incredibly unlikely to have row level locking.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Simon Slavin]

On 7 Nov 2013, at 6:31pm, Raheel Gupta  wrote:

> Any idea when will SQLite4 be released as stable ?

No.  It's not even feature-frozen yet, as far as we know.  And whenever it is, 
it's incredibly unlikely to have row level locking.

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

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

>> SQLite4 still in development phase. It is not production ready.

Any idea when will SQLite4 be released as stable ?


On Tue, Nov 5, 2013 at 8:20 AM, Howard Chu  wrote:

> Aris Setyawan wrote:
>
>> Hi Howard,
>>
>>  I just looked, sophia is nothing special. See these microbench results.
>>> http://pastebin.com/cFK1JsCN
>>>
>>> LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
>>> read
>>> speed.
>>>
>>
>> This is micro benchmark from sophia author compare with lmdb.
>> http://sphia.org/benchmarks.html
>>
>
> Quite off-topic for this list, but those results are garbage.
> https://github.com/pmwkaa/sophia_benchmark/issues/2#issuecomment-27740082
>
>
> --
>   -- Howard Chu
>   CTO, Symas Corp.   http://www.symas.com
>   Director, Highland Sun http://highlandsun.com/hyc/
>   Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

Aris Setyawan wrote:

Hi Howard,


I just looked, sophia is nothing special. See these microbench results.
http://pastebin.com/cFK1JsCN

LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
read
speed.


This is micro benchmark from sophia author compare with lmdb.
http://sphia.org/benchmarks.html


Quite off-topic for this list, but those results are garbage.
https://github.com/pmwkaa/sophia_benchmark/issues/2#issuecomment-27740082

--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

Hi Howard,

> I just looked, sophia is nothing special. See these microbench results.
> http://pastebin.com/cFK1JsCN
>
> LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
> read
> speed.

This is micro benchmark from sophia author compare with lmdb.
http://sphia.org/benchmarks.html

The benchmark is different in:
1. All read or write benchmark shouldn't be done in one transaction, but you do.
2. Between one benchmark to another (run read test just after a write
one), try to drop all caches in-memory and open database, this can
model situation
when data can't be in-memory because database is too high to fit them in.
3. Because sophia is optimized for write, the benchmark use 3 million
and 10 million record.

Hope this is can be an input for your development.

On 11/5/13, Raheel Gupta  wrote:
>>> SQLite4 still in development phase. It is not production ready.
>
> But isnt that the same thing as BDB or Kyoto i.e. a Key Value store ?
>
>>> If you have many core of processors [and big RAM], then I recommend
> BDB Sql over Sqlite.
>
> I have large space and around 4GB of ram with Dual Cores to Quad Cores
> processors meant only for storage.
>
>>> If you can choose DBMS, other than SQLite, try to use DB that have
> storage engine optimized for write, for example LSM (hypertable),
> Fractal Tree (tokudb engine for mysql).
>
> I would be interested in a embedded DB which can give good performance i.e.
> write fast with indexing and read fast as well.
>
>
> On Mon, Nov 4, 2013 at 6:42 PM, Aris Setyawan  wrote:
>
>> > Will SQLite4 be a better solution for me then ?
>>
>> SQLite4 still in development phase. It is not production ready.
>>
>> > Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?
>>
>> If you have many core of processors [and big RAM], then I recommend
>> BDB Sql over Sqlite. Because you can have many processes or threads to
>> write to a database concurrently.  Because it use row or page level
>> locking.
>>
>> If you can choose DBMS, other than SQLite, try to use DB that have
>> storage engine optimized for write, for example LSM (hypertable),
>> Fractal Tree (tokudb engine for mysql).
>>
>> On 11/4/13, Raheel Gupta  wrote:
>> > Will SQLite4 be a better solution for me then ?
>> >
>> > Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?
>> >
>> >
>> > On Mon, Nov 4, 2013 at 8:03 AM, Aris Setyawan 
>> wrote:
>> >
>> >> > I just looked, sophia is nothing special. See these microbench
>> results.
>> >> > http://pastebin.com/cFK1JsCN
>> >> >
>> >> > LMDB's codebase is still smaller and faster. Nothing else touches
>> >> > LMDB's
>> >> > read
>> >> > speed.
>> >>
>> >> Focus to the write number.
>> >>
>> >> You are using SSD or HDD?
>> >>
>> >> On 11/4/13, Howard Chu  wrote:
>> >> > Aris Setyawan wrote:
>> >> >>> SQLightning replaces the SQLite backend with Symas' LMDB, which
>> >> >>> also
>> >> >>> uses
>> >> >>> MVCC
>> >> >>> and thus supports high concurrency. It is also many times faster
>> than
>> >> >>> BerkeleyDB and vanilla SQLite.
>> >> >>
>> >> >> Your MVCC is different compared to InnoDB or BDB locking. Every one
>> >> >> should carefully read each DB's doc, then test it before decide to
>> use
>> >> >> it.
>> >> >
>> >> > Yes, it's different. In LMDB writers never block readers and readers
>> >> never
>> >> > block writers. The original poster was complaining about SELECT
>> >> > taking
>> >> > a
>> >> > long
>> >> > time and preventing other threads from making progress. That problem
>> >> doesn't
>> >> >
>> >> > exist in LMDB. BDB locking *might* be able to avoid this in many
>> cases,
>> >> if
>> >> > there are no hotspots, but is prone to deadlocks which require the
>> >> calling
>> >> > application to retry failed requests.
>> >> >
>> >> >> LMDB is storage engine optimized for read. Why you don't optimize
>> it's
>> >> >> write using cache oblivious data structure, for example LSM tree or
>> >> >> create new, like in sophia (sphia.org) key value DB?
>> >> >
>> >> > I just looked, sophia is nothing special. See these microbench
>> results.
>> >> > http://pastebin.com/cFK1JsCN
>> >> >
>> >> > LMDB's codebase is still smaller and faster. Nothing else touches
>> >> > LMDB's
>> >> > read
>> >> > speed.
>> >> >
>> >> > --
>> >> >-- Howard Chu
>> >> >CTO, Symas Corp.   http://www.symas.com
>> >> >Director, Highland Sun http://highlandsun.com/hyc/
>> >> >Chief Architect, OpenLDAP  http://www.openldap.org/project/
>> >> > ___
>> >> > sqlite-users mailing list
>> >> > sqlite-users@sqlite.org
>> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >> >
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@sqlite.org
>> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> > 

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

>> SQLite4 still in development phase. It is not production ready.

But isnt that the same thing as BDB or Kyoto i.e. a Key Value store ?

>> If you have many core of processors [and big RAM], then I recommend
BDB Sql over Sqlite.

I have large space and around 4GB of ram with Dual Cores to Quad Cores
processors meant only for storage.

>> If you can choose DBMS, other than SQLite, try to use DB that have
storage engine optimized for write, for example LSM (hypertable),
Fractal Tree (tokudb engine for mysql).

I would be interested in a embedded DB which can give good performance i.e.
write fast with indexing and read fast as well.


On Mon, Nov 4, 2013 at 6:42 PM, Aris Setyawan  wrote:

> > Will SQLite4 be a better solution for me then ?
>
> SQLite4 still in development phase. It is not production ready.
>
> > Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?
>
> If you have many core of processors [and big RAM], then I recommend
> BDB Sql over Sqlite. Because you can have many processes or threads to
> write to a database concurrently.  Because it use row or page level
> locking.
>
> If you can choose DBMS, other than SQLite, try to use DB that have
> storage engine optimized for write, for example LSM (hypertable),
> Fractal Tree (tokudb engine for mysql).
>
> On 11/4/13, Raheel Gupta  wrote:
> > Will SQLite4 be a better solution for me then ?
> >
> > Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?
> >
> >
> > On Mon, Nov 4, 2013 at 8:03 AM, Aris Setyawan 
> wrote:
> >
> >> > I just looked, sophia is nothing special. See these microbench
> results.
> >> > http://pastebin.com/cFK1JsCN
> >> >
> >> > LMDB's codebase is still smaller and faster. Nothing else touches
> >> > LMDB's
> >> > read
> >> > speed.
> >>
> >> Focus to the write number.
> >>
> >> You are using SSD or HDD?
> >>
> >> On 11/4/13, Howard Chu  wrote:
> >> > Aris Setyawan wrote:
> >> >>> SQLightning replaces the SQLite backend with Symas' LMDB, which also
> >> >>> uses
> >> >>> MVCC
> >> >>> and thus supports high concurrency. It is also many times faster
> than
> >> >>> BerkeleyDB and vanilla SQLite.
> >> >>
> >> >> Your MVCC is different compared to InnoDB or BDB locking. Every one
> >> >> should carefully read each DB's doc, then test it before decide to
> use
> >> >> it.
> >> >
> >> > Yes, it's different. In LMDB writers never block readers and readers
> >> never
> >> > block writers. The original poster was complaining about SELECT taking
> >> > a
> >> > long
> >> > time and preventing other threads from making progress. That problem
> >> doesn't
> >> >
> >> > exist in LMDB. BDB locking *might* be able to avoid this in many
> cases,
> >> if
> >> > there are no hotspots, but is prone to deadlocks which require the
> >> calling
> >> > application to retry failed requests.
> >> >
> >> >> LMDB is storage engine optimized for read. Why you don't optimize
> it's
> >> >> write using cache oblivious data structure, for example LSM tree or
> >> >> create new, like in sophia (sphia.org) key value DB?
> >> >
> >> > I just looked, sophia is nothing special. See these microbench
> results.
> >> > http://pastebin.com/cFK1JsCN
> >> >
> >> > LMDB's codebase is still smaller and faster. Nothing else touches
> >> > LMDB's
> >> > read
> >> > speed.
> >> >
> >> > --
> >> >-- Howard Chu
> >> >CTO, Symas Corp.   http://www.symas.com
> >> >Director, Highland Sun http://highlandsun.com/hyc/
> >> >Chief Architect, OpenLDAP  http://www.openldap.org/project/
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

> Will SQLite4 be a better solution for me then ?

SQLite4 still in development phase. It is not production ready.

> Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?

If you have many core of processors [and big RAM], then I recommend
BDB Sql over Sqlite. Because you can have many processes or threads to
write to a database concurrently.  Because it use row or page level
locking.

If you can choose DBMS, other than SQLite, try to use DB that have
storage engine optimized for write, for example LSM (hypertable),
Fractal Tree (tokudb engine for mysql).

On 11/4/13, Raheel Gupta  wrote:
> Will SQLite4 be a better solution for me then ?
>
> Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?
>
>
> On Mon, Nov 4, 2013 at 8:03 AM, Aris Setyawan  wrote:
>
>> > I just looked, sophia is nothing special. See these microbench results.
>> > http://pastebin.com/cFK1JsCN
>> >
>> > LMDB's codebase is still smaller and faster. Nothing else touches
>> > LMDB's
>> > read
>> > speed.
>>
>> Focus to the write number.
>>
>> You are using SSD or HDD?
>>
>> On 11/4/13, Howard Chu  wrote:
>> > Aris Setyawan wrote:
>> >>> SQLightning replaces the SQLite backend with Symas' LMDB, which also
>> >>> uses
>> >>> MVCC
>> >>> and thus supports high concurrency. It is also many times faster than
>> >>> BerkeleyDB and vanilla SQLite.
>> >>
>> >> Your MVCC is different compared to InnoDB or BDB locking. Every one
>> >> should carefully read each DB's doc, then test it before decide to use
>> >> it.
>> >
>> > Yes, it's different. In LMDB writers never block readers and readers
>> never
>> > block writers. The original poster was complaining about SELECT taking
>> > a
>> > long
>> > time and preventing other threads from making progress. That problem
>> doesn't
>> >
>> > exist in LMDB. BDB locking *might* be able to avoid this in many cases,
>> if
>> > there are no hotspots, but is prone to deadlocks which require the
>> calling
>> > application to retry failed requests.
>> >
>> >> LMDB is storage engine optimized for read. Why you don't optimize it's
>> >> write using cache oblivious data structure, for example LSM tree or
>> >> create new, like in sophia (sphia.org) key value DB?
>> >
>> > I just looked, sophia is nothing special. See these microbench results.
>> > http://pastebin.com/cFK1JsCN
>> >
>> > LMDB's codebase is still smaller and faster. Nothing else touches
>> > LMDB's
>> > read
>> > speed.
>> >
>> > --
>> >-- Howard Chu
>> >CTO, Symas Corp.   http://www.symas.com
>> >Director, Highland Sun http://highlandsun.com/hyc/
>> >Chief Architect, OpenLDAP  http://www.openldap.org/project/
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

Will SQLite4 be a better solution for me then ?

Also @aris do you recommend BDB over Sqlite for 1-10 Billion records ?


On Mon, Nov 4, 2013 at 8:03 AM, Aris Setyawan  wrote:

> > I just looked, sophia is nothing special. See these microbench results.
> > http://pastebin.com/cFK1JsCN
> >
> > LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
> > read
> > speed.
>
> Focus to the write number.
>
> You are using SSD or HDD?
>
> On 11/4/13, Howard Chu  wrote:
> > Aris Setyawan wrote:
> >>> SQLightning replaces the SQLite backend with Symas' LMDB, which also
> >>> uses
> >>> MVCC
> >>> and thus supports high concurrency. It is also many times faster than
> >>> BerkeleyDB and vanilla SQLite.
> >>
> >> Your MVCC is different compared to InnoDB or BDB locking. Every one
> >> should carefully read each DB's doc, then test it before decide to use
> >> it.
> >
> > Yes, it's different. In LMDB writers never block readers and readers
> never
> > block writers. The original poster was complaining about SELECT taking a
> > long
> > time and preventing other threads from making progress. That problem
> doesn't
> >
> > exist in LMDB. BDB locking *might* be able to avoid this in many cases,
> if
> > there are no hotspots, but is prone to deadlocks which require the
> calling
> > application to retry failed requests.
> >
> >> LMDB is storage engine optimized for read. Why you don't optimize it's
> >> write using cache oblivious data structure, for example LSM tree or
> >> create new, like in sophia (sphia.org) key value DB?
> >
> > I just looked, sophia is nothing special. See these microbench results.
> > http://pastebin.com/cFK1JsCN
> >
> > LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
> > read
> > speed.
> >
> > --
> >-- Howard Chu
> >CTO, Symas Corp.   http://www.symas.com
> >Director, Highland Sun http://highlandsun.com/hyc/
> >Chief Architect, OpenLDAP  http://www.openldap.org/project/
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

> I just looked, sophia is nothing special. See these microbench results.
> http://pastebin.com/cFK1JsCN
>
> LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
> read
> speed.

Focus to the write number.

You are using SSD or HDD?

On 11/4/13, Howard Chu  wrote:
> Aris Setyawan wrote:
>>> SQLightning replaces the SQLite backend with Symas' LMDB, which also
>>> uses
>>> MVCC
>>> and thus supports high concurrency. It is also many times faster than
>>> BerkeleyDB and vanilla SQLite.
>>
>> Your MVCC is different compared to InnoDB or BDB locking. Every one
>> should carefully read each DB's doc, then test it before decide to use
>> it.
>
> Yes, it's different. In LMDB writers never block readers and readers never
> block writers. The original poster was complaining about SELECT taking a
> long
> time and preventing other threads from making progress. That problem doesn't
>
> exist in LMDB. BDB locking *might* be able to avoid this in many cases, if
> there are no hotspots, but is prone to deadlocks which require the calling
> application to retry failed requests.
>
>> LMDB is storage engine optimized for read. Why you don't optimize it's
>> write using cache oblivious data structure, for example LSM tree or
>> create new, like in sophia (sphia.org) key value DB?
>
> I just looked, sophia is nothing special. See these microbench results.
> http://pastebin.com/cFK1JsCN
>
> LMDB's codebase is still smaller and faster. Nothing else touches LMDB's
> read
> speed.
>
> --
>-- Howard Chu
>CTO, Symas Corp.   http://www.symas.com
>Director, Highland Sun http://highlandsun.com/hyc/
>Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

Aris Setyawan wrote:

SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
MVCC
and thus supports high concurrency. It is also many times faster than
BerkeleyDB and vanilla SQLite.


Your MVCC is different compared to InnoDB or BDB locking. Every one
should carefully read each DB's doc, then test it before decide to use
it.


Yes, it's different. In LMDB writers never block readers and readers never 
block writers. The original poster was complaining about SELECT taking a long 
time and preventing other threads from making progress. That problem doesn't 
exist in LMDB. BDB locking *might* be able to avoid this in many cases, if 
there are no hotspots, but is prone to deadlocks which require the calling 
application to retry failed requests.



LMDB is storage engine optimized for read. Why you don't optimize it's
write using cache oblivious data structure, for example LSM tree or
create new, like in sophia (sphia.org) key value DB?


I just looked, sophia is nothing special. See these microbench results.
http://pastebin.com/cFK1JsCN

LMDB's codebase is still smaller and faster. Nothing else touches LMDB's read 
speed.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

Raheel Gupta wrote:

@Howard I had tested your code earlier but it didnt seem to be stable and
getting it to run was a task. Also I learnt that it is a "in-memory"
database.


False. LMDB is a memory-mapped disk database, that is not the same as an 
in-memory database.


@Aris are you saying BDB is better and faster than SQLite ?


Oracle claims that. From what I can see, Oracle is wrong.



On Sun, Nov 3, 2013 at 8:28 PM, Howard Chu  wrote:


Aris Setyawan wrote:


SQLightning replaces the SQLite backend with Symas' LMDB, which also uses

MVCC
and thus supports high concurrency. It is also many times faster than
BerkeleyDB and vanilla SQLite.



Your MVCC is different compared to InnoDB or BDB locking. Every one
should carefully read each DB's doc, then test it before decide to use
it.

LMDB is storage engine optimized for read. Why you don't optimize it's
write using cache oblivious data structure, for example LSM tree or
create new, like in sophia (sphia.org) key value DB?



Because read optimization is what was important to us when I created LMDB.
That's like asking why a hammer isn't a screwdriver.


  On 11/3/13, Howard Chu  wrote:



Aris Setyawan wrote:


SQLite do not use row level locking, but db level locking, so it was
the right behavior the second thread was blocked.

For innodb like in SQLite, Oracle have SQLite compatible API, but use
BDB backend.
Since BDB use MVCC (row/page level locking), your threads only blocked
if they will write in the same row/page.

www.oracle.com/technetwork/database/berkeleydb/bdb-
sqlite-comparison-wp-176431.pdf

* You must aware that BDB now have AGPL license.



SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
MVCC
and thus supports high concurrency. It is also many times faster than
BerkeleyDB and vanilla SQLite.

https://gitorious.org/mdb/sqlightning/



On 11/3/13, Raheel Gupta  wrote:


Hi,

I have been using SQLite for one project of mine and I will be storing
TBs
of Data.
Now there will be a lot of selections in this database and I have come
across one problem with SQLite.
In journal_mode=delete the selection is database locked.
When one thread does a "TRANSACTION" on the database and soon after
another
thread does "SELECT" on the database (using the same connection) or
vice
versa, the second thread has to wait till the first thread finishes.

In order to avoid this, I had to use journal_mode=wal so that two
threads
dont have to wait when they both are doing SELECTs which might be
taking
3-5 seconds to process.

I was wondering if Row Level Locking would be introduced in
journal_mode=delete as its there in InnoDB for MySQL. Atleast for
selects
or inserts Row Level rocking should be possible as neither modify the
existing rows.

journal_mode=wal is a little slower and has its own limitations over
NFS.

OR if there is a mode equivalent to innodb in SQLITE please do let me
know.
I need to do a lot of selects and inserts in my application and hence a
row
level locking is suitable vs table or database level locking.



--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

@Howard I had tested your code earlier but it didnt seem to be stable and
getting it to run was a task. Also I learnt that it is a "in-memory"
database.

@Aris are you saying BDB is better and faster than SQLite ?


On Sun, Nov 3, 2013 at 8:28 PM, Howard Chu  wrote:

> Aris Setyawan wrote:
>
>> SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
>>> MVCC
>>> and thus supports high concurrency. It is also many times faster than
>>> BerkeleyDB and vanilla SQLite.
>>>
>>
>> Your MVCC is different compared to InnoDB or BDB locking. Every one
>> should carefully read each DB's doc, then test it before decide to use
>> it.
>>
>> LMDB is storage engine optimized for read. Why you don't optimize it's
>> write using cache oblivious data structure, for example LSM tree or
>> create new, like in sophia (sphia.org) key value DB?
>>
>
> Because read optimization is what was important to us when I created LMDB.
> That's like asking why a hammer isn't a screwdriver.
>
>
>  On 11/3/13, Howard Chu  wrote:
>>
>>> Aris Setyawan wrote:
>>>
 SQLite do not use row level locking, but db level locking, so it was
 the right behavior the second thread was blocked.

 For innodb like in SQLite, Oracle have SQLite compatible API, but use
 BDB backend.
 Since BDB use MVCC (row/page level locking), your threads only blocked
 if they will write in the same row/page.

 www.oracle.com/technetwork/database/berkeleydb/bdb-
 sqlite-comparison-wp-176431.pdf

 * You must aware that BDB now have AGPL license.

>>>
>>> SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
>>> MVCC
>>> and thus supports high concurrency. It is also many times faster than
>>> BerkeleyDB and vanilla SQLite.
>>>
>>> https://gitorious.org/mdb/sqlightning/
>>>
>>>
 On 11/3/13, Raheel Gupta  wrote:

> Hi,
>
> I have been using SQLite for one project of mine and I will be storing
> TBs
> of Data.
> Now there will be a lot of selections in this database and I have come
> across one problem with SQLite.
> In journal_mode=delete the selection is database locked.
> When one thread does a "TRANSACTION" on the database and soon after
> another
> thread does "SELECT" on the database (using the same connection) or
> vice
> versa, the second thread has to wait till the first thread finishes.
>
> In order to avoid this, I had to use journal_mode=wal so that two
> threads
> dont have to wait when they both are doing SELECTs which might be
> taking
> 3-5 seconds to process.
>
> I was wondering if Row Level Locking would be introduced in
> journal_mode=delete as its there in InnoDB for MySQL. Atleast for
> selects
> or inserts Row Level rocking should be possible as neither modify the
> existing rows.
>
> journal_mode=wal is a little slower and has its own limitations over
> NFS.
>
> OR if there is a mode equivalent to innodb in SQLITE please do let me
> know.
> I need to do a lot of selects and inserts in my application and hence a
> row
> level locking is suitable vs table or database level locking.
>

>
> --
>   -- Howard Chu
>   CTO, Symas Corp.   http://www.symas.com
>   Director, Highland Sun http://highlandsun.com/hyc/
>   Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

Aris Setyawan wrote:

SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
MVCC
and thus supports high concurrency. It is also many times faster than
BerkeleyDB and vanilla SQLite.


Your MVCC is different compared to InnoDB or BDB locking. Every one
should carefully read each DB's doc, then test it before decide to use
it.

LMDB is storage engine optimized for read. Why you don't optimize it's
write using cache oblivious data structure, for example LSM tree or
create new, like in sophia (sphia.org) key value DB?


Because read optimization is what was important to us when I created LMDB. 
That's like asking why a hammer isn't a screwdriver.



On 11/3/13, Howard Chu  wrote:

Aris Setyawan wrote:

SQLite do not use row level locking, but db level locking, so it was
the right behavior the second thread was blocked.

For innodb like in SQLite, Oracle have SQLite compatible API, but use
BDB backend.
Since BDB use MVCC (row/page level locking), your threads only blocked
if they will write in the same row/page.

www.oracle.com/technetwork/database/berkeleydb/bdb-sqlite-comparison-wp-176431.pdf

* You must aware that BDB now have AGPL license.


SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
MVCC
and thus supports high concurrency. It is also many times faster than
BerkeleyDB and vanilla SQLite.

https://gitorious.org/mdb/sqlightning/



On 11/3/13, Raheel Gupta  wrote:

Hi,

I have been using SQLite for one project of mine and I will be storing
TBs
of Data.
Now there will be a lot of selections in this database and I have come
across one problem with SQLite.
In journal_mode=delete the selection is database locked.
When one thread does a "TRANSACTION" on the database and soon after
another
thread does "SELECT" on the database (using the same connection) or vice
versa, the second thread has to wait till the first thread finishes.

In order to avoid this, I had to use journal_mode=wal so that two
threads
dont have to wait when they both are doing SELECTs which might be taking
3-5 seconds to process.

I was wondering if Row Level Locking would be introduced in
journal_mode=delete as its there in InnoDB for MySQL. Atleast for
selects
or inserts Row Level rocking should be possible as neither modify the
existing rows.

journal_mode=wal is a little slower and has its own limitations over
NFS.

OR if there is a mode equivalent to innodb in SQLITE please do let me
know.
I need to do a lot of selects and inserts in my application and hence a
row
level locking is suitable vs table or database level locking.



--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

> SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
> MVCC
> and thus supports high concurrency. It is also many times faster than
> BerkeleyDB and vanilla SQLite.

Your MVCC is different compared to InnoDB or BDB locking. Every one
should carefully read each DB's doc, then test it before decide to use
it.

LMDB is storage engine optimized for read. Why you don't optimize it's
write using cache oblivious data structure, for example LSM tree or
create new, like in sophia (sphia.org) key value DB?

On 11/3/13, Howard Chu  wrote:
> Aris Setyawan wrote:
>> SQLite do not use row level locking, but db level locking, so it was
>> the right behavior the second thread was blocked.
>>
>> For innodb like in SQLite, Oracle have SQLite compatible API, but use
>> BDB backend.
>> Since BDB use MVCC (row/page level locking), your threads only blocked
>> if they will write in the same row/page.
>>
>> www.oracle.com/technetwork/database/berkeleydb/bdb-sqlite-comparison-wp-176431.pdf
>>
>> * You must aware that BDB now have AGPL license.
>
> SQLightning replaces the SQLite backend with Symas' LMDB, which also uses
> MVCC
> and thus supports high concurrency. It is also many times faster than
> BerkeleyDB and vanilla SQLite.
>
> https://gitorious.org/mdb/sqlightning/
>
>>
>> On 11/3/13, Raheel Gupta  wrote:
>>> Hi,
>>>
>>> I have been using SQLite for one project of mine and I will be storing
>>> TBs
>>> of Data.
>>> Now there will be a lot of selections in this database and I have come
>>> across one problem with SQLite.
>>> In journal_mode=delete the selection is database locked.
>>> When one thread does a "TRANSACTION" on the database and soon after
>>> another
>>> thread does "SELECT" on the database (using the same connection) or vice
>>> versa, the second thread has to wait till the first thread finishes.
>>>
>>> In order to avoid this, I had to use journal_mode=wal so that two
>>> threads
>>> dont have to wait when they both are doing SELECTs which might be taking
>>> 3-5 seconds to process.
>>>
>>> I was wondering if Row Level Locking would be introduced in
>>> journal_mode=delete as its there in InnoDB for MySQL. Atleast for
>>> selects
>>> or inserts Row Level rocking should be possible as neither modify the
>>> existing rows.
>>>
>>> journal_mode=wal is a little slower and has its own limitations over
>>> NFS.
>>>
>>> OR if there is a mode equivalent to innodb in SQLITE please do let me
>>> know.
>>> I need to do a lot of selects and inserts in my application and hence a
>>> row
>>> level locking is suitable vs table or database level locking.
>
>
> --
>-- Howard Chu
>CTO, Symas Corp.   http://www.symas.com
>Director, Highland Sun http://highlandsun.com/hyc/
>Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Howard Chu]

Aris Setyawan wrote:

SQLite do not use row level locking, but db level locking, so it was
the right behavior the second thread was blocked.

For innodb like in SQLite, Oracle have SQLite compatible API, but use
BDB backend.
Since BDB use MVCC (row/page level locking), your threads only blocked
if they will write in the same row/page.

www.oracle.com/technetwork/database/berkeleydb/bdb-sqlite-comparison-wp-176431.pdf

* You must aware that BDB now have AGPL license.


SQLightning replaces the SQLite backend with Symas' LMDB, which also uses MVCC 
and thus supports high concurrency. It is also many times faster than 
BerkeleyDB and vanilla SQLite.


https://gitorious.org/mdb/sqlightning/



On 11/3/13, Raheel Gupta  wrote:

Hi,

I have been using SQLite for one project of mine and I will be storing TBs
of Data.
Now there will be a lot of selections in this database and I have come
across one problem with SQLite.
In journal_mode=delete the selection is database locked.
When one thread does a "TRANSACTION" on the database and soon after another
thread does "SELECT" on the database (using the same connection) or vice
versa, the second thread has to wait till the first thread finishes.

In order to avoid this, I had to use journal_mode=wal so that two threads
dont have to wait when they both are doing SELECTs which might be taking
3-5 seconds to process.

I was wondering if Row Level Locking would be introduced in
journal_mode=delete as its there in InnoDB for MySQL. Atleast for selects
or inserts Row Level rocking should be possible as neither modify the
existing rows.

journal_mode=wal is a little slower and has its own limitations over NFS.

OR if there is a mode equivalent to innodb in SQLITE please do let me know.
I need to do a lot of selects and inserts in my application and hence a row
level locking is suitable vs table or database level locking.



--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Aris Setyawan]

SQLite do not use row level locking, but db level locking, so it was
the right behavior the second thread was blocked.

For innodb like in SQLite, Oracle have SQLite compatible API, but use
BDB backend.
Since BDB use MVCC (row/page level locking), your threads only blocked
if they will write in the same row/page.

www.oracle.com/technetwork/database/berkeleydb/bdb-sqlite-comparison-wp-176431.pdf

* You must aware that BDB now have AGPL license.

On 11/3/13, Raheel Gupta  wrote:
> Hi,
>
> I have been using SQLite for one project of mine and I will be storing TBs
> of Data.
> Now there will be a lot of selections in this database and I have come
> across one problem with SQLite.
> In journal_mode=delete the selection is database locked.
> When one thread does a "TRANSACTION" on the database and soon after another
> thread does "SELECT" on the database (using the same connection) or vice
> versa, the second thread has to wait till the first thread finishes.
>
> In order to avoid this, I had to use journal_mode=wal so that two threads
> dont have to wait when they both are doing SELECTs which might be taking
> 3-5 seconds to process.
>
> I was wondering if Row Level Locking would be introduced in
> journal_mode=delete as its there in InnoDB for MySQL. Atleast for selects
> or inserts Row Level rocking should be possible as neither modify the
> existing rows.
>
> journal_mode=wal is a little slower and has its own limitations over NFS.
>
> OR if there is a mode equivalent to innodb in SQLITE please do let me know.
> I need to do a lot of selects and inserts in my application and hence a row
> level locking is suitable vs table or database level locking.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Simon Slavin]

On 3 Nov 2013, at 1:24pm, Raheel Gupta  wrote:

> In order to avoid this, I had to use journal_mode=wal so that two threads
> dont have to wait when they both are doing SELECTs which might be taking
> 3-5 seconds to process.

I assume you have designed your indexes specifically for your WHERE and ORDER 
BY clauses.  And that you have set an appropriate SQLite time-out so you don't 
have to handle time-outs in your own programming.

> I was wondering if Row Level Locking would be introduced in
> journal_mode=delete as its there in InnoDB for MySQL. Atleast for selects
> or inserts Row Level rocking should be possible as neither modify the
> existing rows.

It /might/ be possible to introduce table-level locking, but row-level locking 
is actually very complicated.  Not only do you have to lock that row of the 
table data, you have to lock that chunk in all the indexes.  You also have to 
make sure that no operation makes changes to any of the indexes which might 
cause that row to be skipped or duplicated in a scan, which means that locking 
a row really means locking a range of rows in certain indexes.  There are also 
opportunities for deadlock which need to be dealt with, introducing lots of 
extra programming.

SQLite gets a lot of its speed because it doesn't have to do complicated 
checking whenever it does things, it just tests to see whether the entire 
database is locked.  Having to continually check for locking on each row would 
make it far slower.  There's a good chance that having to check for locking in 
every _step() rather than just once per SELECT would actually give you slower 
results than you currently have.

> journal_mode=wal is a little slower and has its own limitations over NFS.

NFS access is not suitable for SQLite because locking under NFS is 
untrustworthy.  Please see



If you really need concurrent multi-computer access then your project may be 
more suited to a DBMS with server/client architecture.  Those are inherently 
better for NFS because they don't use the NFS for database transactions: the 
database is actually accessed only from software running on a computer which 
accesses the file locally.  See the last section of



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

[sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

Hi,

I have been using SQLite for one project of mine and I will be storing TBs
of Data.
Now there will be a lot of selections in this database and I have come
across one problem with SQLite.
In journal_mode=delete the selection is database locked.
When one thread does a "TRANSACTION" on the database and soon after another
thread does "SELECT" on the database (using the same connection) or vice
versa, the second thread has to wait till the first thread finishes.

In order to avoid this, I had to use journal_mode=wal so that two threads
dont have to wait when they both are doing SELECTs which might be taking
3-5 seconds to process.

I was wondering if Row Level Locking would be introduced in
journal_mode=delete as its there in InnoDB for MySQL. Atleast for selects
or inserts Row Level rocking should be possible as neither modify the
existing rows.

journal_mode=wal is a little slower and has its own limitations over NFS.

OR if there is a mode equivalent to innodb in SQLITE please do let me know.
I need to do a lot of selects and inserts in my application and hence a row
level locking is suitable vs table or database level locking.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users