Re: [sqlite] Concurrency Question

[Dominique Devienne]

On Sat, Nov 23, 2019 at 4:17 PM Dan Kennedy  wrote:

> > [...] Why is thread_B blocked when doing a read just because SQLite is
> writing to another table? [...]
> > Is this the expected behavior or am I doing something stupid in my code.
> And if so, what to check?
>
> This should only happen if you are using shared-cache mode. Don't use
> shared-cache mode.
>

But I'm forced to use shared-cache for multiple connections to an in-memory
database [1].

This is an important use-case IMHO, and the fact in-memory DBs can't use
WAL-mode,
and benefit from the added concurrency in the face of updates, is a real
bummer IMHO.

[1] https://www.sqlite.org/sharedcache.html#inmemsharedcache
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency Question

[Dan Kennedy]

On 24/11/62 00:05, Jens Alfke wrote:

On Nov 23, 2019, at 7:17 AM, Dan Kennedy  wrote:

This should only happen if you are using shared-cache mode. Don't use 
shared-cache mode.

Shared-cache mode also breaks Isolation between connections — during a 
transaction, other connections will see the writer’s intermediate state. (IIRC. 
It’s been a few years.)



Only if you explicitly set "PRAGMA read_uncommitted" I think.

Dan.




In my experience, it’s only useful if all connections are read-only, or if 
you’re willing to use your own mutexes to keep writers from screwing up readers 
(in which case you might as well just share a single connection, right?)

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

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

Re: [sqlite] Concurrency Question

[Mario M. Westphal]

 

That's very helpful, thank you very much, Daniel. Also to Jens.

 

I've re-read the SQLite documentation for shared cache now. I guess I had
enabled it in the past to increase the performance (many threads in my
application).
Apparently, this had the opposite effect :-/

 

After disabling the shared cache mode, the locks no longer happen. As I had
initially anticipated, the update and the read now run happily in parallel.

 

Thanks again. On a weekend, even!

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

Re: [sqlite] Concurrency Question

[Jens Alfke]

> On Nov 23, 2019, at 7:17 AM, Dan Kennedy  wrote:
> 
> This should only happen if you are using shared-cache mode. Don't use 
> shared-cache mode.

Shared-cache mode also breaks Isolation between connections — during a 
transaction, other connections will see the writer’s intermediate state. (IIRC. 
It’s been a few years.)

In my experience, it’s only useful if all connections are read-only, or if 
you’re willing to use your own mutexes to keep writers from screwing up readers 
(in which case you might as well just share a single connection, right?)

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

Re: [sqlite] Concurrency Question

[Dan Kennedy]

On 23/11/62 17:02, Mario M. Westphal wrote:

I have an issue with concurrency. This may be the intended behavior of
SQLite. Or I'm doing something wrong.

If somebody could shed a light, I would be thankful.

  


I compile and use SQLite on Windows with VStudio.

I compile it with SQLITE_THREADSAFE=1

At runtime, I use sqlite3_open_v2 () and set the flag SQLITE_OPEN_NOMUTEX.

I use SQLite in WAL mode.

My application uses several threads. Each thread opens its own database
connection.

  


Two threads run in parallel.

Thread A does a lengthy UPDATE to table_A (prepared statement). This update
takes, say, 5 seconds.

Thread B uses a prepared statement to SELECT data from an unrelated table_B.

Thread_B is blocked seconds in sqlite3Step because lockBtreeMutex() blocks
in a mutex.

  


I did not expect this.

Why is thread_B blocked when doing a read just because SQLite is writing to
another table?

  


Is this the expected behavior or am I doing something stupid in my code. And
if so, what to check?


This should only happen if you are using shared-cache mode. Don't use 
shared-cache mode.


Dan.



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

[sqlite] Concurrency Question

[Mario M. Westphal]

I have an issue with concurrency. This may be the intended behavior of
SQLite. Or I'm doing something wrong.

If somebody could shed a light, I would be thankful.

 

I compile and use SQLite on Windows with VStudio.

I compile it with SQLITE_THREADSAFE=1

At runtime, I use sqlite3_open_v2 () and set the flag SQLITE_OPEN_NOMUTEX.

I use SQLite in WAL mode.

My application uses several threads. Each thread opens its own database
connection.

 

Two threads run in parallel.

Thread A does a lengthy UPDATE to table_A (prepared statement). This update
takes, say, 5 seconds.

Thread B uses a prepared statement to SELECT data from an unrelated table_B.

Thread_B is blocked seconds in sqlite3Step because lockBtreeMutex() blocks
in a mutex.

 

I did not expect this.

Why is thread_B blocked when doing a read just because SQLite is writing to
another table?

 

Is this the expected behavior or am I doing something stupid in my code. And
if so, what to check?

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

[sqlite] concurrency acces from desktop and web service

[vlopz]

Hello everyone!

I´m developing an app with 3 parts: 
- JavaFX Desktop app.
- Java Server WebApp
- AndroidApp

I´m using Hibernate for mapping a SQLite Database.

But when the desktop app is open and try to insert a new ibject from the
AndroidApp throug the Server it gives me an error: `java.sql.SQLException:
database is locked`
  
My hibernate.cfg.xml file:

 

true
true
dialect.SQLiteDialect
org.sqlite.JDBC
jdbc:sqlite:grainsa_provisional.sqlite




And my "Objects Manager",the same way in the Server and in the Desktop  by
example:

private Session mSession;
private Transaction mTransaction;

private void initQuery() throws HibernateException {
mSession = HibernateUtil.getSessionFactory().openSession();
mTransaction = mSession.beginTransaction();
}

private void manejaExcepcion(HibernateException hibernateException) {
mTransaction.rollback();
throw new HibernateException("ha ocurrido un error con la Base 
de
Datos!!!", hibernateException);
}


public Conductor selectConductorByID(Integer id) {
Conductor conductor = new Conductor();
try{
initQuery();
conductor = (Conductor) mSession.get(Conductor.class, 
id);
} catch (HibernateException e){
manejaExcepcion(e);
throw e;
} finally {
mSession.close();
}
return conductor;
}


 If you need more information please ask!

What i´m doing wrong?I close everytime the sesion with mSession.close()...

Thanks everyone and sorry about my english!


Edit: ím thinking to change the acces mode of mi desktop JavaFX app to make
the query through the server, but it will take me alot of time, and i do not
think that is the best way to do it..

Please help! and thanks again!



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/concurrency-acces-from-desktop-and-web-service-tp74798.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Concurrency and sqlite4

[deltagam...@gmx.net]

Hello,

will there be some improvement regarding concurrency in the new sqlite4 ?

Sqlite3 fits quite good to my needs, only the concurrency causes some 
problems, so I am eager to know about improvements in this area.


Thx for your efforts

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

Re: [sqlite] Concurrency

[Simon]

On Mon, Apr 30, 2012 at 8:35 PM, Sean Cui  wrote:
>
> Thanks very much for the replies.
>
> It'd be nice to have the online documentation of WAL mode updated to include 
> the clarification of readers' visibility on uncommitted data.
>
> I think lot of developers will have the same question after reading the 
> current doc of "...readers do not block writers and a writer does not block 
> readers. Reading and writing can proceed concurrently."
>

I had thought the docs on WAL to be pretty clear a while ago and this
thread puzzled me.  So I went back and read the first paragraph in the
"Concurrency" section of that page.

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

Concurrency

When a read operation begins on a WAL-mode database, it first
remembers the location of the last valid commit record in the WAL.
Call this point the "end mark". Because the WAL can be growing and
adding new commit records while various readers connect to the
database, each reader can potentially have its own end mark. But for
any particular reader, the end mark is unchanged for the duration of
the transaction,

--> thus ensuring that a single read transaction only sees the
database content as it existed at a single point in time.

-

Richard Hipp:
  You asked how to improve the documentation...   I would modify the
Pros#2 to contain some of the explanation in the last sentence I
highlighted in the paragraph above.  Since Pros#2 seems to stress WAL
is a solution to "blocking", I would just add what the beauty of not
blocking is like.  Oh and I would move it to #1 since I guess "faster
in most scenarios" depends if there is blocking involved or not (among
other "scenarios").

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

Re: [sqlite] Concurrency

[Sean Cui]

Thanks very much for the replies.

It'd be nice to have the online documentation of WAL mode updated to include 
the clarification of readers' visibility on uncommitted data.

I think lot of developers will have the same question after reading the current 
doc of "...readers do not block writers and a writer does not block readers. 
Reading and writing can proceed concurrently." 


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: April-30-12 5:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Concurrency

On Mon, Apr 30, 2012 at 8:10 PM, Sean Cui <s...@gemcomsoftware.com> wrote:

> Here is a simple scenario to explain what the OP meant:
>
> Under WAL mode,
>
> In connection A, we issue SQL "UPDATE Employee SET Salary=0" to SQLite.
>
> While the command is executing, from another connection, we issue 
> "SELECT Salary FROM Employee".
>

When you say "while the command [UPDATE] command is executing", I assume you 
mean *before* it commits.  In that case, the SELECT statement will see a 
snapshot of the database file as it existed before the UPDATE was started.

Even if the UPDATE commits a second or two later, and the SELECT continues 
running for hours, the SELECT will still only continue to see the snapshot of 
the database as it existed prior to the start of the UPDATE, since that was the 
state of the database when the SELECT was started.

You can have multiple concurrent SELECT statements running, and if they were 
all started at different times (with database changes in between) then each 
will see a different snapshot of the database file - a full and complete image 
of the database file exactly as it existed when the SELECT was first started.



>
> In this case, even under WAL, the SELECT command will still be blocked 
> by the UPDATE command, right?
>
>
> Thanks,
>
> Sean
>
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Harnek Manj
> Sent: April-30-12 5:04 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Concurrency
>
> Hi Igor,
>
> Sorry about that. What I meant was "really latest committed data".
>
> As you answered that "It will read most recent", so as per the 
> scenario I mentioned in my previous post does it mean that read will 
> wait for the current write operation to finish?
>
> Regards
> Harnek
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: April-30-12 4:56 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Concurrency
>
> On 4/30/2012 7:46 PM, Harnek Manj wrote:
> > So what I am confused is, if there is a Write (update) operation and 
> > it
> creates a WAL file and subsequently a read operation comes in and the 
> read is trying to access the same data what write is working on. Does 
> in this scenario read will wait or it will grab the data from the 
> original database file
>
> The latter - it will read the most recently committed data.
>
> > which is not really data?
>
> I don't understand this statement. What is it, if not data?
> --
> 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
>
> ___
> 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] Concurrency

[Gerry Snyder]

On 4/30/2012 5:10 PM, Sean Cui wrote:

Here is a simple scenario to explain what the OP meant:

Under WAL mode,

In connection A, we issue SQL "UPDATE Employee SET Salary=0" to SQLite.

While the command is executing, from another connection, we issue "SELECT Salary 
FROM Employee".

In this case, even under WAL, the SELECT command will still be blocked by the 
UPDATE command, right?



The document referred to several emails ago in this thread states:

The WAL approach inverts this. The original content is preserved in the 
database file and the changes are appended into a separate WAL file. A 
COMMIT  occurs when a 
special record indicating a commit is appended to the WAL. Thus a COMMIT 
can happen without ever writing to the original database, which _allows 
readers to continue operating from the original unaltered database while 
changes are simultaneously being committed into the WAL_. Multiple 
transactions can be appended to the end of a single WAL file.


(Emphasis mine)

Otherwise it would not be very concurrent, would it?

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

Re: [sqlite] Concurrency

[Igor Tandetnik]

On 4/30/2012 8:03 PM, Harnek Manj wrote:

Sorry about that. What I meant was "really latest committed data".

As you answered that "It will read most recent", so as per the scenario I 
mentioned in my previous post does it mean that read will wait for the current write 
operation to finish?


No. The current writer hasn't committed its changes yet. So the most 
recently committed data is the data as it existed before this writer has 
made any changes. Which is what the reader would see.

--
Igor Tandetnik

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

Re: [sqlite] Concurrency

[Richard Hipp]

On Mon, Apr 30, 2012 at 8:10 PM, Sean Cui <s...@gemcomsoftware.com> wrote:

> Here is a simple scenario to explain what the OP meant:
>
> Under WAL mode,
>
> In connection A, we issue SQL "UPDATE Employee SET Salary=0" to SQLite.
>
> While the command is executing, from another connection, we issue "SELECT
> Salary FROM Employee".
>

When you say "while the command [UPDATE] command is executing", I assume
you mean *before* it commits.  In that case, the SELECT statement will see
a snapshot of the database file as it existed before the UPDATE was started.

Even if the UPDATE commits a second or two later, and the SELECT continues
running for hours, the SELECT will still only continue to see the snapshot
of the database as it existed prior to the start of the UPDATE, since that
was the state of the database when the SELECT was started.

You can have multiple concurrent SELECT statements running, and if they
were all started at different times (with database changes in between) then
each will see a different snapshot of the database file - a full and
complete image of the database file exactly as it existed when the SELECT
was first started.



>
> In this case, even under WAL, the SELECT command will still be blocked by
> the UPDATE command, right?
>
>
> Thanks,
>
> Sean
>
>
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Harnek Manj
> Sent: April-30-12 5:04 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Concurrency
>
> Hi Igor,
>
> Sorry about that. What I meant was "really latest committed data".
>
> As you answered that "It will read most recent", so as per the scenario I
> mentioned in my previous post does it mean that read will wait for the
> current write operation to finish?
>
> Regards
> Harnek
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: April-30-12 4:56 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Concurrency
>
> On 4/30/2012 7:46 PM, Harnek Manj wrote:
> > So what I am confused is, if there is a Write (update) operation and it
> creates a WAL file and subsequently a read operation comes in and the read
> is trying to access the same data what write is working on. Does in this
> scenario read will wait or it will grab the data from the original database
> file
>
> The latter - it will read the most recently committed data.
>
> > which is not really data?
>
> I don't understand this statement. What is it, if not data?
> --
> 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
>
> ___
> 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] Concurrency

[Sean Cui]

Here is a simple scenario to explain what the OP meant:

Under WAL mode,

In connection A, we issue SQL "UPDATE Employee SET Salary=0" to SQLite.

While the command is executing, from another connection, we issue "SELECT 
Salary FROM Employee".

In this case, even under WAL, the SELECT command will still be blocked by the 
UPDATE command, right?

  
Thanks,

Sean
 



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Harnek Manj
Sent: April-30-12 5:04 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Concurrency

Hi Igor,

Sorry about that. What I meant was "really latest committed data". 

As you answered that "It will read most recent", so as per the scenario I 
mentioned in my previous post does it mean that read will wait for the current 
write operation to finish?

Regards
Harnek

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: April-30-12 4:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Concurrency

On 4/30/2012 7:46 PM, Harnek Manj wrote:
> So what I am confused is, if there is a Write (update) operation and it 
> creates a WAL file and subsequently a read operation comes in and the read is 
> trying to access the same data what write is working on. Does in this 
> scenario read will wait or it will grab the data from the original database 
> file

The latter - it will read the most recently committed data.

> which is not really data?

I don't understand this statement. What is it, if not data?
-- 
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

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

Re: [sqlite] Concurrency

[Harnek Manj]

Hi Igor,

Sorry about that. What I meant was "really latest committed data". 

As you answered that "It will read most recent", so as per the scenario I 
mentioned in my previous post does it mean that read will wait for the current 
write operation to finish?

Regards
Harnek

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: April-30-12 4:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Concurrency

On 4/30/2012 7:46 PM, Harnek Manj wrote:
> So what I am confused is, if there is a Write (update) operation and it 
> creates a WAL file and subsequently a read operation comes in and the read is 
> trying to access the same data what write is working on. Does in this 
> scenario read will wait or it will grab the data from the original database 
> file

The latter - it will read the most recently committed data.

