Re: [sqlite] concurrent writes and reads to /from DB

[Simon Slavin]

On 5 Nov 2011, at 3:32am, swamir wrote:

> Will a busy_timeout setting for all connections and making write
> transactions as "begin immediate"  handle the situation ?

First, just try just setting a timeout and see if that fixes the problem.

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

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

[sqlite] concurrent writes and reads to /from DB

[swamir]

Have a database and an application wherein , concurrent writes and reads
to/from the db happen (each DB connection is independent , autocommit mode
and no SQLITE_BUSY handler / timeout ).

I see "Database is locked" message.

Is there a good practice /setting for such high rate of concurrent writes
with reads ?
I understand that multiple readers with single writer at any time t is the
model which sqlite has with various locks/states (unlocked, pending , shared
, reserved , exclusive).

Will a busy_timeout setting for all connections and making write
transactions as "begin immediate"  handle the situation ?

Any thoughts appreciated .



-- 
View this message in context: 
http://old.nabble.com/concurrent-writes-and-reads-to--from-DB-tp32784852p32784852.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] Lock and transaction

[Paxdo Presse]

Great. Thanks to both.

(and sorry for my bad english)

Le 5 nov. 2011 à 00:05, Petite Abeille a écrit :

> 
> On Nov 4, 2011, at 11:59 PM, Paxdo Presse wrote:
> 
>> Are we sure that another process is not going to create another row between 
>> my "INSERT" and "SELECT LAST ROWID"? 
> 
> yes
> 
>> The "LAST ROWID" is it for sure the id of "INSERT INTO" of the transaction?
> 
> yes
> 
> ___
> 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] Lock and transaction

[Petite Abeille]

On Nov 4, 2011, at 11:59 PM, Paxdo Presse wrote:

> Are we sure that another process is not going to create another row between 
> my "INSERT" and "SELECT LAST ROWID"? 

yes

> The "LAST ROWID" is it for sure the id of "INSERT INTO" of the transaction?

yes

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

Re: [sqlite] Lock and transaction

[Richard Hipp]

On Fri, Nov 4, 2011 at 6:59 PM, Paxdo Presse  wrote:

>
> Hello,
>
> a question from beginner please :
>
> In this transaction (with wal-mode) :
>
>
> BEGIN TRANSACTION
>
> INSERT INTO
>
> SELECT last_insert_rowid()
>
> COMMIT
>
>
> Are we sure that another process is not going to create another row
> between my "INSERT" and "SELECT LAST ROWID"?
>

Yes.  Isolation in SQLite is SERIALIZABLE.



> The "LAST ROWID" is it for sure the id of "INSERT INTO" of the transaction?
>

Yes.  Actually, this is true even without the BEGIN...COMMIT.  The
last_insert_rowid() is the rowid of the most recent insert on the same
database connection.



>
> In fact, is the lock that starts with INSERT is set to COMMIT? Or the
> write lock is it only when the INSERT? (so no lock between INSERT and
> COMMIT)
>

I do not exactly understand the question, so I'm guessing:  The lock that
is acquire at INSERT is held until COMMIT.



>
> Thank you
>
> olivier
> ___
> 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] Lock and transaction

[Paxdo Presse]

Hello,

a question from beginner please :

In this transaction (with wal-mode) :


BEGIN TRANSACTION

INSERT INTO

SELECT last_insert_rowid()

COMMIT


Are we sure that another process is not going to create another row between my 
"INSERT" and "SELECT LAST ROWID"? 
The "LAST ROWID" is it for sure the id of "INSERT INTO" of the transaction?

In fact, is the lock that starts with INSERT is set to COMMIT? Or the write 
lock is it only when the INSERT? (so no lock between INSERT and COMMIT)

Thank you

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

Re: [sqlite] iPhone iOS 3.1.3 Sqlite3 Crashes Unless Compiled Without Optimizations

[Price,Ray]

Interesting, I'll give that a try, thanks.  Good to know I'm not going crazy... 
my worry with this kind of thing is always that my memory management is not 
bulletproof and is having a knock-on effect somewhere.

