Re: [sqlite] set of db connections

[Simon Slavin]

On 17 Jul 2012, at 9:04am, Durga D  wrote:

> Hi Simon, what you suggest now? sigle object based or set of connections?

Sorry, I have no experience with what you're doing.  I hope someone else can 
advise you or you can try out a solution and tell whether it does what you want.

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

Re: [sqlite] set of db connections

[Durga D]

Hi Simon, what you suggest now? sigle object based or set of connections?

Thanks,


On Mon, Jul 16, 2012 at 6:11 PM, Simon Slavin  wrote:

>
> On 16 Jul 2012, at 2:59pm, "Igor Tandetnik"  wrote:
>
> > Durga D  wrote:
> >>   So, I can establish x connections in app initialize time in WAL mode.
> >> Based on request (read or write), I can pick the connection and serve.
> >> Allows only one write at a time.
> >
> > I'm not sure what you mean by picking a connection based on a request.
> It's not connections that are readers or writers - transactions are. A
> connection may execute a read-only transaction, then later a write
> transaction, then a read transaction again, and so on.
> >
> > You cannot have two write transactions (on two separate connections)
> active at the same point in time - one of them will block the other.
>
> Igor forgot to explain that WAL mode affects the database file.  You don't
> put a connection into WAL mode, something is stored with the database file
> which means that everything that talks to it will automatically use WAL
> mode.
>
> So you'll probably do something like this:
>
> Create a blank database file
> Put it into WAL mode using "PRAGMA journal_mode=WAL" (you can do this at
> any time, even after data is written)
> Make some tables (and put some data in if you want)
> Close the file
>
> Then you can write your apps to do whatever accessing you want.  Each app
> would open one connection to the database, and use that connection for
> whatever requests you want, in any order you want.  Each request gets its
> own 'handle' and stores its own command and its own results.
>
> 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] set of db connections

[Simon Slavin]

On 16 Jul 2012, at 2:59pm, "Igor Tandetnik"  wrote:

> Durga D  wrote:
>>   So, I can establish x connections in app initialize time in WAL mode.
>> Based on request (read or write), I can pick the connection and serve.
>> Allows only one write at a time.
> 
> I'm not sure what you mean by picking a connection based on a request. It's 
> not connections that are readers or writers - transactions are. A connection 
> may execute a read-only transaction, then later a write transaction, then a 
> read transaction again, and so on.
> 
> You cannot have two write transactions (on two separate connections) active 
> at the same point in time - one of them will block the other.

Igor forgot to explain that WAL mode affects the database file.  You don't put 
a connection into WAL mode, something is stored with the database file which 
means that everything that talks to it will automatically use WAL mode.

So you'll probably do something like this:

Create a blank database file
Put it into WAL mode using "PRAGMA journal_mode=WAL" (you can do this at any 
time, even after data is written)
Make some tables (and put some data in if you want)
Close the file

Then you can write your apps to do whatever accessing you want.  Each app would 
open one connection to the database, and use that connection for whatever 
requests you want, in any order you want.  Each request gets its own 'handle' 
and stores its own command and its own results.

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

Re: [sqlite] set of db connections

[Igor Tandetnik]

Durga D  wrote:
>So, I can establish x connections in app initialize time in WAL mode.
> Based on request (read or write), I can pick the connection and serve.
> Allows only one write at a time.

I'm not sure what you mean by picking a connection based on a request. It's not 
connections that are readers or writers - transactions are. A connection may 
execute a read-only transaction, then later a write transaction, then a read 
transaction again, and so on.

You cannot have two write transactions (on two separate connections) active at 
the same point in time - one of them will block the other.
-- 
Igor Tandetnik

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

Re: [sqlite] set of db connections

[Durga D]

Hi Michael/Igor,

So, I can establish x connections in app initialize time in WAL mode.
Based on request (read or write), I can pick the connection and serve.
Allows only one write at a time.

Is it correct?

Thanks in advance.

Regards,

On Mon, Jul 16, 2012 at 4:43 PM, Igor Tandetnik  wrote:

> Black, Michael (IS)  wrote:
> > Ummmare we forgetting about WAL mode?
> >
> > http://www.sqlite.org/draft/wal.html
> >
> > "Reading and writing can proceed concurrently."
> >
> > Not that you can have multiples of each...just one of each.
>
> To be precise, with WAL you can have one writer and multiple readers at
> any point in time. Without WAL, it's one writer OR multiple readers.
> --
> Igor Tandetnik
>
> ___
> 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] set of db connections

[Igor Tandetnik]

Black, Michael (IS)  wrote:
> Ummmare we forgetting about WAL mode?
> 
> http://www.sqlite.org/draft/wal.html
> 
> "Reading and writing can proceed concurrently."
> 
> Not that you can have multiples of each...just one of each.

To be precise, with WAL you can have one writer and multiple readers at any 
point in time. Without WAL, it's one writer OR multiple readers.
-- 
Igor Tandetnik

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

Re: [sqlite] set of db connections

[Black, Michael (IS)]

Ummmare we forgetting about WAL mode?

http://www.sqlite.org/draft/wal.html



"Reading and writing can proceed concurrently."



Not that you can have multiples of each...just one of each.







Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Monday, July 16, 2012 6:24 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] set of db connections


On 16 Jul 2012, at 12:09pm, Durga D <durga.d...@gmail.com> wrote:

>scenario: while write request is in progress with 100K records
> insertion, new request with reading of 1 records already existing
> records. Here, read request should wait till write request completes. Is it
> correct?

You are correct.  In order to make the insertion of 100K records a single write 
request you will, of course, make it one transaction by enclosing it in BEGIN 
... COMMIT.

When one request (even one which reads and does not write) is in progress 
SQLite locks the entire database.  The read request will automatically know 
that it cannot execute until the write request is finished.  This will prevent 
any other requests from executing.  You should set a timeout value of a few 
seconds using

<http://www.sqlite.org/c3ref/busy_timeout.html>

to prevent the second request from simply returning a SQLITE_BUSY or 
SQLITE_LOCKED result.  With a timeout set, the read request will wait until the 
write request is finished (or the timeout has expired), and then execute.

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] set of db connections

[Durga D]

Got it. Thank you so much.

On Mon, Jul 16, 2012 at 3:24 PM, Simon Slavin  wrote:

>
> On 16 Jul 2012, at 12:09pm, Durga D  wrote:
>
> >scenario: while write request is in progress with 100K records
> > insertion, new request with reading of 1 records already existing
> > records. Here, read request should wait till write request completes. Is
> it
> > correct?
>
> You are correct.  In order to make the insertion of 100K records a single
> write request you will, of course, make it one transaction by enclosing it
> in BEGIN ... COMMIT.
>
> When one request (even one which reads and does not write) is in progress
> SQLite locks the entire database.  The read request will automatically know
> that it cannot execute until the write request is finished.  This will
> prevent any other requests from executing.  You should set a timeout value
> of a few seconds using
>
> 
>
> to prevent the second request from simply returning a SQLITE_BUSY or
> SQLITE_LOCKED result.  With a timeout set, the read request will wait until
> the write request is finished (or the timeout has expired), and then
> execute.
>
> 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] set of db connections

[Simon Slavin]

On 16 Jul 2012, at 12:09pm, Durga D  wrote:

>scenario: while write request is in progress with 100K records
> insertion, new request with reading of 1 records already existing
> records. Here, read request should wait till write request completes. Is it
> correct?

You are correct.  In order to make the insertion of 100K records a single write 
request you will, of course, make it one transaction by enclosing it in BEGIN 
... COMMIT.

When one request (even one which reads and does not write) is in progress 
SQLite locks the entire database.  The read request will automatically know 
that it cannot execute until the write request is finished.  This will prevent 
any other requests from executing.  You should set a timeout value of a few 
seconds using



to prevent the second request from simply returning a SQLITE_BUSY or 
SQLITE_LOCKED result.  With a timeout set, the read request will wait until the 
write request is finished (or the timeout has expired), and then execute.

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

Re: [sqlite] set of db connections

[Durga D]

Hi Simon,

I agree with one connection logic.

scenario: while write request is in progress with 100K records
insertion, new request with reading of 1 records already existing
records. Here, read request should wait till write request completes. Is it
correct?

Thanks in advance.

Regards,

On Mon, Jul 16, 2012 at 2:29 PM, Simon Slavin  wrote:

>
> On 16 Jul 2012, at 7:50am, Durga D  wrote:
>
> >I am developing readers (> 1) and writer(1) application for sqlite3
> db.
> >
> >I would like to maintain set of connections in application
> > initialization time. whenever read request comes, serve the request from
> > existing connection object like pool.
>
> You can keep one database connection open per application, for the entire
> time that your application runs.  This will work correctly.
>
> The API will only service one request at a time.  You can prepare a number
> of statements (for example, a number of "SELECT" commands) but SQLite is
> single-threaded and locks the entire database when working.  So it will
> work only on one command at a time.  So there's no point in doing clever
> things with multiprocessing and background handling of many commands
> because the SQLite API will still be your bottleneck.
>
> >Here, my doubt is: if app. runs for a long time ( one week), will
> > connection maintain latest state of db?
>
> SQLite correctly handles many different apps, users and computers
> accessing the database at once.  A change made by one app on one computer
> will be instantly noticed by a different app on a different computer.
>
> 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] set of db connections

[Simon Slavin]

On 16 Jul 2012, at 7:50am, Durga D  wrote:

>I am developing readers (> 1) and writer(1) application for sqlite3 db.
> 
>I would like to maintain set of connections in application
> initialization time. whenever read request comes, serve the request from
> existing connection object like pool.

You can keep one database connection open per application, for the entire time 
that your application runs.  This will work correctly.

The API will only service one request at a time.  You can prepare a number of 
statements (for example, a number of "SELECT" commands) but SQLite is 
single-threaded and locks the entire database when working.  So it will work 
only on one command at a time.  So there's no point in doing clever things with 
multiprocessing and background handling of many commands because the SQLite API 
will still be your bottleneck.

>Here, my doubt is: if app. runs for a long time ( one week), will
> connection maintain latest state of db?

SQLite correctly handles many different apps, users and computers accessing the 
database at once.  A change made by one app on one computer will be instantly 
noticed by a different app on a different computer.

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

[sqlite] set of db connections

[Durga D]

Hi all,

I am developing readers (> 1) and writer(1) application for sqlite3 db.

I would like to maintain set of connections in application
initialization time. whenever read request comes, serve the request from
existing connection object like pool.

Here, my doubt is: if app. runs for a long time ( one week), will
connection maintain latest state of db?

Is it correct approach?

Thanks in advance.

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