> which is not really data?

I don't understand this statement. What is it, if not data?
-- 
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] Concurrency

[Igor Tandetnik]

On 4/30/2012 7:46 PM, Harnek Manj wrote:

So what I am confused is, if there is a Write (update) operation and it creates 
a WAL file and subsequently a read operation comes in and the read is trying to 
access the same data what write is working on. Does in this scenario read will 
wait or it will grab the data from the original database file


The latter - it will read the most recently committed data.


which is not really data?


I don't understand this statement. What is it, if not data?
--
Igor Tandetnik

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

Re: [sqlite] Concurrency

[Harnek Manj]

Hi Richard,

Thanks for the quick reply.

So what I am confused is, if there is a Write (update) operation and it creates 
a WAL file and subsequently a read operation comes in and the read is trying to 
access the same data what write is working on. Does in this scenario read will 
wait or it will grab the data from the original database file which is not 
really data?

Harnek

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: April-30-12 4:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Concurrency

On Mon, Apr 30, 2012 at 7:17 PM, Harnek Manj <hm...@gemcomsoftware.com>wrote:

> Hi,
>
> I am trying to make sqlite available for concurrency within a process 
> and multi-process.
>

WAL mode.

5. Read about WAL, it gives the concurrency. But I think it gives
> concurrency by  allowing dirty reads. Please correct me if I am wrong.
>

Isolation in WAL mode is SERIALIZABLE.  Please help me to improve the 
documentation for SQLite by telling me what you read in the documentation that 
lead you to believe that WAL mode gaves dirty reads?



--
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] Concurrency

[Richard Hipp]

On Mon, Apr 30, 2012 at 7:17 PM, Harnek Manj wrote:

> Hi,
>
> I am trying to make sqlite available for concurrency within a process and
> multi-process.
>

WAL mode.

5. Read about WAL, it gives the concurrency. But I think it gives
> concurrency by  allowing dirty reads. Please correct me if I am wrong.
>

Isolation in WAL mode is SERIALIZABLE.  Please help me to improve the
documentation for SQLite by telling me what you read in the documentation
that lead you to believe that WAL mode gaves dirty reads?



-- 
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] Concurrency

[Harnek Manj]

Hi,

I am trying to make sqlite available for concurrency within a process and 
multi-process.

1. Currently I am using the default Isolation level for Sqlite, which doesn't 
even allows me the multiple reads on the single table. What's the default 
isolation level and how can I change it? I tried to make the change in Begin 
Transaction statement but it threw an exception.

2. The other issue I am facing is that when I do a write long transaction on a 
table, all the read statements on other tables fail with the exception of "File 
Locked". How can I resolve this?

3. Did some search & read about Shared Cache, will this be the right choice for 
making Table Level Locking enabled to solve my previous issue?

4. If I have to enable Shared Cache, do I need to re-compile the code? I am 
using Sqlite driver with .net application.

5. Read about WAL, it gives the concurrency. But I think it gives concurrency 
by  allowing dirty reads. Please correct me if I am wrong.


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

Re: [sqlite] Concurrency violation

[Agrawal, Manish]

Thanks very much for your response. I tried a simple test based on this 
suggestion as follows:

CREATE TABLE "table1" ("field1" INTEGER PRIMARY KEY NOT NULL UNIQUE, "field2" 
INTEGER NOT NULL );
INSERT INTO "table1" VALUES(1, 1);
INSERT INTO "table1" VALUES(2, 2);
INSERT INTO "table1" VALUES(3, 3);
INSERT INTO "table1" VALUES(4, 4);
INSERT INTO "table1" VALUES(5, 5);

The update command generated is:

this._adapter.UpdateCommand.CommandText = "UPDATE 
[main].[sqlite_default_schema].[table1] SET [field1] = @field1, [field2] = 
@field2 WHERE 

(([field1] = @Original_field1) AND ([field2] = @Original_field2));"

Changing field2 to REAL or TEXT made no difference. The comparison is always 
with both fields.

This is just for information for solution if possible. Fortunately it only 
requires a one-time clean-up after the dataset is created.

Thanks
Manish

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Friday, March 30, 2012 4:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Concurrency violation: the UpdateCommand affected 0 of 
the expected 1 records


On 30 Mar 2012, at 6:27pm, "Agrawal, Manish" <magra...@usf.edu> wrote:

> Thanks very much. Most of our tables do have datetime fields.

Not in SQLite they don't.  There is no such datatype in SQLite.

<http://sqlite.org/datatype3.html>

Find out how you're storing your dates, and declare your fields as INTEGER, 
REAL or TEXT, then your problem will go away.

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] Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

[Simon Slavin]

On 30 Mar 2012, at 6:27pm, "Agrawal, Manish"  wrote:

> Thanks very much. Most of our tables do have datetime fields.

Not in SQLite they don't.  There is no such datatype in SQLite.



Find out how you're storing your dates, and declare your fields as INTEGER, 
REAL or TEXT, then your problem will go away.

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

Re: [sqlite] Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

[Agrawal, Manish]

Thanks very much. Most of our tables do have datetime fields.

Manish

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joe Mistachkin
Sent: Friday, March 30, 2012 9:45 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Concurrency violation: the UpdateCommand affected 0 of 
the expected 1 records


Agrawal, Manish wrote:
>
>   So my question is: is this a bug or a feature in System.Data.SQLite
>

The behavior seen here does not originate with System.Data.SQLite.  It has to 
do with how the .NET Framework System.Data.* infrastructure classes (e.g.
DbCommandBuilder) build the WHERE clause of the SQL query.

There have been several tickets submitted that involved this issue and I have 
researched the root causes of each.  In almost all cases, the issue ended up 
being that the System.Data.* infrastructure components in the .NET Framework 
are very sensitive to the exact data types declared in the schema (e.g.
INTEGER
vs. DOUBLE), even though SQLite itself is loosely typed.

System.Data.SQLite attempts to mitigate this impedance mismatch by mapping type 
names to .NET Framework types and/or to well-known SQL data types.
Normally, this works quite well; however, the use of the equality operator by 
the DbCommandBuilder class to build the WHERE clause seems to cause issues for 
some data types (e.g. DateTime and Double).  I have not yet found a good 
workaround for these situations since there appears to be no direct way to 
influence the WHERE clause generated by the DbCommandBuilder class without 
changing the underlying database schema.

--
Joe Mistachkin

___
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] Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

[Joe Mistachkin]

Agrawal, Manish wrote:
>
>   So my question is: is this a bug or a feature in System.Data.SQLite
>

The behavior seen here does not originate with System.Data.SQLite.  It has
to
do with how the .NET Framework System.Data.* infrastructure classes (e.g.
DbCommandBuilder) build the WHERE clause of the SQL query.

There have been several tickets submitted that involved this issue and I
have
researched the root causes of each.  In almost all cases, the issue ended up
being that the System.Data.* infrastructure components in the .NET Framework
are very sensitive to the exact data types declared in the schema (e.g.
INTEGER
vs. DOUBLE), even though SQLite itself is loosely typed.

System.Data.SQLite attempts to mitigate this impedance mismatch by mapping
type names to .NET Framework types and/or to well-known SQL data types.
Normally, this works quite well; however, the use of the equality operator
by
the DbCommandBuilder class to build the WHERE clause seems to cause issues
for
some data types (e.g. DateTime and Double).  I have not yet found a good
workaround for these situations since there appears to be no direct way to
influence the WHERE clause generated by the DbCommandBuilder class without
changing the underlying database schema.

--
Joe Mistachkin

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

Re: [sqlite] Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

[Agrawal, Manish]

Thanks

The connection was indeed getting closed. On further debugging, I found 
this is because the SQL created by the designer for the update command requires 
a matching of all fields in the WHERE clause. Below are some examples from the 
designer.cs file:

this._adapter.UpdateCommand.CommandText = @"UPDATE 
[main].[sqlite_default_schema].[Assignment] SET [Course_Id] = @Course_Id, 
[Name] = @Name, [Description] = @Description, [Pointspossible] = 
@Pointspossible, [Downloadpath] = @Downloadpath, [AssignedDate] = 
@AssignedDate, [DueDate] = @DueDate, [CreateDate] = @CreateDate WHERE 
(([ASSIGNMENT_ID] = @ORIGINAL_ASSIGNMENT_ID) AND ([COURSE_ID] = 
@ORIGINAL_COURSE_ID) AND ([NAME] = @ORIGINAL_NAME) AND ((@ISNULL_DESCRIPTION = 
1 AND [DESCRIPTION] IS NULL) OR ([DESCRIPTION] = @ORIGINAL_DESCRIPTION)) AND 
([POINTSPOSSIBLE] = @ORIGINAL_POINTSPOSSIBLE) AND ((@ISNULL_DOWNLOADPATH = 1 
AND [DOWNLOADPATH] IS NULL) OR ([DOWNLOADPATH] = @ORIGINAL_DOWNLOADPATH)) AND 
([ASSIGNEDDATE] = @ORIGINAL_ASSIGNEDDATE) AND ([DUEDATE] = @ORIGINAL_DUEDATE) 
AND ([CREATEDATE] = @ORIGINAL_CREATEDATE))";

this._adapter.UpdateCommand.CommandText = @"UPDATE 
[main].[sqlite_default_schema].[Course] SET [Name] = @Name, [Description] = 
@Description WHERE (([COURSE_ID] = @ORIGINAL_COURSE_ID) AND ([NAME] = 
@ORIGINAL_NAME) AND ((@ISNULL_DESCRIPTION = 1 AND [DESCRIPTION] IS NULL) OR 
([DESCRIPTION] = @ORIGINAL_DESCRIPTION)))";

When I removed the redundant matching columns, leaving only the primary 
key, the error went away as expected. I believe that the extra conditions 
generated in the UPDATE command are unusual.

So my question is: is this a bug or a feature in System.Data.SQLite

Thanks
Manish

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joe Mistachkin
Sent: Monday, March 26, 2012 2:47 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Concurrency violation: the UpdateCommand affected 0 of 
the expected 1 records


Agrawal, Manish wrote:
>
> 1. Database connection not valid for getting number of changes
>   At System.Data.SQLite.SQLiteConnection.get_Changes()
> 
> 2. Database connection not valid for getting last insert rowid
>   At System.Data.SQLite.SQLiteConnection.get_LastInsertRowId()
> 
> 3. Database connection not valid for getting maximum memory used
>   At System.Data.SQLite.SQLiteConnection.get_MemoryHighwater()
> 
> 4. Database connection not valid for getting memory used
>   At System.Data.SQLite.SQLiteConnection.get_MemoryUsed()
> 

Those exceptions indicate that the connection has been closed somehow.

--
Joe Mistachkin

___
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] Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

[Joe Mistachkin]

Agrawal, Manish wrote:
>
> 1. Database connection not valid for getting number of changes
>   At System.Data.SQLite.SQLiteConnection.get_Changes()
> 
> 2. Database connection not valid for getting last insert rowid
>   At System.Data.SQLite.SQLiteConnection.get_LastInsertRowId()
> 
> 3. Database connection not valid for getting maximum memory used
>   At System.Data.SQLite.SQLiteConnection.get_MemoryHighwater()
> 
> 4. Database connection not valid for getting memory used
>   At System.Data.SQLite.SQLiteConnection.get_MemoryUsed()
> 

Those exceptions indicate that the connection has been closed somehow.

--
Joe Mistachkin

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

Re: [sqlite] Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

[Agrawal, Manish]

Debugging shows that the update statement generates the following 4 errors:

http://www.ismlab.usf.edu/sqlite_errors.jpg

1. Database connection not valid for getting number of changes
At System.Data.SQLite.SQLiteConnection.get_Changes()

2. Database connection not valid for getting last insert rowid
At System.Data.SQLite.SQLiteConnection.get_LastInsertRowId()

3. Database connection not valid for getting maximum memory used
At System.Data.SQLite.SQLiteConnection.get_MemoryHighwater()

4. Database connection not valid for getting memory used
At System.Data.SQLite.SQLiteConnection.get_MemoryUsed()

Workarounds or solutions would be greatly appreciated.

Thanks
Manish

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Agrawal, Manish
Sent: Sunday, March 25, 2012 8:37 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Concurrency violation: the UpdateCommand affected 0 of the 
expected 1 records

Hello

I am getting this error when trying to update a database row in a C# 
application. The data set ds has been created using the standard Visual Studio/ 
System.Data.SQLite tools.

The table definition is:

CREATE TABLE "Test" (
"test_Id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , 
"Points" INTEGER NOT NULL,  
"File_Location" VARCHAR NOT NULL 
)

The C# code is:

int rowNumber = tBox.rowNumber; // TextBox
ds.Test[rowNumber].Points = (int)points;// from tbox
testAdapter.Update(ds.Test[rowNumber]); // ERROR THROWN HERE

Any help in resolving would be greatly appreciated.

Using Visual Studio ultimate 2010 and sqlite-1.0.79

Thanks
Manish
___
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] Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

[Agrawal, Manish]

Hello

I am getting this error when trying to update a database row in a C# 
application. The data set ds has been created using the standard Visual Studio/ 
System.Data.SQLite tools.

The table definition is:

CREATE TABLE "Test" (
"test_Id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , 
"Points" INTEGER NOT NULL,  
"File_Location" VARCHAR NOT NULL 
)

The C# code is:

int rowNumber = tBox.rowNumber; // TextBox
ds.Test[rowNumber].Points = (int)points;// from tbox
testAdapter.Update(ds.Test[rowNumber]); // ERROR THROWN HERE

Any help in resolving would be greatly appreciated.

Using Visual Studio ultimate 2010 and sqlite-1.0.79

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

Re: [sqlite] Concurrency Quesion

[Simon Slavin]

On 11 Oct 2011, at 4:51pm, Gal Waldman wrote:

> On Tue, Oct 11, 2011 at 17:30, Simon Slavin  wrote:
> 
>> How are you handling transactions ?  Are all the changes involved in one
>> update from one probe made inside one transaction ?  Does this involve
>> changing many rows ?
> 
> Single transaction for all probes.

This may be keeping the database locked for long enough to block out other 
processes.  If you change it to one transaction for each probe, the other 
processes may be able to sneak in between updates.

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

Re: [sqlite] Concurrency Quesion

[Gal Waldman]

On Tue, Oct 11, 2011 at 17:30, Simon Slavin  wrote:

>
> On 11 Oct 2011, at 4:01pm, Gal Waldman wrote:
>
> >> If you don't mind inconsistency due to changes not being included in the
> >> same transaction, it may be that doing faster changes but leaving a tiny
> gap
> >> between each one will allow your other processes to access the database
> >> without exceeding the busy-wait time.
> >
> > I guess the issue is data integrity vs Db logical scheme, I thought about
> > the external table as a bypass, but than again I need to implement the DB
> > scheme logic on the application level.
>
> How are you handling transactions ?  Are all the changes involved in one
> update from one probe made inside one transaction ?  Does this involve
> changing many rows ?
>

Single transaction for all probes.


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



-- 
Waldman Gal
Products Manager
--
Mobile  +972 522 558885
Fax  +972 3 6209277
Emailga...@tagvs.com
Skype  waldman_gal
Site  http://www.tagvs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency Quesion

[Simon Slavin]

On 11 Oct 2011, at 4:01pm, Gal Waldman wrote:

>> If you don't mind inconsistency due to changes not being included in the
>> same transaction, it may be that doing faster changes but leaving a tiny gap
>> between each one will allow your other processes to access the database
>> without exceeding the busy-wait time.
> 
> I guess the issue is data integrity vs Db logical scheme, I thought about
> the external table as a bypass, but than again I need to implement the DB
> scheme logic on the application level.

How are you handling transactions ?  Are all the changes involved in one update 
from one probe made inside one transaction ?  Does this involve changing many 
rows ?

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

Re: [sqlite] Concurrency Quesion