Thanks
Ray

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of James Berry
Sent: Thursday, November 03, 2011 3:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] iPhone iOS 3.1.3 Sqlite3 Crashes Unless Compiled Without 
Optimizations


On Nov 3, 2011, at 6:19 AM, James Berry wrote:

> I've seen this same problem. It crashes seemingly due to bugs in llvm-clang 
> when compiled for arm6 if, as you say, optimizations at any level are turned 
> on. I've worked around this issue by turning off optimizations for arm6. 
> Sqlite3, by the way, is not the only bit of my iOS app that encounters 
> problems with that arm6 optimization combination. arm6, fortunately, is 
> required by fewer and fewer devices these days.
> 
> Report bugs to apple, if you care.

Btw, somebody mentioned to me this morning that these issues can be worked 
around by disabling thumb mode when compiling for arm6 (as an alternative to 
disabling optimizations). That would be the -mno-thumb switch to the compiler. 
Note that these issues only manifest when compiling using the llvm/clang 
compiler.

James

> 
> 
> On Nov 3, 2011, at 1:39 AM, Price,Ray wrote:
> 
>> Hi All,
>> 
>> I'm having a VERY odd problem with Sqlite at the moment.  I have an 
>> application that works fine and has been working find for over a year, but 
>> since updating to xcode 4 and the ios 5 SDK Sqlite crashes regularly, but 
>> ONLY on older devices still running iOS 3.1.3.
>> 
>> However, if I compile the sqlite3.c module WITHOUT optimizations, the crash 
>> goes away, but this is obviously FAR from ideal.
>> 
>> Has anyone else experienced this?  Is there anything I can do to narrow down 
>> the problem, or is there anyone I could report this to?
>> 
>> Thanks
>> Ray
>> 
>> 
>> 
>> 
>> This e-mail message, including any attachments, is for the sole use of the 
>> person to whom it has been sent, and may contain information that is 
>> confidential or legally protected. If you are not the intended recipient or 
>> have received this message in error, you are not authorized to copy, 
>> distribute, or otherwise use this message or its attachments. Please notify 
>> the sender immediately by return e-mail and permanently delete this message 
>> and any attachments. Gartner makes no warranty that this e-mail is error or 
>> virus free.
>> ___
>> 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] Using "sub-select" to return limit

[Don V Nielsen]

Thanks.  I was afraid of that.  I got out my old Data Base Systems books
and was trying to figure out what I forgot.  Turns out I didn't forget it.
 It's just not possible.

Thanks again.  Off to coding.

On Thu, Nov 3, 2011 at 9:08 PM, Pavel Ivanov  wrote:

> > What I ultimately want to do is iterate through a table that contains
> the> limit and use that value to select all matching values from another
> table,> limiting the number of records selected from the group to that
> defined by> limit.
> There's no way to do such thing using only SQL. You have to select
> data from your table of limits into your programming language, iterate
> through results and for each row issue separate select statement with
> appropriate limit (which will be a constant not a nested select
> query).
>
>
> Pavel
>
>
> On Thu, Nov 3, 2011 at 5:23 PM, Don V Nielsen 
> wrote:
> > Given the following, I get an error that f.zip does not exist.
>  Obviously,
> > I am mentally missing something contextually, but I'm not getting it.
> >  Would someone work through the scope of things in this select.
> >
> > select p.*,pr.rowid from pool_wi as p
> > inner join add_priorities as pr on pr.prty = p.prty
> > where p.zip = '53005' and p.crrt = 'C022'
> > order by pr.rowid
> > limit (
> >  select f.need from seg_02_final_view as f where f.zip = p.zip and f.crrt
> > = p.crrt
> > )
> >
> > What I ultimately want to do is iterate through a table that contains the
> > limit and use that value to select all matching values from another
> table,
> > limiting the number of records selected from the group to that defined by
> > limit.  For example, a table would hold two rows of zip, route and limit,
> > [53005,C020,1] & [53005,C022,2].  I want to use the zip and route to
> select
> > all matching records from another table, but limit the result of the
> > sub-select to the qty of records as defined by limit.
> >
> > Thanks for your time and consideration.
> > dvn
> > ___
> > 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] [patch] Re: Potential bug: "insert into X select * from Y" ignores the "ON CONFLICT REPLACE" conflict-clause

