Re: [sqlite] Insert while select
I was way off on the version. We are using 3.3.17. Sorry to bother
you about behavior from Apr 25, 2007. I am simply curious to
understand this behavior.
On Wed, Jan 26, 2011 at 1:33 PM, Richard Hipp wrote:
> On Wed, Jan 26, 2011 at 1:30 PM, Igor Tandetnik wrote:
>
>> On 1/26/2011 11:09 AM, Rich Rattanni wrote:
>> > I am helping someone write an application that utilizes SQLite. The
>> > following code is giving us grief (sqlite lib version 3.5.ish -
>> > Windows XP):
>> >
>> > sqlite3_prepare("select some rows")
>> > while (sqlite3_step() == SQLITE_ROW)
>> > {
>> > // Do some calc on multiple rows, and occasinally
>> > sqlite3_exec("Insert calculated data into the same table from
>> > which we are reading")
>> > }
>>
>> If I recall correctly, this is not allowed in SQLite 3.5.*. This became
>> supported in 3.6.x (for some x I don't remember at the moment).
>>
>> > I had a look at http://www.sqlite.org/lockingv3.html.
>>
>> The documentation describes the current behavior. Older versions often
>> behave differently. 3.5 series are 2.5 years old, a lot of progress has
>> been made since then.
>>
>
> 2.5 calendar years equals 17.5 internet years, right? :-)
>
>
>> --
>> Igor Tandetnik
>>
>> ___
>> 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] Insert while select
On Wed, Jan 26, 2011 at 1:30 PM, Igor Tandetnik wrote:
> On 1/26/2011 11:09 AM, Rich Rattanni wrote:
> > I am helping someone write an application that utilizes SQLite. The
> > following code is giving us grief (sqlite lib version 3.5.ish -
> > Windows XP):
> >
> > sqlite3_prepare("select some rows")
> > while (sqlite3_step() == SQLITE_ROW)
> > {
> > // Do some calc on multiple rows, and occasinally
> > sqlite3_exec("Insert calculated data into the same table from
> > which we are reading")
> > }
>
> If I recall correctly, this is not allowed in SQLite 3.5.*. This became
> supported in 3.6.x (for some x I don't remember at the moment).
>
> > I had a look at http://www.sqlite.org/lockingv3.html.
>
> The documentation describes the current behavior. Older versions often
> behave differently. 3.5 series are 2.5 years old, a lot of progress has
> been made since then.
>
2.5 calendar years equals 17.5 internet years, right? :-)
> --
> Igor Tandetnik
>
> ___
> 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] Insert while select
On 1/26/2011 11:09 AM, Rich Rattanni wrote:
> I am helping someone write an application that utilizes SQLite. The
> following code is giving us grief (sqlite lib version 3.5.ish -
> Windows XP):
>
> sqlite3_prepare("select some rows")
> while (sqlite3_step() == SQLITE_ROW)
> {
> // Do some calc on multiple rows, and occasinally
> sqlite3_exec("Insert calculated data into the same table from
> which we are reading")
> }
If I recall correctly, this is not allowed in SQLite 3.5.*. This became
supported in 3.6.x (for some x I don't remember at the moment).
> I had a look at http://www.sqlite.org/lockingv3.html.
The documentation describes the current behavior. Older versions often
behave differently. 3.5 series are 2.5 years old, a lot of progress has
been made since then.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert while select
On 26 Jan 2011, at 4:09pm, Rich Rattanni wrote:
> sqlite3_prepare("select some rows")
> while (sqlite3_step() == SQLITE_ROW)
> {
>// Do some calc on multiple rows, and occasinally
>sqlite3_exec("Insert calculated data into the same table from
> which we are reading")
> }
> sqlite3_finalize()
Is there a chance that the INSERTs that he's doing would interfere in any way
with the SELECT ? In other words, if he executed an indentical SELECT after
doing all the INSERTs, would be definitely get the same results as he did
before ?
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Insert while select
I am helping someone write an application that utilizes SQLite. The
following code is giving us grief (sqlite lib version 3.5.ish -
Windows XP):
sqlite3_prepare("select some rows")
while (sqlite3_step() == SQLITE_ROW)
{
// Do some calc on multiple rows, and occasinally
sqlite3_exec("Insert calculated data into the same table from
which we are reading")
}
sqlite3_finalize()
I had a look at http://www.sqlite.org/lockingv3.html. After a quick
read, I would think that the prepare or step put a SHARED lock on the
database.
Now it is sqlite3_exec's turn to run, so he may acquire at most a
PENDING lock? I would come to this conclusion since the select is
still active
so the SHARED lock exists. Then the sqlite3_finalize() allows the
SHARED lock to be released, which then allows the PENDING to promote
to
EXCLUSIVE and the data to finally commit?
I am kind of throwing out my analysis and looking for a yes or no. My
final statement for which I request validation... So unless I wrap my
data
in explicit begin/commits the data is not, in fact guaranteed to be
safely on disk? If my program were to crash before the
sqlite3_finalize then
is the data from the selects lost?
One last question: The locking documentation says that at some time
some cache may fill up which then causes the process to wish to commit
data.
If the above while loop generated enough insert statements to fill up
this cache, would the this loop deadlock? A better way to ask this:
Is nesting a
insert statement in a select loop dangerous?
Thanks for taking the time to read this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