[Gal Waldman]

On Tue, Oct 11, 2011 at 16:39, Simon Slavin  wrote:

>
> On 11 Oct 2011, at 3:19pm, Gal Waldman wrote:
>
> >   - Currently we use a single DB and encounter SQLITE_BUSY more times
> than
> >   we can afford ( we want to let the monitor handle more probes )
>
> Okay, sorry, you didn't say you were actually getting a problem.  A lot of
> posts here come because people are doing premature optimisation.  I now
> understand you aren't.
>
> Have you tried changing the timeout value as described in
>
> http://www.sqlite.org/c3ref/busy_timeout.html


Using Busy_handler doesn't helps in my case ( I have already  implemented a
sleep with growing interval  ) as busy scenario itself is what I am trying
to prevent.


>
> (your API may not allow this) ?
>
> >   - Separating to two different DBs will enable us to separate the "slow"
> >   changing part from the "fast" changing part. but as in the FOREIGN KEY
> >   example we loose the logical consistency over the DB's
> >   - I understand the locking mechanism, the whole point of separating is
> >   to achieve the ability to write to the "fast" changing DB (statistics)
> >   without locking the "slow" DB
> >   - The Idea of using external table was to write the statistics to some
> >   ram file so configuration Db would scarcely get locked, If I can even
> >   prevent DB locking on write to external Table I don't mind loosing some
> >   DB consistency as it for statistics.
>
> Is there a way to prevent locking on external table write operation ?



> If you don't mind inconsistency due to changes not being included in the
> same transaction, it may be that doing faster changes but leaving a tiny gap
> between each one will allow your other processes to access the database
> without exceeding the busy-wait time.
>

I guess the issue is data integrity vs Db logical scheme, I thought about
the external table as a bypass, but than again I need to implement the DB
scheme logic on the application level.


>
> When you do your writing to the database are you using transactions ?  This
> considerably reduces the amount of time a database stays locked in total.
>  On the other hand it increases the total number of locks.  If you're doing
> one, and it makes sense within how your code works, try the other.
>
> We use transaction with ready made queries for optimization.


> I would also comment that TRIGGERs are slow, and leave the database locked
> for quite a long time in comparison with single changes.  Although you
> sacrifice some database integrity it might be better to move consistency
> from TRIGGERs to your programming language, so you can better control when
> the changes are made.
>

I guess we can drop the triggers although I need mechanism for row
level time-stamps I guess I can take it out from the DB

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



-- 
Waldman Gal
Products Manager
--
Mobile  +972 522 558885
Fax  +972 3 6209277
Emailga...@tagvs.com
Skype  waldman_gal
Site  http://www.tagvs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency Quesion

[Simon Slavin]

On 11 Oct 2011, at 3:19pm, Gal Waldman wrote:

>   - Currently we use a single DB and encounter SQLITE_BUSY more times than
>   we can afford ( we want to let the monitor handle more probes )

Okay, sorry, you didn't say you were actually getting a problem.  A lot of 
posts here come because people are doing premature optimisation.  I now 
understand you aren't.

Have you tried changing the timeout value as described in

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

(your API may not allow this) ?

>   - Separating to two different DBs will enable us to separate the "slow"
>   changing part from the "fast" changing part. but as in the FOREIGN KEY
>   example we loose the logical consistency over the DB's
>   - I understand the locking mechanism, the whole point of separating is
>   to achieve the ability to write to the "fast" changing DB (statistics)
>   without locking the "slow" DB
>   - The Idea of using external table was to write the statistics to some
>   ram file so configuration Db would scarcely get locked, If I can even
>   prevent DB locking on write to external Table I don't mind loosing some
>   DB consistency as it for statistics.

If you don't mind inconsistency due to changes not being included in the same 
transaction, it may be that doing faster changes but leaving a tiny gap between 
each one will allow your other processes to access the database without 
exceeding the busy-wait time.

When you do your writing to the database are you using transactions ?  This 
considerably reduces the amount of time a database stays locked in total.  On 
the other hand it increases the total number of locks.  If you're doing one, 
and it makes sense within how your code works, try the other.

I would also comment that TRIGGERs are slow, and leave the database locked for 
quite a long time in comparison with single changes.  Although you sacrifice 
some database integrity it might be better to move consistency from TRIGGERs to 
your programming language, so you can better control when the changes are made.

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

Re: [sqlite] Concurrency Quesion

[Gal Waldman]

Let me rephrase:


   - Currently we use a single DB and encounter SQLITE_BUSY more times than
   we can afford ( we want to let the monitor handle more probes )
   - Separating to two different DBs will enable us to separate the "slow"
   changing part from the "fast" changing part. but as in the FOREIGN KEY
   example we loose the logical consistency over the DB's
   - I understand the locking mechanism, the whole point of separating is
   to achieve the ability to write to the "fast" changing DB (statistics)
   without locking the "slow" DB
   - The Idea of using external table was to write the statistics to some
   ram file so configuration Db would scarcely get locked, If I can even
   prevent DB locking on write to external Table I don't mind loosing some
   DB consistency as it for statistics.



On Tue, Oct 11, 2011 at 13:56, Simon Slavin  wrote:

>
> On 11 Oct 2011, at 12:28pm, Gal Waldman wrote:
>
> > We do encounter locking problems on configuration changes ( currently it
> is
> > a single DB file )
> > For Statistic write operation optimization we collect all data and write
> in
> > a single transaction, we had to do this without any foreign key / trigger
> > dependencies, which create some DB scheme problems (theoretically)
>
> FOREIGN KEYs must involve TABLEs in the same database file.  Remember that
> any two files can be split up, and one opened on one computer and the other
> opened on another computer.  Since SQLite can have no strategy on what to do
> if a key table is missing, there's no point in allowing the situation to
> happen.
>
> > The problem with my suggestion is
> >
> >   - while the DB files are attached ( for example on the web server when
> >   need to supply a mix of configuration + statistics ) there might be a
> >   locking problem, so I need to attach detach based on specific scenario,
> >   wouldn't that have some overhead.
>
> SQLite locks only when writing is going on.  You can have as many apps
> reading the database as you like and nothing will get locked.  So keep your
> writing sessions as short as possible.
>
> I don't think you need to do any detaching.  If a database file is locked,
> the problems are no worse than if it is detached.  And there's an added
> advantage that once the writing process is finished, the database becomes
> available immediately without any extra work.  I don't think you are gaining
> anything by detaching a database just because another app has it locked.
>
> >   - If I wish to preserve logical correlation bewteen the DB files in
> need
> >   to keep the attachment all around which brings me back to the starting
> >   point.
>
> I doubt we're going to understand this unless we know why you want your
> data in separate files to begin with.
>
> > We also checked the option of external table usage, but I am not sure
> what
> > is locking scheme for external tables.
>
> I don't think there is any advantage to you but we would probably need to
> know more about why you think you need them.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Waldman Gal
Products Manager
--
Mobile  +972 522 558885
Fax  +972 3 6209277
Emailga...@tagvs.com
Skype  waldman_gal
Site  http://www.tagvs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency Quesion

[Simon Slavin]

On 11 Oct 2011, at 12:28pm, Gal Waldman wrote:

> We do encounter locking problems on configuration changes ( currently it is
> a single DB file )
> For Statistic write operation optimization we collect all data and write in
> a single transaction, we had to do this without any foreign key / trigger
> dependencies, which create some DB scheme problems (theoretically)

FOREIGN KEYs must involve TABLEs in the same database file.  Remember that any 
two files can be split up, and one opened on one computer and the other opened 
on another computer.  Since SQLite can have no strategy on what to do if a key 
table is missing, there's no point in allowing the situation to happen.

> The problem with my suggestion is
> 
>   - while the DB files are attached ( for example on the web server when
>   need to supply a mix of configuration + statistics ) there might be a
>   locking problem, so I need to attach detach based on specific scenario,
>   wouldn't that have some overhead.

SQLite locks only when writing is going on.  You can have as many apps reading 
the database as you like and nothing will get locked.  So keep your writing 
sessions as short as possible.

I don't think you need to do any detaching.  If a database file is locked, the 
problems are no worse than if it is detached.  And there's an added advantage 
that once the writing process is finished, the database becomes available 
immediately without any extra work.  I don't think you are gaining anything by 
detaching a database just because another app has it locked.

>   - If I wish to preserve logical correlation bewteen the DB files in need
>   to keep the attachment all around which brings me back to the starting
>   point.

I doubt we're going to understand this unless we know why you want your data in 
separate files to begin with.

> We also checked the option of external table usage, but I am not sure what
> is locking scheme for external tables.

I don't think there is any advantage to you but we would probably need to know 
more about why you think you need them.

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

Re: [sqlite] Concurrency Quesion

[Gal Waldman]

We do encounter locking problems on configuration changes ( currently it is
a single DB file )
For Statistic write operation optimization we collect all data and write in
a single transaction, we had to do this without any foreign key / trigger
dependencies, which create some DB scheme problems (theoretically)

The problem with my suggestion is

   - while the DB files are attached ( for example on the web server when
   need to supply a mix of configuration + statistics ) there might be a
   locking problem, so I need to attach detach based on specific scenario,
   wouldn't that have some overhead.
   - If I wish to preserve logical correlation bewteen the DB files in need
   to keep the attachment all around which brings me back to the starting
   point.

We also checked the option of external table usage, but I am not sure what
is locking scheme for external tables.


On Tue, Oct 11, 2011 at 12:02, Simon Slavin  wrote:

>
> On 11 Oct 2011, at 9:56am, Gal Waldman wrote:
>
> > I am looking for ideas/solutions for Concurrency in my application.
> > I have web-server for user configuration, where configuration changes ->
> > write operation is scarce, at the same time I have a single monitor
> > application which collect information from multiple probes which does
> lots
> > of statistics write operations.
>
> There's nothing about the above description which suggests it would fail.
>  My only comment would be that the app which does the writing has two
> possible strategies:
>
> (A) Collect data from all probes.  Write it all in one go.
> (B) Collect data from one probe, write that, move on to next probe.
>
> Whether each one one works for you depends on how much data is collected
> during each pass, and on several other things.  No way we can advise you on
> it and you're probably better just guessing, writing one of them, and seeing
> whether it fails.
>
> > I thought about separating to configuration / statistics DB but I
> understand
> > that while attach is in place both DB's would be locked. any other
> > suggestions ?
>
> Just don't do your stats calculations (or anything else time-consuming)
> while the database is locked.  So don't start a transaction, do one INSERT,
> then start doing calculations before you can finish the transactions.
>
> Do you have a problem you are asking us to help you solve ?  Are you
> actually getting errors because your apps are both trying to access the
> database at the same time ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Waldman Gal
Products Manager
--
Mobile  +972 522 558885
Fax  +972 3 6209277
Emailga...@tagvs.com
Skype  waldman_gal
Site  http://www.tagvs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency Quesion

[Simon Slavin]

On 11 Oct 2011, at 9:56am, Gal Waldman wrote:

> I am looking for ideas/solutions for Concurrency in my application.
> I have web-server for user configuration, where configuration changes ->
> write operation is scarce, at the same time I have a single monitor
> application which collect information from multiple probes which does lots
> of statistics write operations.

There's nothing about the above description which suggests it would fail.  My 
only comment would be that the app which does the writing has two possible 
strategies:

(A) Collect data from all probes.  Write it all in one go.
(B) Collect data from one probe, write that, move on to next probe.

Whether each one one works for you depends on how much data is collected during 
each pass, and on several other things.  No way we can advise you on it and 
you're probably better just guessing, writing one of them, and seeing whether 
it fails.

> I thought about separating to configuration / statistics DB but I understand
> that while attach is in place both DB's would be locked. any other
> suggestions ?

Just don't do your stats calculations (or anything else time-consuming) while 
the database is locked.  So don't start a transaction, do one INSERT, then 
start doing calculations before you can finish the transactions.

Do you have a problem you are asking us to help you solve ?  Are you actually 
getting errors because your apps are both trying to access the database at the 
same time ?

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

[sqlite] Concurrency Quesion

[Gal Waldman]

Hello,
I am looking for ideas/solutions for Concurrency in my application.
I have web-server for user configuration, where configuration changes ->
write operation is scarce, at the same time I have a single monitor
application which collect information from multiple probes which does lots
of statistics write operations.

I thought about separating to configuration / statistics DB but I understand
that while attach is in place both DB's would be locked. any other
suggestions ?

-- 
Waldman Gal
Products Manager
--
Mobile  +972 522 558885
Fax  +972 3 6209277
Emailga...@tagvs.com
Skype  waldman_gal
Site  http://www.tagvs.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Concurrency problem

[Yan Burman]

Hi.


I use sqlite in my code for a while now, and at first I parsed all queries each 
time, but now I'm migrating my code to using prepared statements (using 
sqlite3_prepare_v2
and binding parameters).
At some point I started receiving 'library routine called out of sequence' 
error every now and then while the application is running.
I run the code in multithreaded environment + once in a while the command line 
utility accesses the same database.
I use a single connection to the database and use a mutex to protect access to 
db.
I was using version 2.6.23.1 when the error occurred. Once I upgraded to 3.7.3 
the problem seem to have disappeared (without changing any of my code).
I went over my code and I could not find any problem (for example calling 
something without my mutex held).
Is it possible that I stumbled upon a bug in 2.6.23.1 that was fixed in 3.7.3, 
or do I still have a problem that is harder to reproduce?
I don't like problems that go away on their own.

Btw, I'm accessing the database in embedded linux environment from jffs2 
filesystem if that matters.
Also, I have a potential case where I may compile the same statement twice, but 
in theory this should not be a problem, since this is at most a one time 
resource leak.

I would really appreciate some help.

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

Re: [sqlite] Concurrency for in-memory scenarios

[Pavel Ivanov]

> Could you please explain what you meant in this line?
>> Of course I don't consider option of concurrency from
>> different processes.

I guess I meant that my above words are related to the case when you
access the database from the same process, and if one talks about
different processes it's a little bit different story.
But to be honest it's really hard to remember what I meant after 2 months... :)


Pavel

On Wed, Jun 9, 2010 at 7:04 AM, Navaneeth Sen B
 wrote:
> Hi Pavel,
> Could you please explain what you meant in this line?
>> Of course I don't consider option of concurrency from
>> different processes.
> Thanks & Regards,
> Sen
>
>
> On 4/6/2010 8:33 PM, Pavel Ivanov wrote:
>>> Can anyone confirm whether concurrent
>>> access to an in-memory database is supported?
>>>
>> No, SQLite doesn't support full concurrent access to any database. The
>> only concurrency you can earn is having on-disk database without
>> shared cache (so actually having several copies of the database in
>> memory). Of course I don't consider option of concurrency from
>> different processes.
>>
>>
>> Pavel
>>
>> On Tue, Apr 6, 2010 at 9:43 AM, Kent Boogaart  
>> wrote:
>>
>>>   Hi there,
>>>
>>>   I've recently been struggling with concurrency for an in-memory
>>> SQLite scenario. Basically, I want to be able to perform concurrent
>>> reads against an in-memory SQLite database, thus using multiple CPUs
>>> to good effect.
>>>
>>>   I've tried everything I could think of and find in various blog posts
>>> to get this working, but SQLite insisted on synchronizing the reads.
>>> Switching from an in-memory DB to an on-disk DB immediately rectified
>>> the issue.
>>>
>>>   What I'm wondering is whether SQLite flat out doesn't support
>>> concurrent access to an in-memory database, or perhaps whether I'm
>>> just doing something wrong. Can anyone confirm whether concurrent
>>> access to an in-memory database is supported?
>>>
>>>   Thanks,
>>>   Kent
>>>
>>> ___
>>> 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] Concurrency for in-memory scenarios

[Navaneeth Sen B]

