Re: [sqlite] WAL and multiple writers?

[Lynton Grice]

Hi there,

Well I'm sure I was, all I did was add a "busy handler" and it seems to work
100% now ;-)

Lynton

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
Sent: 16 November 2010 06:09 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WAL and multiple writers?

Are you sure that you are not keeping a transaction in the message sender?
___
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] WAL and multiple writers?

[Sylvain Pointeau]

Are you sure that you are not keeping a transaction in the message sender?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL and multiple writers?

[Lynton Grice]

Hi Richard,

So just for my understanding, let's say I have a WAL enabled database and I
have a "queue sender" application that just constantly sends and sends
messages, are you saying that a "queue receiver" cannot "receive and delete
the message" from the same DB until the sender releases that whole DB file
after sending?

>From what I understand below it should be possible but they will "share" the
processing on "whoever gets the lock" at that time?

I have a problem that while my SENDER is sending to a queue, for example, a
small snippet:

20101115140513  Message sent successfully!...79
20101115140513  Message sent successfully!...80
20101115140513  Message sent successfully!...81
20101115140513  Message sent successfully!...82
20101115140513  Message sent successfully!...83
20101115140513  Message sent successfully!...84
20101115140513  Message sent successfully!...85

The RECEIVER running at the same time (just a snippet below) can READ but
cannot DELETE the messages

Received IDoc: 1234567890  276
Could not delete...:1401
Received IDoc: 1234567890  277
Could not delete...:1401
Received IDoc: 1234567890  278
Could not delete...:1401
Received IDoc: 1234567890  279
Could not delete...:1401
Received IDoc: 1234567890  280
Could not delete...:1401
Received IDoc: 1234567890  281

This doesn't sound right does it?

Thanks

Lynton

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: 15 November 2010 02:05 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] WAL and multiple writers?

On Mon, Nov 15, 2010 at 5:40 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 15 Nov 2010, at 10:13am, Lynton Grice wrote:
>
> > Any ideas on having 2 separate applications accessing the same WAL
> enabled
> > database?
> >
> > Or does the first app get an entire lock?
>
> All SQLite locking is locking of the entire database.  SQLite does not
lock
> rows, or ranges, or anything like that.  Don't try to manipulate the
locking
> system, let SQLite do what it wants to do.  Use transactions and COMMIT
them
> as soon as possible.
>

To clarify:  SQLite locks the entire database file - but only for the
duration of each write operation.  So two or more applications can access
the database - they simply have to take turns.


>
> > Can I perhaps set the THREAD mode
> > to help here?
>
> What is it that isn't working as planned for you ?
>
> > I have tried:
> >
> >   rc = sqlite3_exec(handle,"PRAGMA wal_checkpoint",0,0,0);
> >rc = sqlite3_exec(handle,"PRAGMA synchronous=normal",0,0,0);
> >rc = sqlite3_exec(handle,"PRAGMA temp_store=memory",0,0,0);
> >
> > And obviously the following as well: "PRAGMA journal_mode=wal"
>
> Try /just/ the "PRAGMA journal_mode = WAL".  Stick with just this PRAGMA
> until you get it working in some shape or form.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] WAL and multiple writers?

[Richard Hipp]

On Mon, Nov 15, 2010 at 5:40 AM, Simon Slavin  wrote:

>
> On 15 Nov 2010, at 10:13am, Lynton Grice wrote:
>
> > Any ideas on having 2 separate applications accessing the same WAL
> enabled
> > database?
> >
> > Or does the first app get an entire lock?
>
> All SQLite locking is locking of the entire database.  SQLite does not lock
> rows, or ranges, or anything like that.  Don't try to manipulate the locking
> system, let SQLite do what it wants to do.  Use transactions and COMMIT them
> as soon as possible.
>

To clarify:  SQLite locks the entire database file - but only for the
duration of each write operation.  So two or more applications can access
the database - they simply have to take turns.


>
> > Can I perhaps set the THREAD mode
> > to help here?
>
> What is it that isn't working as planned for you ?
>
> > I have tried:
> >
> >   rc = sqlite3_exec(handle,"PRAGMA wal_checkpoint",0,0,0);
> >rc = sqlite3_exec(handle,"PRAGMA synchronous=normal",0,0,0);
> >rc = sqlite3_exec(handle,"PRAGMA temp_store=memory",0,0,0);
> >
> > And obviously the following as well: "PRAGMA journal_mode=wal"
>
> Try /just/ the "PRAGMA journal_mode = WAL".  Stick with just this PRAGMA
> until you get it working in some shape or form.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] WAL and multiple writers?

[Simon Slavin]

On 15 Nov 2010, at 10:13am, Lynton Grice wrote:

> Any ideas on having 2 separate applications accessing the same WAL enabled
> database?
> 
> Or does the first app get an entire lock?

All SQLite locking is locking of the entire database.  SQLite does not lock 
rows, or ranges, or anything like that.  Don't try to manipulate the locking 
system, let SQLite do what it wants to do.  Use transactions and COMMIT them as 
soon as possible.

> Can I perhaps set the THREAD mode
> to help here?

What is it that isn't working as planned for you ?

> I have tried:
> 
>   rc = sqlite3_exec(handle,"PRAGMA wal_checkpoint",0,0,0);
>rc = sqlite3_exec(handle,"PRAGMA synchronous=normal",0,0,0);
>rc = sqlite3_exec(handle,"PRAGMA temp_store=memory",0,0,0);
> 
> And obviously the following as well: "PRAGMA journal_mode=wal"

Try /just/ the "PRAGMA journal_mode = WAL".  Stick with just this PRAGMA until 
you get it working in some shape or form.

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

Re: [sqlite] WAL and multiple writers?

[Lynton Grice]

Hi all,

Any ideas on having 2 separate applications accessing the same WAL enabled
database?

Or does the first app get an entire lock? Can I perhaps set the THREAD mode
to help here?

I have tried:

rc = sqlite3_exec(handle,"PRAGMA wal_checkpoint",0,0,0);
rc = sqlite3_exec(handle,"PRAGMA synchronous=normal",0,0,0);
rc = sqlite3_exec(handle,"PRAGMA temp_store=memory",0,0,0);

And obviously the following as well: "PRAGMA journal_mode=wal"

I have also tried "sqlite3_config(SQLITE_CONFIG_MULTITHREAD);"

Any ideas?

Thanks

Lynton

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Lynton Grice
Sent: 14 November 2010 05:12 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] WAL and multiple writers?
Importance: High

Hi there,

 

I have a "queue" implemented in C using SQLIte in WAL mode.

 

When I send messages to the queueit is fine, and I can receive messages
from the queue no problem.

 

BUT when I try run BOTH applications at the same time the sender works 100%
and the receiver seems to be able to read the messages but not DELETE them
as they come in.

 

Am I doing something stupid here or is WAL not going to work for this Queue
implementation whereby I need two (or more) applications to be able to SEND
and RECEIVE at the same time?

 

Thanks for the help ;-)

 

Lynton

___
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] WAL and multiple writers?

[Lynton Grice]

Hi there,

 

I have a "queue" implemented in C using SQLIte in WAL mode.

 

When I send messages to the queueit is fine, and I can receive messages
from the queue no problem.

 

BUT when I try run BOTH applications at the same time the sender works 100%
and the receiver seems to be able to read the messages but not DELETE them
as they come in.

 

Am I doing something stupid here or is WAL not going to work for this Queue
implementation whereby I need two (or more) applications to be able to SEND
and RECEIVE at the same time?

 

Thanks for the help ;-)

 

Lynton

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