[Yuriy Kaminskiy]

Yuriy Kaminskiy wrote:
> Yuriy Kaminskiy wrote:
>> David wrote:
>>> Simon L wrote 2011-10-25 06:20:
 To reproduce this problem, enter the following 5 SQL statements at the
 SQLite command line.

 create table X(id INTEGER primary key ON CONFLICT REPLACE);
 create table Y(id INTEGER primary key ON CONFLICT REPLACE);
 insert into X values (1);
 insert into Y select * from X;
 insert into Y select * from X;


 When I tried to run the last SQL statement twice,  SQLite produced the
 following error message.
 Error: PRIMARY KEY must be unique


 Is this a bug? Please advise. Thank you.
> 
>>> This certainly looks like a bug. I got a constraint failure when I tried
>>> it in sqlite 3.7.8.
>>>
>>> But it works fine when you state the column name explicitly in the
>>> select clause.
>>>
>>> Like this:
>>>
>>> create table X(id INTEGER primary key ON CONFLICT REPLACE);
>>> create table Y(id INTEGER primary key ON CONFLICT REPLACE);
>>> insert into X values (1);
>>> insert into Y select id from X;
>>> insert into Y select id from X;
>>>
>>> I recall the "INSERT INTO ... SELECT * ..." had a bug related to foreign
>>> key checks,
>>> which was reported on the mailing list earlier this year:
>>>
>>> http://www.sqlite.org/src/tktview?name=6284df89de
>>>
>>> Hopefully, a member of the sqlite dev team will acknowledge this bug soon.
>> Thanks for pointer; root cause, indeed, transfer optimization (it ignores 
>> table
>> INTEGER PRIMARY KEY's ON CONFLICT clause, fix was easy, patch below (use 
>> table's
>> ON CONFLICT clause by default; falls back to regular transfer if destination
>> table is not empty and we cannot handle ON CONFLICT resolution);
>>
>> Index: sqlite3-3.7.8/src/insert.c
>> ===
>> --- sqlite3-3.7.8.orig/src/insert.c  2011-10-25 15:20:26.0 +0400
>> +++ sqlite3-3.7.8/src/insert.c   2011-10-25 15:54:54.0 +0400
> 
> Ping.

Okey, I've noticed
http://www.sqlite.org/src/info/6f9898db7f

Won't that result in performance regression in VACUUM? [only on tables with
INTEGER PRIMARY KEY ON REPLACE xxx, obviously; so not *terrible* big deal - but
still]
When we insert into empty table, ON CONFLICT will never trigger, so we can
safely use optimized xfer.

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

Re: [sqlite] Slow INDEX

[Paul Corke]

> If you could use DTrace you could really find out, but since we have

How about something like sysinternals diskmon?

http://technet.microsoft.com/en-us/sysinternals/bb896646

That should give you (OP) some indication of what disk activity
is going on.

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

Re: [sqlite] status of unqlspec / sqlite

[Alek Paunov]

Hi,

Recent comment on the topic from unql mailing list:

https://groups.google.com/forum/#!msg/unql/dVc_cM1ZGw8/3QHE1_MIqRQJ

On 04.11.2011 10:50, sqlite-us...@h-rd.org wrote:

Hi,

some time ago Richard was involved in http://www.unqlspec.org/ . Is that
still going on? I am quite interested in a backend for sqlite.



Sqlite backend for UNQL frontend or new backend for VDBE in sqlite ?


thanks,

___
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] status of unqlspec / sqlite

[sqlite-us...@h-rd.org]

Hi,

some time ago Richard was involved in http://www.unqlspec.org/ .  Is  
that still going on?  I am quite interested in a backend for sqlite.


thanks,

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