Hi Pavel,
Could you please explain what you meant in this line?
> Of course I don't consider option of concurrency from
> different processes.
Thanks & Regards,
Sen


On 4/6/2010 8:33 PM, Pavel Ivanov wrote:
>> Can anyone confirm whether concurrent
>> access to an in-memory database is supported?
>>  
> No, SQLite doesn't support full concurrent access to any database. The
> only concurrency you can earn is having on-disk database without
> shared cache (so actually having several copies of the database in
> memory). Of course I don't consider option of concurrency from
> different processes.
>
>
> Pavel
>
> On Tue, Apr 6, 2010 at 9:43 AM, Kent Boogaart  wrote:
>
>>   Hi there,
>>
>>   I've recently been struggling with concurrency for an in-memory
>> SQLite scenario. Basically, I want to be able to perform concurrent
>> reads against an in-memory SQLite database, thus using multiple CPUs
>> to good effect.
>>
>>   I've tried everything I could think of and find in various blog posts
>> to get this working, but SQLite insisted on synchronizing the reads.
>> Switching from an in-memory DB to an on-disk DB immediately rectified
>> the issue.
>>
>>   What I'm wondering is whether SQLite flat out doesn't support
>> concurrent access to an in-memory database, or perhaps whether I'm
>> just doing something wrong. Can anyone confirm whether concurrent
>> access to an in-memory database is supported?
>>
>>   Thanks,
>>   Kent
>>
>> ___
>> 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] Concurrency for in-memory scenarios

[Alexey Pechnikov]

Hello!

On Thursday 08 April 2010 12:38:57 you wrote:
>  A RAM disk is a good idea, but would require me to jump through too 
> many hoops to get it approved. Corporate environment, you see  :( 
>  For now, I've gone with a disk-based database with a larger cache and 
> page size. 

You can set
PRAGMA synchronous=0
and writes will be very fast too. But will be possible database corruption.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency for in-memory scenarios

[Dan Kennedy]

On Apr 8, 2010, at 3:38 PM, Kent Boogaart wrote:

>  BODY { font-family:Arial, Helvetica, sans-serif;font-size:12px;
> }Thanks to all respondents.
> A RAM disk is a good idea, but would require me to jump through too
> many hoops to get it approved. Corporate environment, you see  :(
> For now, I've gone with a disk-based database with a larger cache and
> page size. Concurrent reads appear to be working, so I guess the
> shared cache is disabled by default . . . ?

It is indeed disabled by default.


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

Re: [sqlite] Concurrency for in-memory scenarios

[Kent Boogaart]

  BODY { font-family:Arial, Helvetica, sans-serif;font-size:12px; 
}Thanks to all respondents. 
 A RAM disk is a good idea, but would require me to jump through too 
many hoops to get it approved. Corporate environment, you see  :( 
 For now, I've gone with a disk-based database with a larger cache and 
page size. Concurrent reads appear to be working, so I guess the 
shared cache is disabled by default . . . ? 
 Best, 
 Kent 
 On Wed 07/04/10  6:47 PM , Alexey Pechnikov pechni...@mobigroup.ru 
sent: 
 Hello!  
 On Tuesday 06 April 2010 17:43:47 Kent Boogaart wrote:  
 >  What I'm wondering is whether SQLite flat out doesn't support   
 > concurrent access to an in-memory database, or perhaps whether I'm  
 
 > just doing something wrong. Can anyone confirm whether concurrent   
 > access to an in-memory database is supported?   
 Use database on tmpfs (ram disk) for this task. It's not needed to 
add   
 this functionality at SQLite internals.  
 Best regards, Alexey Pechnikov.  
 http://pechnikov.tel/  
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency for in-memory scenarios

[Alexey Pechnikov]

Hello!

On Tuesday 06 April 2010 17:43:47 Kent Boogaart wrote:
>  What I'm wondering is whether SQLite flat out doesn't support 
> concurrent access to an in-memory database, or perhaps whether I'm 
> just doing something wrong. Can anyone confirm whether concurrent 
> access to an in-memory database is supported? 

Use database on tmpfs (ram disk) for this task. It's not needed to add 
this functionality at SQLite internals.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency for in-memory scenarios

[Pavel Ivanov]

BTW, beware of this thread
http://www.mail-archive.com/sqlite-users@sqlite.org/msg50640.html too,
although it wasn't understood what was the actual problem there.


Pavel

On Tue, Apr 6, 2010 at 11:03 AM, Pavel Ivanov  wrote:
>> Can anyone confirm whether concurrent
>> access to an in-memory database is supported?
>
> No, SQLite doesn't support full concurrent access to any database. The
> only concurrency you can earn is having on-disk database without
> shared cache (so actually having several copies of the database in
> memory). Of course I don't consider option of concurrency from
> different processes.
>
>
> Pavel
>
> On Tue, Apr 6, 2010 at 9:43 AM, Kent Boogaart  wrote:
>>  Hi there,
>>
>>  I've recently been struggling with concurrency for an in-memory
>> SQLite scenario. Basically, I want to be able to perform concurrent
>> reads against an in-memory SQLite database, thus using multiple CPUs
>> to good effect.
>>
>>  I've tried everything I could think of and find in various blog posts
>> to get this working, but SQLite insisted on synchronizing the reads.
>> Switching from an in-memory DB to an on-disk DB immediately rectified
>> the issue.
>>
>>  What I'm wondering is whether SQLite flat out doesn't support
>> concurrent access to an in-memory database, or perhaps whether I'm
>> just doing something wrong. Can anyone confirm whether concurrent
>> access to an in-memory database is supported?
>>
>>  Thanks,
>>  Kent
>>
>> ___
>> 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] Concurrency for in-memory scenarios

[Pavel Ivanov]

> Can anyone confirm whether concurrent
> access to an in-memory database is supported?

No, SQLite doesn't support full concurrent access to any database. The
only concurrency you can earn is having on-disk database without
shared cache (so actually having several copies of the database in
memory). Of course I don't consider option of concurrency from
different processes.


Pavel

On Tue, Apr 6, 2010 at 9:43 AM, Kent Boogaart  wrote:
>  Hi there,
>
>  I've recently been struggling with concurrency for an in-memory
> SQLite scenario. Basically, I want to be able to perform concurrent
> reads against an in-memory SQLite database, thus using multiple CPUs
> to good effect.
>
>  I've tried everything I could think of and find in various blog posts
> to get this working, but SQLite insisted on synchronizing the reads.
> Switching from an in-memory DB to an on-disk DB immediately rectified
> the issue.
>
>  What I'm wondering is whether SQLite flat out doesn't support
> concurrent access to an in-memory database, or perhaps whether I'm
> just doing something wrong. Can anyone confirm whether concurrent
> access to an in-memory database is supported?
>
>  Thanks,
>  Kent
>
> ___
> 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] Concurrency for in-memory scenarios

[Kent Boogaart]

 Hi there, 
 
 I've recently been struggling with concurrency for an in-memory 
SQLite scenario. Basically, I want to be able to perform concurrent 
reads against an in-memory SQLite database, thus using multiple CPUs 
to good effect. 
 
 I've tried everything I could think of and find in various blog posts 
to get this working, but SQLite insisted on synchronizing the reads. 
Switching from an in-memory DB to an on-disk DB immediately rectified 
the issue. 
 
 What I'm wondering is whether SQLite flat out doesn't support 
concurrent access to an in-memory database, or perhaps whether I'm 
just doing something wrong. Can anyone confirm whether concurrent 
access to an in-memory database is supported? 
 
 Thanks, 
 Kent 
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency support for multiple process

[Max Vlasov]

>  I expect process B able to
> read the old data and displays appropriately. But, process B seems to be
> blocked. Why is this so as I thought SQLite handle concurrency as in this
> case where there are multiple reads and one single write ?
>

After Pavel's explanation just reread
http://www.sqlite.org/lockingv3.htmland can add that PENDING lock
theoretically allow reading for an existing
SHARED reader, but as I suppose single SELECT operation acquires SHARED and
releases it right at the end of the query so if your "display" process is
not in process of some sqlite operation, it probably has no SHARED lock so
for any new Select it is considered "new" so prohibited.

As long as I see the moment when the lock moves from RESERVED to PENDING
depends on the write cache size. I just made a test with two admins
accessing the same base and for a fresh base without any pragma tuning
several megabytes of inserts passed without changing the lock.

But the question is why do you need this kind of concurrency at all? If your
insert is small and wrapped in a single transaction, your "display"
application should not show noticeable "hanging" (if it just waits for
SQLITE_BUSY to disappear). On the other side If you do some caching starting
transaction and ending it only after some time, you may consider Commit on a
time basis so to give your "display" process chance to read the base. Anyway
I think holding single transaction for a long time is not a good idea so
coniser optimizing your write operations

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

Re: [sqlite] Concurrency support for multiple process

[Pavel Ivanov]

It's not clear what locks did you want to show with your diagram but
in reality locks would be held as following:

*Process A*: Begin Transaction - no lock
*Process B*: Select DB1  <-- shared lock
*Process A*: Insert DB1 <-- if insert is small then only reserved lock
*Process B*: Select DB1 <-- shared lock still can be obtained
*Process A*: Insert DB1 <-- if insert is big then pending lock is obtained
*Process B*: Select DB1 <-- cannot get shared lock, blocking
*Process A*: <-- whatever it does here it still has pending lock
*Process A*: Commit Transaction <-- exclusive lock then release all locks
*Process B*: Can proceed with blocked Select and do anything else


Pavel

On Thu, Jan 28, 2010 at 7:00 AM, nyetngoh wong  wrote:
> Hi,
> Is the locking states shown below for DB1 correct ?
>
> *Process A*
> *Process B*
> Begin Transaction
> Insert DB1                    <-- Reserved lock
> Select DB1                    <-- Shared lock
> Insert DB1
> :
> after 10 Inserts and 10 Selects
>
> Select DB1            <-- Shared lock
> :
> Continue Inserts and Selects
> :
>
> End Transaction          <-- Pending to Exclusive Lock
>
>
> On Thu, Jan 28, 2010 at 7:42 PM, Max Vlasov  wrote:
>
>> >
>> > > SQLite allows multiple readers OR a single writer to access the
>> database
>> > simultaneously.
>> > From the SQLite doc, as long as no transaction is pending, other process
>> > can
>> > read or obtain reserved lock for write.
>> >
>>
>> the docs say: "Locks are not acquired until the first read or write
>> operation. The first read operation against a database creates a SHARED
>> lock
>> and the first write operation creates a RESERVED lock.".
>>
>> So your only chance to have concurrent access from both processes only if
>> the first one starts transaction and does something innocent like
>> preparations with selects, temp tables and so on. Any UPDATE or INSERT will
>> lead to RESERVED lock and process B suspension with db access.
>>
>> Max
>> ___
>> 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] Concurrency support for multiple process

[nyetngoh wong]

Hi,
Is the locking states shown below for DB1 correct ?

*Process A*
*Process B*
Begin Transaction
Insert DB1<-- Reserved lock
Select DB1<-- Shared lock
Insert DB1
:
after 10 Inserts and 10 Selects

Select DB1<-- Shared lock
:
Continue Inserts and Selects
:

End Transaction  <-- Pending to Exclusive Lock


On Thu, Jan 28, 2010 at 7:42 PM, Max Vlasov  wrote:

> >
> > > SQLite allows multiple readers OR a single writer to access the
> database
> > simultaneously.
> > From the SQLite doc, as long as no transaction is pending, other process
> > can
> > read or obtain reserved lock for write.
> >
>
> the docs say: "Locks are not acquired until the first read or write
> operation. The first read operation against a database creates a SHARED
> lock
> and the first write operation creates a RESERVED lock.".
>
> So your only chance to have concurrent access from both processes only if
> the first one starts transaction and does something innocent like
> preparations with selects, temp tables and so on. Any UPDATE or INSERT will
> lead to RESERVED lock and process B suspension with db access.
>
> Max
> ___
> 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] Concurrency support for multiple process

[Max Vlasov]

>
> > SQLite allows multiple readers OR a single writer to access the database
> simultaneously.
> From the SQLite doc, as long as no transaction is pending, other process
> can
> read or obtain reserved lock for write.
>

the docs say: "Locks are not acquired until the first read or write
operation. The first read operation against a database creates a SHARED lock
and the first write operation creates a RESERVED lock.".

So your only chance to have concurrent access from both processes only if
the first one starts transaction and does something innocent like
preparations with selects, temp tables and so on. Any UPDATE or INSERT will
lead to RESERVED lock and process B suspension with db access.

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

Re: [sqlite] Concurrency support for multiple process

[nyetngoh wong]

> What made you expect that?
Process A has not entered the exclusive lock and so process B can obtain
shared lock to read.

> SQLite allows multiple readers OR a single writer to access the database
simultaneously.
>From the SQLite doc, as long as no transaction is pending, other process can
read or obtain reserved lock for write.

On Thu, Jan 28, 2010 at 12:51 PM, Igor Tandetnik wrote:

> nyetngoh wong wrote:
> > First, I've a process A that do many inserts to the database and reads
> back
> > from the database to verify. The writes are done in one DEFERRED
> transaction
> > as data are not committed yet. While the first process running, another
> > process B is launched to read from the database for display purposes.
> > Process B will halts until the transaction ends. I expect process B able
> to
> > read the old data and displays appropriately.
>
> What made you expect that?
>
> > But, process B seems to be
> > blocked. Why is this so as I thought SQLite handle concurrency as in this
> > case where there are multiple reads and one single write ?
>
> SQLite allows multiple readers OR a single writer to access the database
> simultaneously.
>
> 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] Concurrency support for multiple process

[Igor Tandetnik]

nyetngoh wong wrote:
> First, I've a process A that do many inserts to the database and reads back
> from the database to verify. The writes are done in one DEFERRED transaction
> as data are not committed yet. While the first process running, another
> process B is launched to read from the database for display purposes.
> Process B will halts until the transaction ends. I expect process B able to
> read the old data and displays appropriately.

What made you expect that?

> But, process B seems to be
> blocked. Why is this so as I thought SQLite handle concurrency as in this
> case where there are multiple reads and one single write ?

SQLite allows multiple readers OR a single writer to access the database 
simultaneously.

Igor Tandetnik

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

[sqlite] Concurrency support for multiple process

[nyetngoh wong]

Hi,

First, I've a process A that do many inserts to the database and reads back
from the database to verify. The writes are done in one DEFERRED transaction
as data are not committed yet. While the first process running, another
process B is launched to read from the database for display purposes.
Process B will halts until the transaction ends. I expect process B able to
read the old data and displays appropriately. But, process B seems to be
blocked. Why is this so as I thought SQLite handle concurrency as in this
case where there are multiple reads and one single write ? Please help.
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency Question

[Pavel Ivanov]

> If I have 2 separate processes, 1 of which will attempt to Read and Write a
> Database (Process A)
> and the 2nd which will only Read the database (Process B), then if Process A
> is in the middle of a Write when Process B tries to read what will happen?

If Process A is in the middle of writing transaction but has not
actually written anything to disk yet (all changes are in cache) then
Process B will proceed reading unlocked. If Process A is in the middle
of committing transaction or it has written some changes to disk
(there're so many changes that they don't fit into cache) then API in
Process B will return SQLITE_BUSY error. But only unless you've called
sqlite3_busy_timeout() with non-zero value. In the latter case Process
B will return SQLITE_BUSY only after mentioned timeout has gone and
Process A does not finish transaction yet. And when SQLITE_BUSY is
returned then indeed it's up to you to try again later.

> And Vice versa, What happens if Process B is reading while A tries to write?

Process A will continue unblocked until it will have to actually write
to disk. At this point it will wait for Process B to finish its
reading and after that it will actually write everything it needs.


Pavel

On Fri, Aug 7, 2009 at 11:50 AM, JimmyKryptonite wrote:
>
> I'm looking to start a project using SQLite to handle some database
> transactions.  I have question about how SQLite handles Concurrency.  I read
> up on SQLite.org about the file locking and concurrency but I didn't really
> get the info I wanted (at least I didn't walk away with an answer in the
> terms I wanted it).
>
> My question is the following:
>
> If I have 2 separate processes, 1 of which will attempt to Read and Write a
> Database (Process A)
> and the 2nd which will only Read the database (Process B), then if Process A
> is in the middle of a Write when Process B tries to read what will happen?
> Will the Read request be blocked and Process B will wait or will SQLite
> return some kind of Busy Error Code to process B and it is up to Process B
> to try again later?
>
> And Vice versa, What happens if Process B is reading while A tries to write?
> Same answer as above?
>
> I'm very much a newbie to SQLite so patience is requested.
>
> Thanks in advance
> --
> View this message in context: 
> http://www.nabble.com/Concurrency-Question-tp24867278p24867278.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] Concurrency Question

[JimmyKryptonite]

I'm looking to start a project using SQLite to handle some database
transactions.  I have question about how SQLite handles Concurrency.  I read
up on SQLite.org about the file locking and concurrency but I didn't really
get the info I wanted (at least I didn't walk away with an answer in the
terms I wanted it).

My question is the following:

If I have 2 separate processes, 1 of which will attempt to Read and Write a
Database (Process A)
and the 2nd which will only Read the database (Process B), then if Process A
is in the middle of a Write when Process B tries to read what will happen? 
Will the Read request be blocked and Process B will wait or will SQLite
return some kind of Busy Error Code to process B and it is up to Process B
to try again later?

And Vice versa, What happens if Process B is reading while A tries to write? 
Same answer as above?

I'm very much a newbie to SQLite so patience is requested.

Thanks in advance
-- 
View this message in context: 
http://www.nabble.com/Concurrency-Question-tp24867278p24867278.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] concurrency differences between in-memory and on disk?

[Igor Tandetnik]

pierr wrote:
> So, for "A single connection shared by multiple threads" case, is the
> statement "there's a
> many-readers-single-writer lock at the database level" still ture?

Well, the lock is still there, but since there are no other connections, 
there's noone to be locked out by it. The connection itself is not 
affected by its own lock.

Igor Tandetnik 



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

Re: [sqlite] concurrency differences between in-memory and on disk?

[pierr]

Igor Tandetnik wrote:
> 
> Daniel Watrous wrote:
>> I've developed an application that has very high concurrency.  In my
>> initial testing we used SQLite 3 from python, but we experienced too
>> many locks and the database always fell behind.
> 
> What precisely is the nature of the concurrency? Are you opening 
> multiple connections to the same database, or sharing a single 
> connection between threads? Are you trying to write concurrently, and if 
> so, is it to the same table or to different tables?
> 
> Basically, there are three ways SQLite can be used (they can also be 
> combined):
> 
> 1. Many connections to the same database. In this case, there's a 
> many-readers-single-writer lock at the database level, so at any point 
> in time only one connection can write.
> 
> 2. A single connection shared by multiple threads. A connection 
> maintains a mutex that every API call acquires on entry and releases on 
> return, so all calls are serialized. However, one thread can, say, step 
> through a select resultset row-by-row, while another inserts row after 
> row into some table: these calls can interleave, and would appear almost 
> concurrent.
>  
Hi Igor,
So, for "A single connection shared by multiple threads" case, is the
statement "there's a 
many-readers-single-writer lock at the database level" still ture? At least
, the read and write 
would appear concurrent as you said.

Is there any examples showing the difference between these 2 different usage
model?
Thanks.

- Pierr

-- 
View this message in context: 
http://www.nabble.com/concurrency-differences-between-in-memory-and-on-disk--tp24201096p24266195.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] concurrency differences between in-memory and on disk?

[Igor Tandetnik]

Daniel Watrous wrote:
> I've developed an application that has very high concurrency.  In my
> initial testing we used SQLite 3 from python, but we experienced too
> many locks and the database always fell behind.

What precisely is the nature of the concurrency? Are you opening 
multiple connections to the same database, or sharing a single 
connection between threads? Are you trying to write concurrently, and if 
so, is it to the same table or to different tables?

Basically, there are three ways SQLite can be used (they can also be 
combined):

1. Many connections to the same database. In this case, there's a 
many-readers-single-writer lock at the database level, so at any point 
in time only one connection can write.

2. A single connection shared by multiple threads. A connection 
maintains a mutex that every API call acquires on entry and releases on 
return, so all calls are serialized. However, one thread can, say, step 
through a select resultset row-by-row, while another inserts row after 
row into some table: these calls can interleave, and would appear almost 
concurrent.

Note that transactions are maintained on a per-connection basis. So if 
one thread starts a transaction and then another thread writes through 
the same connection, that change becomes part of that transaction. If 
the original thread then rolls it back (perhaps unaware that the other 
thread also made changes), all changes will be lost.

3. Multiple connections sharing cache:

http://sqlite.org/sharedcache.html

This mode supports many-readers-single-writer locks on a per-table 
basis, rather than on the whole database. Multiple connections can write 
simultaneously, as long as they write to different tables. Further, you 
can turn on a "read uncommitted" mode: this way, reads are not blocked 
at all, but they can read inconsistent data, or even data that will 
never actually exist in the database (if the transaction is later rolled 
back).

It is rare, in my experience, that the database schema is amenable to 
being used in this mode. You need groups of tables that are largely 
unrelated to each other and so can be updated independently, at which 
point you might consider just splitting them into multiple databases and 
go with #1.

Igor Tandetnik 



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

[sqlite] concurrency differences between in-memory and on disk?

[Daniel Watrous]

Hello,

I've developed an application that has very high concurrency.  In my
initial testing we used SQLite 3 from python, but we experienced too
many locks and the database always fell behind.  We moved to MySQL,
which handles the concurrency better, but there was a substantial
increase in IO.  We're now considering writing our own persistence
solution that would reside in memory.

I figured I would ask if there is anything that would improve the
performance of SQLite running in memory over what I would expect on
disk?  Would it handle concurrency better?

Thanks for any and all feedback.

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

[sqlite] concurrency differences between in-memory and on disk?

[Daniel Watrous]

Hello,

I've developed an application that has very high concurrency.  In my
initial testing we used SQLite 3 from python, but we experienced too
many locks and the database always fell behind.  We moved to MySQL,
which handles the concurrency better, but there was a substantial
increase in IO.  We're now considering writing our own persistence
solution that would reside in memory.

I figured I would ask if there is anything that would improve the
performance of SQLite running in memory over what I would expect on
disk?  Would it handle concurrency better?

Thanks for any and all feedback.

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

Re: [sqlite] Sqlite concurrency problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Paul Clarke wrote:
> So one can share one open raw database connection across multiple threads can 
> you?

That is true in theory but there are some issues in practise.  For
example there is currently no thread safe way to get error messages
(being addressed).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkQ1tMACgkQmOOfHg372QTyAACgltVnjYHDa5bgUR9NHz05efPz
J7sAoJ3tfiWGdwFfpBl1Kyg7+CuSA96P
=i8Gx
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite concurrency problem

[Paul Clarke]

Thanks

I think that may be the problem.  So one can share one open raw database
connection across multiple threads can you?

I had (wrongly) assumed that one had to open one such connection for
each thread

Many thanks

Paul Clarke
Project LastMile IT
Location: Level B offices, Hatfield CFC
Direct line: 01707 228023
Mobile Work: 07951 180240
Mobile Home: 07767 313141
Internal extension: 8023
www.ocado.com
 
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: 03 November 2008 22:46
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite concurrency problem


On Nov 3, 2008, at 8:04 AM, Paul Clarke wrote:

> Is it really correct that in order to perform an INSERT, Sqlite  
> demands
> that no other connections be active?
>
> It uses the same database instance but (obviously) separate statements
> for each operation
>

Please distinguish between a "connection" and a "statement".  A  
"connection" is what you get back from sqlite3_open().  A "statement"  
is what you get back from sqlite3_prepare_v2().

You can INSERT while a query is pending on the same connection.  You  
cannot INSERT while a query is pending on the same database file but  
using a different connection.  Within a single program there is rarely  
a need to have multiple connections so this is seldom an issue.


>
>
> When the insert is attempted, SQLITE_BUSY is  returned
>
>
>
> Waiting is obviously no use because the conflicting operation is in  
> the
> outer loop
>
>
>
> Is this expected or am I doing something silly?
>
>
>
> Thanks
>
>
>
> Paul Clarke
>
>
> *
>
> Notice:  This email is confidential and may contain copyright  
> material of Ocado Limited (the "Company"). Opinions and views  
> expressed in this message may not necessarily reflect the opinions  
> and views of the Company.
> If you are not the intended recipient, please notify us immediately  
> and delete all copies of this message. Please note that it is your  
> responsibility to scan this message for viruses.
> Please do not print this email unless necessary.
>
> *
>
>
> Ocado Limited
>
> Titan Court
> 3 Bishops Square
> Hatfield Business Park
> Hatfield
> Herts
> AL10 9NE
> Tel: +44 (0) 1707 228000
> Fax: +44 (0) 1707 227999
> www.ocado.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
[EMAIL PROTECTED]



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

_
This message has been checked for all known viruses by the 
MessageLabs Virus Control Centre.

*

Notice:  This email is confidential and may contain copyright material of Ocado 
Limited (the "Company"). Opinions and views expressed in this message may not 
necessarily reflect the opinions and views of the Company. 
If you are not the intended recipient, please notify us immediately and delete 
all copies of this message. Please note that it is your responsibility to scan 
this message for viruses.
Please do not print this email unless necessary.

*


Ocado Limited

Titan Court
3 Bishops Square
Hatfield Business Park
Hatfield
Herts
AL10 9NE
Tel: +44 (0) 1707 228000
Fax: +44 (0) 1707 227999
www.ocado.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite concurrency problem

[D. Richard Hipp]

On Nov 3, 2008, at 8:04 AM, Paul Clarke wrote:

> Is it really correct that in order to perform an INSERT, Sqlite  
> demands
> that no other connections be active?
>
> It uses the same database instance but (obviously) separate statements
> for each operation
>

Please distinguish between a "connection" and a "statement".  A  
"connection" is what you get back from sqlite3_open().  A "statement"  
is what you get back from sqlite3_prepare_v2().

You can INSERT while a query is pending on the same connection.  You  
cannot INSERT while a query is pending on the same database file but  
using a different connection.  Within a single program there is rarely  
a need to have multiple connections so this is seldom an issue.


>
>
> When the insert is attempted, SQLITE_BUSY is  returned
>
>
>
> Waiting is obviously no use because the conflicting operation is in  
> the
> outer loop
>
>
>
> Is this expected or am I doing something silly?
>
>
>
> Thanks
>
>
>
> Paul Clarke
>
>
> *
>
> Notice:  This email is confidential and may contain copyright  
> material of Ocado Limited (the "Company"). Opinions and views  
> expressed in this message may not necessarily reflect the opinions  
> and views of the Company.
> If you are not the intended recipient, please notify us immediately  
> and delete all copies of this message. Please note that it is your  
> responsibility to scan this message for viruses.
> Please do not print this email unless necessary.
>
> *
>
>
> Ocado Limited
>
> Titan Court
> 3 Bishops Square
> Hatfield Business Park
> Hatfield
> Herts
> AL10 9NE
> Tel: +44 (0) 1707 228000
> Fax: +44 (0) 1707 227999
> www.ocado.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] Sqlite concurrency problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Paul Clarke wrote:
> Is it really correct that in order to perform an INSERT, Sqlite demands
> that no other connections be active?

http://www.sqlite.org/lockingv3.html
http://www.sqlite.org/sharedcache.html
http://www.sqlite.org/atomiccommit.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkPfZgACgkQmOOfHg372QT0UwCfeukTaWoY6+Mv5TfYtG+/0fIr
ifEAoNffZzvvjbjIvbji5wQ5DrgBQXI5
=Ycbl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite concurrency problem

[Paul Clarke]

Is it really correct that in order to perform an INSERT, Sqlite demands
that no other connections be active?

 

I have an application that loops through a results set and performs
INSERTS.

 

It uses the same database instance but (obviously) separate statements
for each operation

 

When the insert is attempted, SQLITE_BUSY is  returned

 

Waiting is obviously no use because the conflicting operation is in the
outer loop

 

Is this expected or am I doing something silly?

 

Thanks

 

Paul Clarke


*

Notice:  This email is confidential and may contain copyright material of Ocado 
Limited (the "Company"). Opinions and views expressed in this message may not 
necessarily reflect the opinions and views of the Company. 
If you are not the intended recipient, please notify us immediately and delete 
all copies of this message. Please note that it is your responsibility to scan 
this message for viruses.
Please do not print this email unless necessary.

*


Ocado Limited

Titan Court
3 Bishops Square
Hatfield Business Park
Hatfield
Herts
AL10 9NE
Tel: +44 (0) 1707 228000
Fax: +44 (0) 1707 227999
www.ocado.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency access to SQLite

[Igor Tandetnik]

"Alexander Batyrshin" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
> 1. Any single SQL command in SQLite start transaction.

Yes, unless part of an explicit transaction initiated with a BEGIN 
statement.

> Any write
> operation should start with getting shared lock.

No, write operations start by acquiring a reserved lock.

> 2. What will be if we have SQL command like this "UPDATE ... SELECT" ?

It's a write operation and it starts by acquiring a reserved lock.

Precisely because of statements like this, a reserved lock is compatible 
with shared locks. You may execute an UPDATE statement, but it might 
have to do some reading before it's ready to write, and meanwhile you 
don't want to lock out other readers. Reserved lock indicates that a 
transaction will be writing at some point in the future. It must promote 
to an exclusive lock before actually writing to the physical file.

> In this case we have explicitly calls to SELECT which should get read
> lock.

No, you don't run a SELECT statement. You run an UPDATE statement. The 
fact that the statement contains a nested select is immaterial.

Igor Tandetnik 



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

Re: [sqlite] Concurrency access to SQLite

[Alexander Batyrshin]

> > For example, if 2 processes executes simple SQL INSERT commands and
>  > gets situation like above, one of them can easily drop read lock and
>  > wait for another one. There is no problem for this case.
>
>  Two concurrent inserts never result in a deadlock. For a deadlock to
>  occur in SQLite, at least one transaction should start as a read-only
>  (with a select statement) and later attempt to promote to read-write
>  (with insert, update or delete statements). In this case you may get
>  into a situation where the first transaction holds a shared lock and
>  waits to promote it to reserved, and the second one holds a pending
>  lock, wants to promote it to exclusive and waits for all readers (shared
>  locks) to clear.

I have some additional questions, just for consistency of my knowledge.
So, please, don't irritate.

1. Any single SQL command in SQLite start transaction. Any write
operation should start with getting shared lock. So question is shared
lock == read lock? if its true, then two inserts is transactions which
starts with read lock... So it's still possible situation like above.

2. What will be if we have SQL command like this "UPDATE ... SELECT" ?
In this case we have explicitly calls to SELECT which should get read
lock.

-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency access to SQLite

[Alexander Batyrshin]

I got it. Fixed my program with "IMMEDIATE" transaction.

On Fri, Apr 25, 2008 at 12:01 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> "Alexander Batyrshin" <[EMAIL PROTECTED]>
>  wrote in message
>  news:[EMAIL PROTECTED]
>
> >>  Dropping the read lock is the same as rolling back the
>  >>  transaction.  The first process can, in fact, do this.  And
>  >>  the second process is waiting for the first process to do
>  >>  this.  But the first process cannot do it automatically.  The
>  >>  application must issue a "COMMIT" or "ROLLBACK" command
>  >>  to make it happen.
>  >
>
> > For example, if 2 processes executes simple SQL INSERT commands and
>  > gets situation like above, one of them can easily drop read lock and
>  > wait for another one. There is no problem for this case.
>
>  Two concurrent inserts never result in a deadlock. For a deadlock to
>  occur in SQLite, at least one transaction should start as a read-only
>  (with a select statement) and later attempt to promote to read-write
>  (with insert, update or delete statements). In this case you may get
>  into a situation where the first transaction holds a shared lock and
>  waits to promote it to reserved, and the second one holds a pending
>  lock, wants to promote it to exclusive and waits for all readers (shared
>  locks) to clear.
>
>  Igor Tandetnik
>
>
>
>
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency access to SQLite

[Igor Tandetnik]

"Alexander Batyrshin" <[EMAIL PROTECTED]>
wrote in message
news:[EMAIL PROTECTED]
>>  Dropping the read lock is the same as rolling back the
>>  transaction.  The first process can, in fact, do this.  And
>>  the second process is waiting for the first process to do
>>  this.  But the first process cannot do it automatically.  The
>>  application must issue a "COMMIT" or "ROLLBACK" command
>>  to make it happen.
>
> For example, if 2 processes executes simple SQL INSERT commands and
> gets situation like above, one of them can easily drop read lock and
> wait for another one. There is no problem for this case.

Two concurrent inserts never result in a deadlock. For a deadlock to 
occur in SQLite, at least one transaction should start as a read-only 
(with a select statement) and later attempt to promote to read-write 
(with insert, update or delete statements). In this case you may get 
into a situation where the first transaction holds a shared lock and 
waits to promote it to reserved, and the second one holds a pending 
lock, wants to promote it to exclusive and waits for all readers (shared 
locks) to clear.

Igor Tandetnik 



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

Re: [sqlite] Concurrency access to SQLite

[Alexander Batyrshin]

>  Dropping the read lock is the same as rolling back the
>  transaction.  The first process can, in fact, do this.  And
>  the second process is waiting for the first process to do
>  this.  But the first process cannot do it automatically.  The
>  application must issue a "COMMIT" or "ROLLBACK" command
>  to make it happen.

This is looks little bit odd for me.
For example, if 2 processes executes simple SQL INSERT commands and
gets situation like above, one of them can easily drop read lock and
wait for another one. There is no problem for this case.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency access to SQLite

[D. Richard Hipp]

On Apr 24, 2008, at 7:42 AM, Alexander Batyrshin wrote:

> I am not understand this example. First of all second process can't
> promote exclusive lock from reserved. It should use intermediate
> pending lock.

It does go to pending.  But it still cannot complete the transaction
until it is able to promote the pending lock to exclusive.  And it
cannot promote to exclusive until the first process drops its read
lock.

> And secondary why first process can't just drop read
> lock and then invoke busy handler?

Dropping the read lock is the same as rolling back the
transaction.  The first process can, in fact, do this.  And
the second process is waiting for the first process to do
this.  But the first process cannot do it automatically.  The
application must issue a "COMMIT" or "ROLLBACK" command
to make it happen.

>
>
> In this case any write to database that already has process
>
> On Thu, Apr 24, 2008 at 6:01 PM, Simon Davies
> <[EMAIL PROTECTED]> wrote:
>> Alexander,
>>
>> From http://www.sqlite.org/c3ref/busy_handler.html
>>
>> "The presence of a busy handler does not guarantee that it will be
>> invoked when there is lock contention. If SQLite determines that
>> invoking the busy handler could result in a deadlock, it will go  
>> ahead
>> and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of invoking  
>> the
>> busy handler. Consider a scenario where one process is holding a read
>> lock that it is trying to promote to a reserved lock and a second
>> process is holding a reserved lock that it is trying to promote to an
>> exclusive lock. The first process cannot proceed because it is  
>> blocked
>> by the second and the second process cannot proceed because it is
>> blocked by the first. If both processes invoke the busy handlers,
>> neither will make any progress. Therefore, SQLite returns SQLITE_BUSY
>> for the first process, hoping that this will induce the first process
>> to release its read lock and allow the second process to proceed"
>>
>> Rgds,
>> Simon
>>
>> 2008/4/24 Alexander Batyrshin <[EMAIL PROTECTED]>:
>>
>>
>>> Oh... Nope, I am not using any thread-mechanism.
>>> I am using simple processes (via fork). So synchronization should be
>>> task for SQLite library.
>>>
>>> But right now I am confused, because my processes do not blocks on
>>> sqlite3_exec. They immediately report BUSY_TIMEOUT, without awaiting
>>> for time set by sqlite3_busy_timeout.
>>>
>>>
>>>
>>> On Thu, Apr 24, 2008 at 4:29 PM, John Stanton  
>>> <[EMAIL PROTECTED]> wrote:
 If it is one process I would assign a mutex to the resource  
 (Sqlite) and
 wait on it to get access to the resource.  When the Sqlite  
 operation is
 complete release the mutex and the next thread will have exclusive
 access to it.

 If you use pthreads you can use read and write locks to get  
 concurrency
 on reads.

 To my mind syncing on a mutex is better and simpler than polling  
 the
 resource using SQLITE_BUSY.



 Alexander Batyrshin wrote:
> So, you advice me, to implement synchronization inside my  
> process by my self?
>
> On Thu, Apr 24, 2008 at 3:40 PM, John Stanton  
> <[EMAIL PROTECTED]> wrote:
>> You have a single shared resource, Sqlite, and you have to  
>> synchronize
>> access.  You can use the internal locking in Sqlite and use  
>> polling or
>> wait on a mutex or semaphore.
>>
>>
>> Alexander Batyrshin wrote:
>>> Hello All,
>>>
>>> I am observing situation, that my concurrency process does not  
>>> have
>>> access to SQLite database with equal probability.
>>>
>>> Here is example. I have N process that do work like this:
>>>
>>> while (1) {
>>>do_some_work(); // takes ~ 30 sec
>>>save_work_result_to_sqlite(); // takes ~ 1 sec
>>> }
>>>
>>> So, as you can see, these N process has concurrency access to  
>>> SQLite database.
>>> In theory in worst case, save_work_result_to_sqlite() should  
>>> NOT wait
>>> for access to database longer than N * 1 sec.
>>> But in practice, some process blocks on save_work_to_sqlite()  
>>> more
>>> than N*2 sec and dies on my SQLITE_BUSY asserts :/
>>>
>>> So, I am wondering, is there any ideas how to avoid this?
>>>
>>
>> ___
>> 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

>>>
>>>
>>>
>>> --
>>> Alexander Batyrshin aka bash
>>> bash = Biomechanica Artificial Sabotage Humanoid
>>> ___
>>>
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> 

Re: [sqlite] Concurrency access to SQLite

[John Stanton]

If you are using processes you can sync them using a semaphore so that 
it automatically blocks.  Alternatively do not use sqlite3_exec (it is 
an old interface) and instead use sqlite3_prepare ... sqlite3_step.  If 
you get an SQLITE_BUSY returned by sqlite3_step then pause a hundred mS 
or so and try again.

If you don't use threads you can compile Sqlite without THREADSAFE and 
have it run a little faster.

Alexander Batyrshin wrote:
> Oh... Nope, I am not using any thread-mechanism.
> I am using simple processes (via fork). So synchronization should be
> task for SQLite library.
> 
> But right now I am confused, because my processes do not blocks on
> sqlite3_exec. They immediately report BUSY_TIMEOUT, without awaiting
> for time set by sqlite3_busy_timeout.
> 
> 
> On Thu, Apr 24, 2008 at 4:29 PM, John Stanton <[EMAIL PROTECTED]> wrote:
>> If it is one process I would assign a mutex to the resource (Sqlite) and
>>  wait on it to get access to the resource.  When the Sqlite operation is
>>  complete release the mutex and the next thread will have exclusive
>>  access to it.
>>
>>  If you use pthreads you can use read and write locks to get concurrency
>>  on reads.
>>
>>  To my mind syncing on a mutex is better and simpler than polling the
>>  resource using SQLITE_BUSY.
>>
>>
>>
>>  Alexander Batyrshin wrote:
>>  > So, you advice me, to implement synchronization inside my process by my 
>> self?
>>  >
>>  > On Thu, Apr 24, 2008 at 3:40 PM, John Stanton <[EMAIL PROTECTED]> wrote:
>>  >> You have a single shared resource, Sqlite, and you have to synchronize
>>  >>  access.  You can use the internal locking in Sqlite and use polling or
>>  >>  wait on a mutex or semaphore.
>>  >>
>>  >>
>>  >>  Alexander Batyrshin wrote:
>>  >>  >  Hello All,
>>  >>  >
>>  >>  > I am observing situation, that my concurrency process does not have
>>  >>  > access to SQLite database with equal probability.
>>  >>  >
>>  >>  > Here is example. I have N process that do work like this:
>>  >>  >
>>  >>  > while (1) {
>>  >>  > do_some_work(); // takes ~ 30 sec
>>  >>  > save_work_result_to_sqlite(); // takes ~ 1 sec
>>  >>  > }
>>  >>  >
>>  >>  > So, as you can see, these N process has concurrency access to SQLite 
>> database.
>>  >>  > In theory in worst case, save_work_result_to_sqlite() should NOT wait
>>  >>  > for access to database longer than N * 1 sec.
>>  >>  > But in practice, some process blocks on save_work_to_sqlite() more
>>  >>  > than N*2 sec and dies on my SQLITE_BUSY asserts :/
>>  >>  >
>>  >>  > So, I am wondering, is there any ideas how to avoid this?
>>  >>  >
>>  >>
>>  >>  ___
>>  >>  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] Concurrency access to SQLite

[Alexander Batyrshin]

I am not understand this example. First of all second process can't
promote exclusive lock from reserved. It should use intermediate
pending lock. And secondary why first process can't just drop read
lock and then invoke busy handler?

In this case any write to database that already has process

On Thu, Apr 24, 2008 at 6:01 PM, Simon Davies
<[EMAIL PROTECTED]> wrote:
> Alexander,
>
>  From http://www.sqlite.org/c3ref/busy_handler.html
>
>  "The presence of a busy handler does not guarantee that it will be
>  invoked when there is lock contention. If SQLite determines that
>  invoking the busy handler could result in a deadlock, it will go ahead
>  and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of invoking the
>  busy handler. Consider a scenario where one process is holding a read
>  lock that it is trying to promote to a reserved lock and a second
>  process is holding a reserved lock that it is trying to promote to an
>  exclusive lock. The first process cannot proceed because it is blocked
>  by the second and the second process cannot proceed because it is
>  blocked by the first. If both processes invoke the busy handlers,
>  neither will make any progress. Therefore, SQLite returns SQLITE_BUSY
>  for the first process, hoping that this will induce the first process
>  to release its read lock and allow the second process to proceed"
>
>  Rgds,
>  Simon
>
>  2008/4/24 Alexander Batyrshin <[EMAIL PROTECTED]>:
>
>
> > Oh... Nope, I am not using any thread-mechanism.
>  > I am using simple processes (via fork). So synchronization should be
>  > task for SQLite library.
>  >
>  > But right now I am confused, because my processes do not blocks on
>  > sqlite3_exec. They immediately report BUSY_TIMEOUT, without awaiting
>  > for time set by sqlite3_busy_timeout.
>  >
>  >
>  >
>  > On Thu, Apr 24, 2008 at 4:29 PM, John Stanton <[EMAIL PROTECTED]> wrote:
>  > > If it is one process I would assign a mutex to the resource (Sqlite) and
>  > >  wait on it to get access to the resource.  When the Sqlite operation is
>  > >  complete release the mutex and the next thread will have exclusive
>  > >  access to it.
>  > >
>  > >  If you use pthreads you can use read and write locks to get concurrency
>  > >  on reads.
>  > >
>  > >  To my mind syncing on a mutex is better and simpler than polling the
>  > >  resource using SQLITE_BUSY.
>  > >
>  > >
>  > >
>  > >  Alexander Batyrshin wrote:
>  > >  > So, you advice me, to implement synchronization inside my process by 
> my self?
>  > >  >
>  > >  > On Thu, Apr 24, 2008 at 3:40 PM, John Stanton <[EMAIL PROTECTED]> 
> wrote:
>  > >  >> You have a single shared resource, Sqlite, and you have to 
> synchronize
>  > >  >>  access.  You can use the internal locking in Sqlite and use polling 
> or
>  > >  >>  wait on a mutex or semaphore.
>  > >  >>
>  > >  >>
>  > >  >>  Alexander Batyrshin wrote:
>  > >  >>  >  Hello All,
>  > >  >>  >
>  > >  >>  > I am observing situation, that my concurrency process does not 
> have
>  > >  >>  > access to SQLite database with equal probability.
>  > >  >>  >
>  > >  >>  > Here is example. I have N process that do work like this:
>  > >  >>  >
>  > >  >>  > while (1) {
>  > >  >>  > do_some_work(); // takes ~ 30 sec
>  > >  >>  > save_work_result_to_sqlite(); // takes ~ 1 sec
>  > >  >>  > }
>  > >  >>  >
>  > >  >>  > So, as you can see, these N process has concurrency access to 
> SQLite database.
>  > >  >>  > In theory in worst case, save_work_result_to_sqlite() should NOT 
> wait
>  > >  >>  > for access to database longer than N * 1 sec.
>  > >  >>  > But in practice, some process blocks on save_work_to_sqlite() more
>  > >  >>  > than N*2 sec and dies on my SQLITE_BUSY asserts :/
>  > >  >>  >
>  > >  >>  > So, I am wondering, is there any ideas how to avoid this?
>  > >  >>  >
>  > >  >>
>  > >  >>  ___
>  > >  >>  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
>  > >
>  >
>  >
>  >
>  > --
>  > Alexander Batyrshin aka bash
>  > bash = Biomechanica Artificial Sabotage Humanoid
>  > ___
>  >
>  > 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
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org

Re: [sqlite] Concurrency access to SQLite

[Simon Davies]

Alexander,

>From http://www.sqlite.org/c3ref/busy_handler.html

"The presence of a busy handler does not guarantee that it will be
invoked when there is lock contention. If SQLite determines that
invoking the busy handler could result in a deadlock, it will go ahead
and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of invoking the
busy handler. Consider a scenario where one process is holding a read
lock that it is trying to promote to a reserved lock and a second
process is holding a reserved lock that it is trying to promote to an
exclusive lock. The first process cannot proceed because it is blocked
by the second and the second process cannot proceed because it is
blocked by the first. If both processes invoke the busy handlers,
neither will make any progress. Therefore, SQLite returns SQLITE_BUSY
for the first process, hoping that this will induce the first process
to release its read lock and allow the second process to proceed"

Rgds,
Simon

2008/4/24 Alexander Batyrshin <[EMAIL PROTECTED]>:
> Oh... Nope, I am not using any thread-mechanism.
> I am using simple processes (via fork). So synchronization should be
> task for SQLite library.
>
> But right now I am confused, because my processes do not blocks on
> sqlite3_exec. They immediately report BUSY_TIMEOUT, without awaiting
> for time set by sqlite3_busy_timeout.
>
>
>
> On Thu, Apr 24, 2008 at 4:29 PM, John Stanton <[EMAIL PROTECTED]> wrote:
> > If it is one process I would assign a mutex to the resource (Sqlite) and
> >  wait on it to get access to the resource.  When the Sqlite operation is
> >  complete release the mutex and the next thread will have exclusive
> >  access to it.
> >
> >  If you use pthreads you can use read and write locks to get concurrency
> >  on reads.
> >
> >  To my mind syncing on a mutex is better and simpler than polling the
> >  resource using SQLITE_BUSY.
> >
> >
> >
> >  Alexander Batyrshin wrote:
> >  > So, you advice me, to implement synchronization inside my process by my 
> > self?
> >  >
> >  > On Thu, Apr 24, 2008 at 3:40 PM, John Stanton <[EMAIL PROTECTED]> wrote:
> >  >> You have a single shared resource, Sqlite, and you have to synchronize
> >  >>  access.  You can use the internal locking in Sqlite and use polling or
> >  >>  wait on a mutex or semaphore.
> >  >>
> >  >>
> >  >>  Alexander Batyrshin wrote:
> >  >>  >  Hello All,
> >  >>  >
> >  >>  > I am observing situation, that my concurrency process does not have
> >  >>  > access to SQLite database with equal probability.
> >  >>  >
> >  >>  > Here is example. I have N process that do work like this:
> >  >>  >
> >  >>  > while (1) {
> >  >>  > do_some_work(); // takes ~ 30 sec
> >  >>  > save_work_result_to_sqlite(); // takes ~ 1 sec
> >  >>  > }
> >  >>  >
> >  >>  > So, as you can see, these N process has concurrency access to SQLite 
> > database.
> >  >>  > In theory in worst case, save_work_result_to_sqlite() should NOT wait
> >  >>  > for access to database longer than N * 1 sec.
> >  >>  > But in practice, some process blocks on save_work_to_sqlite() more
> >  >>  > than N*2 sec and dies on my SQLITE_BUSY asserts :/
> >  >>  >
> >  >>  > So, I am wondering, is there any ideas how to avoid this?
> >  >>  >
> >  >>
> >  >>  ___
> >  >>  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
> >
>
>
>
> --
> Alexander Batyrshin aka bash
> bash = Biomechanica Artificial Sabotage Humanoid
> ___
>
> 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] Concurrency access to SQLite

[Alexander Batyrshin]

Oh... Nope, I am not using any thread-mechanism.
I am using simple processes (via fork). So synchronization should be
task for SQLite library.

But right now I am confused, because my processes do not blocks on
sqlite3_exec. They immediately report BUSY_TIMEOUT, without awaiting
for time set by sqlite3_busy_timeout.


On Thu, Apr 24, 2008 at 4:29 PM, John Stanton <[EMAIL PROTECTED]> wrote:
> If it is one process I would assign a mutex to the resource (Sqlite) and
>  wait on it to get access to the resource.  When the Sqlite operation is
>  complete release the mutex and the next thread will have exclusive
>  access to it.
>
>  If you use pthreads you can use read and write locks to get concurrency
>  on reads.
>
>  To my mind syncing on a mutex is better and simpler than polling the
>  resource using SQLITE_BUSY.
>
>
>
>  Alexander Batyrshin wrote:
>  > So, you advice me, to implement synchronization inside my process by my 
> self?
>  >
>  > On Thu, Apr 24, 2008 at 3:40 PM, John Stanton <[EMAIL PROTECTED]> wrote:
>  >> You have a single shared resource, Sqlite, and you have to synchronize
>  >>  access.  You can use the internal locking in Sqlite and use polling or
>  >>  wait on a mutex or semaphore.
>  >>
>  >>
>  >>  Alexander Batyrshin wrote:
>  >>  >  Hello All,
>  >>  >
>  >>  > I am observing situation, that my concurrency process does not have
>  >>  > access to SQLite database with equal probability.
>  >>  >
>  >>  > Here is example. I have N process that do work like this:
>  >>  >
>  >>  > while (1) {
>  >>  > do_some_work(); // takes ~ 30 sec
>  >>  > save_work_result_to_sqlite(); // takes ~ 1 sec
>  >>  > }
>  >>  >
>  >>  > So, as you can see, these N process has concurrency access to SQLite 
> database.
>  >>  > In theory in worst case, save_work_result_to_sqlite() should NOT wait
>  >>  > for access to database longer than N * 1 sec.
>  >>  > But in practice, some process blocks on save_work_to_sqlite() more
>  >>  > than N*2 sec and dies on my SQLITE_BUSY asserts :/
>  >>  >
>  >>  > So, I am wondering, is there any ideas how to avoid this?
>  >>  >
>  >>
>  >>  ___
>  >>  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
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency access to SQLite

[John Stanton]

If it is one process I would assign a mutex to the resource (Sqlite) and 
wait on it to get access to the resource.  When the Sqlite operation is 
complete release the mutex and the next thread will have exclusive 
access to it.

If you use pthreads you can use read and write locks to get concurrency 
on reads.

To my mind syncing on a mutex is better and simpler than polling the 
resource using SQLITE_BUSY.

Alexander Batyrshin wrote:
> So, you advice me, to implement synchronization inside my process by my self?
> 
> On Thu, Apr 24, 2008 at 3:40 PM, John Stanton <[EMAIL PROTECTED]> wrote:
>> You have a single shared resource, Sqlite, and you have to synchronize
>>  access.  You can use the internal locking in Sqlite and use polling or
>>  wait on a mutex or semaphore.
>>
>>
>>  Alexander Batyrshin wrote:
>>  >  Hello All,
>>  >
>>  > I am observing situation, that my concurrency process does not have
>>  > access to SQLite database with equal probability.
>>  >
>>  > Here is example. I have N process that do work like this:
>>  >
>>  > while (1) {
>>  > do_some_work(); // takes ~ 30 sec
>>  > save_work_result_to_sqlite(); // takes ~ 1 sec
>>  > }
>>  >
>>  > So, as you can see, these N process has concurrency access to SQLite 
>> database.
>>  > In theory in worst case, save_work_result_to_sqlite() should NOT wait
>>  > for access to database longer than N * 1 sec.
>>  > But in practice, some process blocks on save_work_to_sqlite() more
>>  > than N*2 sec and dies on my SQLITE_BUSY asserts :/
>>  >
>>  > So, I am wondering, is there any ideas how to avoid this?
>>  >
>>
>>  ___
>>  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] Concurrency access to SQLite

[Alexander Batyrshin]

So, you advice me, to implement synchronization inside my process by my self?

On Thu, Apr 24, 2008 at 3:40 PM, John Stanton <[EMAIL PROTECTED]> wrote:
> You have a single shared resource, Sqlite, and you have to synchronize
>  access.  You can use the internal locking in Sqlite and use polling or
>  wait on a mutex or semaphore.
>
>
>  Alexander Batyrshin wrote:
>  >  Hello All,
>  >
>  > I am observing situation, that my concurrency process does not have
>  > access to SQLite database with equal probability.
>  >
>  > Here is example. I have N process that do work like this:
>  >
>  > while (1) {
>  > do_some_work(); // takes ~ 30 sec
>  > save_work_result_to_sqlite(); // takes ~ 1 sec
>  > }
>  >
>  > So, as you can see, these N process has concurrency access to SQLite 
> database.
>  > In theory in worst case, save_work_result_to_sqlite() should NOT wait
>  > for access to database longer than N * 1 sec.
>  > But in practice, some process blocks on save_work_to_sqlite() more
>  > than N*2 sec and dies on my SQLITE_BUSY asserts :/
>  >
>  > So, I am wondering, is there any ideas how to avoid this?
>  >
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency access to SQLite

[John Stanton]

You have a single shared resource, Sqlite, and you have to synchronize 
access.  You can use the internal locking in Sqlite and use polling or 
wait on a mutex or semaphore.

Alexander Batyrshin wrote:
>  Hello All,
> 
> I am observing situation, that my concurrency process does not have
> access to SQLite database with equal probability.
> 
> Here is example. I have N process that do work like this:
> 
> while (1) {
> do_some_work(); // takes ~ 30 sec
> save_work_result_to_sqlite(); // takes ~ 1 sec
> }
> 
> So, as you can see, these N process has concurrency access to SQLite database.
> In theory in worst case, save_work_result_to_sqlite() should NOT wait
> for access to database longer than N * 1 sec.
> But in practice, some process blocks on save_work_to_sqlite() more
> than N*2 sec and dies on my SQLITE_BUSY asserts :/
> 
> So, I am wondering, is there any ideas how to avoid this?
> 

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

[sqlite] Concurrency access to SQLite

[Alexander Batyrshin]

 Hello All,

I am observing situation, that my concurrency process does not have
access to SQLite database with equal probability.

Here is example. I have N process that do work like this:

while (1) {
do_some_work(); // takes ~ 30 sec
save_work_result_to_sqlite(); // takes ~ 1 sec
}

So, as you can see, these N process has concurrency access to SQLite database.
In theory in worst case, save_work_result_to_sqlite() should NOT wait
for access to database longer than N * 1 sec.
But in practice, some process blocks on save_work_to_sqlite() more
than N*2 sec and dies on my SQLITE_BUSY asserts :/

So, I am wondering, is there any ideas how to avoid this?

-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrency

[Eduardo Morras]

At 20:58 01/06/2007, you wrote:
>On 6/1/07, Eduardo Morras <[EMAIL PROTECTED]> wrote:
>
>>If i remember well, sqlite uses two databases for metadata/schema
>
>Databases or tables? Could you be thinking of the sqlite_master table?

 You're right 1 db and 2 tables. Yes i'm thinking on sqlite_master table.

Thanks



   Usuario de FreeBSD+Xfce, OpenOffice y muchos mas OSS.
Microsoft declara que el OSS viola 235 patentes. Por favor, DENUNCIAME. 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Will Leshner]

On 6/1/07, Eduardo Morras <[EMAIL PROTECTED]> wrote:


If i remember well, sqlite uses two databases for metadata/schema


Databases or tables? Could you be thinking of the sqlite_master table?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Eduardo Morras]

At 19:24 01/06/2007, you wrote:
>Why you said less than 29?

SQLite has a soft limit of 10 databases and a hard limit of 32, you can change 
it at compile time. If i remember well, sqlite uses two databases for 
metadata/schema, so you get a max of 30 databases, you need another one as 
master db, so you get 29 free databases. 


---
Nunca mezclo pastillas con alcohol, ... las disuelvo. 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Marco Bambini]

Why you said less than 29?

---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/



On Jun 1, 2007, at 6:56 PM, Eduardo Morras wrote:


At 11:17 01/06/2007, you wrote:

Hi,

I am having a scenario where I have one reader/writer and many  
writer threads.
All writers are pretty basic (single INSERT INTO; some sort of a  
logging info

what a thread has done).

I believe I will receive many BUSY return codes and I don't like  
these
spinlock-like retries. The problem I am having with this design is  
that I would
like to complete the thread ASAP, so that I don't have many  
threads idling and

consuming resources of my embedded system.

I was thinking to either:

a. Use mutex/semaphore before writting to the database or

b. Have a (thread safe) list of INSERT INTO strings that every  
writer thread

populates and the main reader/writer thread later executes.

Is this a good approach? Does anyone have a better design? I don't  
want to use
other database, because I think Sqlite is great for an embedded  
system that I

am using.


How many threads have you?. If threads number is low (less than 29)  
you can use a database for each thread. Each one will have it's own  
file and no write lock problems. From time to time a simple sql  
query can get all data from those databases, write to the main one  
and delete the databases.


HTH


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Eduardo Morras]

At 11:17 01/06/2007, you wrote:
>Hi,
>
>I am having a scenario where I have one reader/writer and many writer threads.
>All writers are pretty basic (single INSERT INTO; some sort of a logging info
>what a thread has done).
>
>I believe I will receive many BUSY return codes and I don't like these
>spinlock-like retries. The problem I am having with this design is that I would
>like to complete the thread ASAP, so that I don't have many threads idling and
>consuming resources of my embedded system.
>
>I was thinking to either:
>
>a. Use mutex/semaphore before writting to the database or
>
>b. Have a (thread safe) list of INSERT INTO strings that every writer thread
>populates and the main reader/writer thread later executes.
>
>Is this a good approach? Does anyone have a better design? I don't want to use
>other database, because I think Sqlite is great for an embedded system that I
>am using.

How many threads have you?. If threads number is low (less than 29) you can use 
a database for each thread. Each one will have it's own file and no write lock 
problems. From time to time a simple sql query can get all data from those 
databases, write to the main one and delete the databases.

HTH



   Usuario de FreeBSD+Xfce, OpenOffice y muchos mas OSS.
Microsoft declara que el OSS viola 235 patentes. Por favor, DENUNCIAME. 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Doug Currie]

On Friday, June 01, 2007 Ian Frosst wrote: 

> For Windows, this is not the case with Automatic Reset events.  The system
> guarantees that only one thread waiting on the event is woken up (it keeps a
> queue): the others happily keep sleeping until the next setting of the
> event.

That may work for you, but has the possible drawback of starving a
thread... Windows doesn't guarantee that the woken thread is the one
at the head of the "queue." See, e.g.,
http://blogs.msdn.com/oldnewthing/archive/2006/03/13/550402.aspx
So, I never use PulseEvent or Automatic Reset events.

Of course, if you expect there to be intervals when there are no
waiting threads, and you can afford to wait for that interval for a
synchronization point, and you don't care the order in which the
inserts are performed, the Automatic Reset event might work.

e

> On 6/1/07, Doug Currie <[EMAIL PROTECTED]> wrote:
>>
>> On Friday, June 01, 2007 Ian Frosst wrote:
>>
>> > On the topic of a more efficient busy handler, one approach I considered
>> > was to implement an event which was signalled when a database unlock
>> > occurred.
>> > That way, the busy handler could just wait on the event (which is an
>> > efficient wait state), and be guaranteed of a wake up when the lock is
>> > released (the event would be signalled at this time.)  However, I wasn't
>> > at the time familiar enough with SQLite's innards to implement such a
>> > beast.
>> > Can anyone see any pitfalls to such an approach?
>>
>> The problems occur when multiple threads are waiting on the event;
>> they all wake up and compete for the resource again. For better
>> solutions, see: http://world.std.com/~jmhart/batons.htm

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Ken]

[EMAIL PROTECTED] wrote: Hi,

I am having a scenario where I have one reader/writer and many writer threads.
All writers are pretty basic (single INSERT INTO; some sort of a logging info
what a thread has done).

I believe I will receive many BUSY return codes and I don't like these
spinlock-like retries. The problem I am having with this design is that I would
like to complete the thread ASAP, so that I don't have many threads idling and
consuming resources of my embedded system.

I was thinking to either:

a. Use mutex/semaphore before writting to the database or

b. Have a (thread safe) list of INSERT INTO strings that every writer thread
populates and the main reader/writer thread later executes.

Is this a good approach? Does anyone have a better design? I don't want to use
other database, because I think Sqlite is great for an embedded system that I
am using.
___
Najhitrej¹i brezplaèni klicni dostop :: Varno in zanesljivo po internetu
Obi¹èite http://www.dialup386.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


 When you have multiple writes using sqlite you will need to perform a 
syncrhonization of some form.
 
 I would recommend using option "b" that you indicated. Create a thread that 
connects to the DB and handles all of you i/o to sqlite. That way you will 
never get a db locked or busy retry... 
 Take a look at src/test_server.c for an example implementation. For 
your case you really don't need to implement the "shared cache". Just create a 
simple db open internally to the thread. Have each client send its insert data 
into the "servers" incoming work queue.
 
 This will work as long as the "clients" don't need to wait or guarantee 
durability before proceeding. 
 
 

RE: [sqlite] Concurrency

[Samuel R. Neff]

Nice analogy, but in the case the cat really does have 9 lives (or many
more) 'cause with SQLITE_BUSY you can just retry and while retrying is a
performance penalty in my experience SQLITE_BUSY is a very rare occurrence.

All I'm saying is don't fix a perceived problem until you've tested to be
sure your perception is correct.

Sam


---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 01, 2007 9:40 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Concurrency

You make a very good point.  Someone called it the "cat running over the 
road" situation.  A cat runs across a busy road without looking by going 
as fast as possible and rarely does one get run over.  On the other hand 
roads are lined with dead armadillos.

If you synchronization logic is sound and you avoid polling or waits the 
faster your database runs the more concurrent transactions per second 
you will achieve.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Ian Frosst]

For Windows, this is not the case with Automatic Reset events.  The system
guarantees that only one thread waiting on the event is woken up (it keeps a
queue): the others happily keep sleeping until the next setting of the
event.

On 6/1/07, Doug Currie <[EMAIL PROTECTED]> wrote:


On Friday, June 01, 2007 Ian Frosst wrote:

> On the topic of a more efficient busy handler, one approach I considered
was
> to implement an event which was signalled when a database unlock
occurred.
> That way, the busy handler could just wait on the event (which is an
> efficient wait state), and be guaranteed of a wake up when the lock is
> released (the event would be signalled at this time.)  However, I wasn't
at
> the time familiar enough with SQLite's innards to implement such a
beast.
> Can anyone see any pitfalls to such an approach?

The problems occur when multiple threads are waiting on the event;
they all wake up and compete for the resource again. For better
solutions, see: http://world.std.com/~jmhart/batons.htm

e

--
Doug Currie
Londonderry, NH, USA



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Concurrency

[Doug Currie]

On Friday, June 01, 2007 Ian Frosst wrote: 

> On the topic of a more efficient busy handler, one approach I considered was
> to implement an event which was signalled when a database unlock occurred.
> That way, the busy handler could just wait on the event (which is an
> efficient wait state), and be guaranteed of a wake up when the lock is
> released (the event would be signalled at this time.)  However, I wasn't at
> the time familiar enough with SQLite's innards to implement such a beast.
> Can anyone see any pitfalls to such an approach?

The problems occur when multiple threads are waiting on the event;
they all wake up and compete for the resource again. For better
solutions, see: http://world.std.com/~jmhart/batons.htm

e

-- 
Doug Currie
Londonderry, NH, USA


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[John Stanton]

With other applications you can use semaphores for synchronization and 
achieve minimal latency and a low overhead.


If you have networked files you are dependent upon the file locking and 
on the effectiveness of the cross network file locking.  This does not 
necessarily work as expected and in particular notorious cases does not 
work at all.


If you want maximal performance across a nwetwork you need to implement 
a server to manage the database on one machine.


Israel Figueroa wrote:

What if the database is locked by another application, or by another box?

The driver should poll untill it get an "idle database" and then thow an
event... and then, we're polling again.

I'm coding some similar to the original post... I'm not that good with
threads yet, but I did foresee that even with my tiny inserts maybe some
moment I could get a Sqlite_busy. Is that bad just keep inserting until i
get a Sqlite_ok ? Assuming that is not a multiuser enviroment, just one app
accesing the database.

2007/6/1, Ian Frosst <[EMAIL PROTECTED]>:



On the topic of a more efficient busy handler, one approach I considered
was
to implement an event which was signalled when a database unlock 
occurred.

That way, the busy handler could just wait on the event (which is an
efficient wait state), and be guaranteed of a wake up when the lock is
released (the event would be signalled at this time.)  However, I wasn't
at
the time familiar enough with SQLite's innards to implement such a beast.
Can anyone see any pitfalls to such an approach?

Ian

On 6/1/07, John Stanton <[EMAIL PROTECTED]> wrote:
>
> Tom Briggs wrote:
> >
> >
> >>I don't want to use
> >>other database, because I think Sqlite is great for an
> >>embedded system that I
> >>am using.
> >
> >
> >I think that your own questions about concurrency prove this
> > incorrect.  If you need high concurrency and you don't like retries,
> > SQLite is not the database for you.
> >
> >-T
> >
>
> If you require ACID type data integrity and have a single disk there is
> no such thing as a "high concurrency database".  They all share a 
single

> disk resource in some way.  With Sqlite it is up to the designer to
> build in concurrency and that cna be done by single streaming.  Better
> performance is achieved by using a single database connection so that
> cache hits are maximized.
>
> If your design is such that you never get a busy then you have an
> effective allocation of your disk resource.  Using mutexes between
> threads and semaphores between processes gives you that capability.
>
> To my mind the only time you should use the busy logic is when you are
> working across a network with shared files.
>
>
>
>
- 


> To unsubscribe, send email to [EMAIL PROTECTED]
>
>
- 


>
>








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Israel Figueroa]

What if the database is locked by another application, or by another box?

The driver should poll untill it get an "idle database" and then thow an
event... and then, we're polling again.

I'm coding some similar to the original post... I'm not that good with
threads yet, but I did foresee that even with my tiny inserts maybe some
moment I could get a Sqlite_busy. Is that bad just keep inserting until i
get a Sqlite_ok ? Assuming that is not a multiuser enviroment, just one app
accesing the database.

2007/6/1, Ian Frosst <[EMAIL PROTECTED]>:


On the topic of a more efficient busy handler, one approach I considered
was
to implement an event which was signalled when a database unlock occurred.
That way, the busy handler could just wait on the event (which is an
efficient wait state), and be guaranteed of a wake up when the lock is
released (the event would be signalled at this time.)  However, I wasn't
at
the time familiar enough with SQLite's innards to implement such a beast.
Can anyone see any pitfalls to such an approach?

Ian

On 6/1/07, John Stanton <[EMAIL PROTECTED]> wrote:
>
> Tom Briggs wrote:
> >
> >
> >>I don't want to use
> >>other database, because I think Sqlite is great for an
> >>embedded system that I
> >>am using.
> >
> >
> >I think that your own questions about concurrency prove this
> > incorrect.  If you need high concurrency and you don't like retries,
> > SQLite is not the database for you.
> >
> >-T
> >
>
> If you require ACID type data integrity and have a single disk there is
> no such thing as a "high concurrency database".  They all share a single
> disk resource in some way.  With Sqlite it is up to the designer to
> build in concurrency and that cna be done by single streaming.  Better
> performance is achieved by using a single database connection so that
> cache hits are maximized.
>
> If your design is such that you never get a busy then you have an
> effective allocation of your disk resource.  Using mutexes between
> threads and semaphores between processes gives you that capability.
>
> To my mind the only time you should use the busy logic is when you are
> working across a network with shared files.
>
>
>
>
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
>
-
>
>





--
Thanks God

RE: [sqlite] Concurrency

[KKH]

// wrapper for sqlite3_prepare_v2 which retries
creating statements if the db returns SQLITE_BUSY or
SQLITE_LOCKED
int sql_prepare(sqlite3 *db, const char *sql,
sqlite3_stmt **ppStmt, int wait) {
#ifdef SQL_DEBUG
printf(sql);
printf("\n");
fflush(stdout);
#endif
int rc;
char looper[4] = {'|','/','-','\\'};
int looperc = 0;
int waited = 0;
while (1) {
rc = sqlite3_prepare_v2(db,sql,-1,ppStmt,NULL);
if (rc == SQLITE_LOCKED || rc == SQLITE_BUSY) {
if (wait != 0) {
fprintf(stdout,"Database is locked or busy.
Waiting %is ... %1c\r", ++waited,
looper[looperc]);
fflush(stdout);
wait--;
looperc = ++looperc % sizeof(looper);
sleep(1);
} else {
fprintf(stderr,"Database was locked or busy 
while
creating statement. I've given up.\n");
return rc;
}
} else {
if (waited != 0) printf("\n\n");
return rc;
}
}
}
--- [EMAIL PROTECTED] schrieb:

> Hi,
> 
> I am having a scenario where I have one
> reader/writer and many writer threads.
> All writers are pretty basic (single INSERT INTO;
> some sort of a logging info
> what a thread has done).
> 
> I believe I will receive many BUSY return codes and
> I don't like these
> spinlock-like retries. The problem I am having with
> this design is that I would
> like to complete the thread ASAP, so that I don't
> have many threads idling and
> consuming resources of my embedded system.
> 
> I was thinking to either:
> 
> a. Use mutex/semaphore before writting to the
> database or
> 
> b. Have a (thread safe) list of INSERT INTO strings
> that every writer thread
> populates and the main reader/writer thread later
> executes.
> 
> Is this a good approach? Does anyone have a better
> design? I don't want to use
> other database, because I think Sqlite is great for
> an embedded system that I
> am using.
>
___
> Najhitrej¹i brezplaèni klicni dostop :: Varno in
> zanesljivo po internetu
> Obi¹èite http://www.dialup386.com/
> 
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
> 
> 




___ 
Telefonate ohne weitere Kosten vom PC zum PC: http://messenger.yahoo.de

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[John Stanton]

Samuel R. Neff wrote:

If option (b), using a single thread for writing and a multi-threaded write
queue works in your situation, then that would probably provide best
concurrency and performance.  The only downside to this is the delayed
writes mean you don't as easily get feedback to the original writer if a
write fails (bad data for example).  You could build delayed
feedback/callback into the system, just depends on your application
architecture.

Also, I would strongly suggest doing some actual testing and confirming
there is a bottleneck without any custom code to increase concurrency before
programming workarounds for an expected problem.  What we've found is that
SQLite is so much faster than other databases (MSSQL in particular) that
concurrency is greatly improved simply because each write is faster and thus
the time the db is locked is less even though the whole db is locked.

HTH,

Sam

---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 01, 2007 5:18 AM

To: sqlite-users@sqlite.org
Subject: [sqlite] Concurrency

Hi,

I am having a scenario where I have one reader/writer and many writer
threads.
All writers are pretty basic (single INSERT INTO; some sort of a logging
info
what a thread has done).

I believe I will receive many BUSY return codes and I don't like these
spinlock-like retries. The problem I am having with this design is that I
would
like to complete the thread ASAP, so that I don't have many threads idling
and
consuming resources of my embedded system.

I was thinking to either:

a. Use mutex/semaphore before writting to the database or

b. Have a (thread safe) list of INSERT INTO strings that every writer thread
populates and the main reader/writer thread later executes.

Is this a good approach? Does anyone have a better design? I don't want to
use
other database, because I think Sqlite is great for an embedded system that
I
am using.



You make a very good point.  Someone called it the "cat running over the 
road" situation.  A cat runs across a busy road without looking by going 
as fast as possible and rarely does one get run over.  On the other hand 
roads are lined with dead armadillos.


If you synchronization logic is sound and you avoid polling or waits the 
faster your database runs the more concurrent transactions per second 
you will achieve.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[Ian Frosst]

On the topic of a more efficient busy handler, one approach I considered was
to implement an event which was signalled when a database unlock occurred.
That way, the busy handler could just wait on the event (which is an
efficient wait state), and be guaranteed of a wake up when the lock is
released (the event would be signalled at this time.)  However, I wasn't at
the time familiar enough with SQLite's innards to implement such a beast.
Can anyone see any pitfalls to such an approach?

Ian

On 6/1/07, John Stanton <[EMAIL PROTECTED]> wrote:


Tom Briggs wrote:
>
>
>>I don't want to use
>>other database, because I think Sqlite is great for an
>>embedded system that I
>>am using.
>
>
>I think that your own questions about concurrency prove this
> incorrect.  If you need high concurrency and you don't like retries,
> SQLite is not the database for you.
>
>-T
>

If you require ACID type data integrity and have a single disk there is
no such thing as a "high concurrency database".  They all share a single
disk resource in some way.  With Sqlite it is up to the designer to
build in concurrency and that cna be done by single streaming.  Better
performance is achieved by using a single database connection so that
cache hits are maximized.

If your design is such that you never get a busy then you have an
effective allocation of your disk resource.  Using mutexes between
threads and semaphores between processes gives you that capability.

To my mind the only time you should use the busy logic is when you are
working across a network with shared files.



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

RE: [sqlite] Concurrency

[Tom Briggs]

 
> If you require ACID type data integrity and have a single 
> disk there is 
> no such thing as a "high concurrency database".  They all 

   Then don't blame me if he's asking the wrong questions. :)

   -T

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Concurrency

[Tom Briggs]

   I have no suggestions (I'm not an embedded systems guy), but your initial 
comment implied that there were other options.  If there aren't then your 
original statement is invalid and there's nothing to discuss. :)

   -T 

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Friday, June 01, 2007 8:27 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Concurrency
> 
> Tom,
> 
> > > I don't want to use
> > > other database, because I think Sqlite is great for an
> > > embedded system that I
> > > am using.
> >
> >I think that your own questions about concurrency prove this
> > incorrect.  If you need high concurrency and you don't like retries,
> > SQLite is not the database for you.
> 
> Then what database would you suggest instead Sqlite for use 
> in embedded (low
> memory, cpu: 200 Mhz) system?
> __
> _
> Najhitrejši brezplačni klicni dostop :: Varno in zanesljivo 
> po internetu
> Obiščite http://www.dialup386.com/
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Concurrency

[Samuel R. Neff]

If option (b), using a single thread for writing and a multi-threaded write
queue works in your situation, then that would probably provide best
concurrency and performance.  The only downside to this is the delayed
writes mean you don't as easily get feedback to the original writer if a
write fails (bad data for example).  You could build delayed
feedback/callback into the system, just depends on your application
architecture.

Also, I would strongly suggest doing some actual testing and confirming
there is a bottleneck without any custom code to increase concurrency before
programming workarounds for an expected problem.  What we've found is that
SQLite is so much faster than other databases (MSSQL in particular) that
concurrency is greatly improved simply because each write is faster and thus
the time the db is locked is less even though the whole db is locked.

HTH,

Sam

---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, June 01, 2007 5:18 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Concurrency

Hi,

I am having a scenario where I have one reader/writer and many writer
threads.
All writers are pretty basic (single INSERT INTO; some sort of a logging
info
what a thread has done).

I believe I will receive many BUSY return codes and I don't like these
spinlock-like retries. The problem I am having with this design is that I
would
like to complete the thread ASAP, so that I don't have many threads idling
and
consuming resources of my embedded system.

I was thinking to either:

a. Use mutex/semaphore before writting to the database or

b. Have a (thread safe) list of INSERT INTO strings that every writer thread
populates and the main reader/writer thread later executes.

Is this a good approach? Does anyone have a better design? I don't want to
use
other database, because I think Sqlite is great for an embedded system that
I
am using.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[John Stanton]

Tom Briggs wrote:
 


I don't want to use
other database, because I think Sqlite is great for an 
embedded system that I

am using.



   I think that your own questions about concurrency prove this
incorrect.  If you need high concurrency and you don't like retries,
SQLite is not the database for you.

   -T



If you require ACID type data integrity and have a single disk there is 
no such thing as a "high concurrency database".  They all share a single 
disk resource in some way.  With Sqlite it is up to the designer to 
build in concurrency and that cna be done by single streaming.  Better 
performance is achieved by using a single database connection so that 
cache hits are maximized.


If your design is such that you never get a busy then you have an 
effective allocation of your disk resource.  Using mutexes between 
threads and semaphores between processes gives you that capability.


To my mind the only time you should use the busy logic is when you are 
working across a network with shared files.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Concurrency

[John Stanton]

We find that synchronizing access to database writes using a mutex works 
well.  You could think of implementing read and write locks using the 
thread primitives and achieve a better result.


If you do poll to resolve busy checks a spinlock is certainly a bad 
idea.  When we use that approach we will yield the thread after each busy.


[EMAIL PROTECTED] wrote:

Hi,

I am having a scenario where I have one reader/writer and many writer threads.
All writers are pretty basic (single INSERT INTO; some sort of a logging info
what a thread has done).

I believe I will receive many BUSY return codes and I don't like these
spinlock-like retries. The problem I am having with this design is that I would
like to complete the thread ASAP, so that I don't have many threads idling and
consuming resources of my embedded system.

I was thinking to either:

a. Use mutex/semaphore before writting to the database or

b. Have a (thread safe) list of INSERT INTO strings that every writer thread
populates and the main reader/writer thread later executes.

Is this a good approach? Does anyone have a better design? I don't want to use
other database, because I think Sqlite is great for an embedded system that I
am using.
___
Najhitrejši brezplačni klicni dostop :: Varno in zanesljivo po internetu
Obiščite http://www.dialup386.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-