Re: [sqlite] [EXTERNAL] Re: database locked on select

[Keith Medcalf]

In this case it makes no real difference.  The select on the connection will 
start a "read" transaction and the update on that connection will upgrade the 
transaction to a "write" transaction.  The transaction will complete when both 
the select and the update(s) are complete and the select finalized.

You might want to start a transaction with "BEGIN IMMEDIATE" before the select 
since that will tell SQLite3 that you intend to "write" (update) on the 
connection rather than praying that the later lock upgrade is successful, and 
do a COMMIT at the end of the whole select/update procedure to commit the 
changes to the database.

The only caveat, of course, with only using one connection is that changes made 
by the "update" are visible to the "select" so it is possible that you "update" 
what is being selected in the middle of the select ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Torsten Curdt
>Sent: Wednesday, 30 May, 2018 02:34
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] [EXTERNAL] Re: database locked on select
>
>> Do the select and updates run inside a explicit transaction or they
>> run in individual implicit transactions?
>>
>
>implicit - does that make a big difference in this case?
>
>
>If you really want a single query you could write something like:
>>
>> WITH data(id, c1, c2 /*, ... */) AS (VALUES
>> (123, 'abc', 'xyz' /*, ... */),
>> (456, 'xxx', 'yyy' /*, ... */),
>> (789, 'xyz', 'xyz' /*, ... */)
>> /*  ...  */
>> ) UPDATE tab
>> SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE
>data.id =
>> tab.id)
>> WHERE id IN (SELECT id FROM data);
>>
>>
>But for that again means all the data (or the single query) needs to
>be
>built up in memory.
>
>cheers,
>Torsten
>___
>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] [EXTERNAL] Re: database locked on select

[Paul Sanderson]

If you are doing each update in a separate transaction it will be much
slower than wrapping them in a single transaction.

See the faq here, it refers to inserts but updates will be the same.

http://sqlite.org/faq.html#q19


Cheers
Paul


On Wed, 30 May 2018 at 09:34, Torsten Curdt  wrote:

> > Do the select and updates run inside a explicit transaction or they
> > run in individual implicit transactions?
> >
>
> implicit - does that make a big difference in this case?
>
>
> If you really want a single query you could write something like:
> >
> > WITH data(id, c1, c2 /*, ... */) AS (VALUES
> > (123, 'abc', 'xyz' /*, ... */),
> > (456, 'xxx', 'yyy' /*, ... */),
> > (789, 'xyz', 'xyz' /*, ... */)
> > /*  ...  */
> > ) UPDATE tab
> > SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE data.id =
> > tab.id)
> > WHERE id IN (SELECT id FROM data);
> >
> >
> But for that again means all the data (or the single query) needs to be
> built up in memory.
>
> cheers,
> Torsten
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Paul
www.sandersonforensics.com
SQLite Forensics Book 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Torsten Curdt]

> Do the select and updates run inside a explicit transaction or they
> run in individual implicit transactions?
>

implicit - does that make a big difference in this case?


If you really want a single query you could write something like:
>
> WITH data(id, c1, c2 /*, ... */) AS (VALUES
> (123, 'abc', 'xyz' /*, ... */),
> (456, 'xxx', 'yyy' /*, ... */),
> (789, 'xyz', 'xyz' /*, ... */)
> /*  ...  */
> ) UPDATE tab
> SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE data.id =
> tab.id)
> WHERE id IN (SELECT id FROM data);
>
>
But for that again means all the data (or the single query) needs to be
built up in memory.

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

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Abroży Nieprzełoży]

Do the select and updates run inside a explicit transaction or they
run in individual implicit transactions?

pseudocode:
-
exec(BEGIN);// <- Do you have this?

st_sel = prepare(SELECT ...);
st_upd = prepare(UPDATE tab SET c1=?1, c2=?2, WHERE id=?3);

while(step(st_sel) == SQLITE_ROW)
{
do_something(...);
bind(st_upd, ...);
step(st_upd);
reset(st_upd);
}

finalize(st_upd);
finalize(st_sel);

exec(COMMIT);// <- and this?
-



>> One technique I have used in a similar situation was to write all my
>> UPDATE commands to a long text buffer.  This was in an unusual situation
>> where I needed to get the SELECT done as quickly as possible to avoid
>> locking up lab equipment.  So the software figured out all the UPDATE
>> commands and concatenated them in a text variable:
>>
>> UPDATE for_row_1;UPDATE for_row_2;UPDATE for_row_3;...
Each update is prepared and executed separately.
If you really want a single query you could write something like:

WITH data(id, c1, c2 /*, ... */) AS (VALUES
(123, 'abc', 'xyz' /*, ... */),
(456, 'xxx', 'yyy' /*, ... */),
(789, 'xyz', 'xyz' /*, ... */)
/*  ...  */
) UPDATE tab
SET (c1, c2 /*, ... */) = (SELECT c1, c2 /*, ... */ WHERE data.id = tab.id)
WHERE id IN (SELECT id FROM data);


2018-05-28 21:32 GMT+02:00, Torsten Curdt :
> Yes, manually buffering the resultset or buffering the updates is of course
> a possible workaround.
> But I would like to avoid that as much as possible.
>
> Another approach is to use limit/offset and then page through the resultset
> to control the amount of buffering needed.
> But this just feels like a lot of complexity for such simple task.
>
> Anyway - since WAL mode seems to work I will stick with that for now.
>
> cheers,
> Torsten
>
> On Mon, May 28, 2018 at 9:10 PM Simon Slavin  wrote:
>
>> On 28 May 2018, at 7:56pm, Torsten Curdt  wrote:
>>
>> > Just to clarify: I have a single thread - so intermixing the stepping
>> > through a resultset and doing an update requires WAL mode but should be
>> > fine. Correct?
>>
>> Yes, this should work fine.  Obviously, one thread is not going to be
>> trying to do two database accesses at the same time, especially since your
>> software design uses the result from one SELECT row in order to figure out
>> what UPDATE to issue.
>>
>> You could, course, build up an array of pairs in memory while doing the
>> SELECT, then consult the array to create all the UPDATE commands once you
>> have finalized the SELECT.  If you do do this don't forget to surround the
>> UPDATE commands with BEGIN;...COMMIT; .
>>
>> One technique I have used in a similar situation was to write all my
>> UPDATE commands to a long text buffer.  This was in an unusual situation
>> where I needed to get the SELECT done as quickly as possible to avoid
>> locking up lab equipment.  So the software figured out all the UPDATE
>> commands and concatenated them in a text variable:
>>
>> UPDATE for_row_1;UPDATE for_row_2;UPDATE for_row_3;...
>>
>> The buffer could get as big as half a megabyte or so.  Then I
>> sqlite3_reset() the SELECT command.  Then I submit the entire piece of
>> text
>> as one long parameter to sqlite3_exec().  Worked perfectly, very quickly,
>> and didn't take up much more memory than storing the parameters in an
>> array.
>>
>> Simon.
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Torsten Curdt]

Yes, manually buffering the resultset or buffering the updates is of course
a possible workaround.
But I would like to avoid that as much as possible.

Another approach is to use limit/offset and then page through the resultset
to control the amount of buffering needed.
But this just feels like a lot of complexity for such simple task.

Anyway - since WAL mode seems to work I will stick with that for now.

cheers,
Torsten

On Mon, May 28, 2018 at 9:10 PM Simon Slavin  wrote:

> On 28 May 2018, at 7:56pm, Torsten Curdt  wrote:
>
> > Just to clarify: I have a single thread - so intermixing the stepping
> > through a resultset and doing an update requires WAL mode but should be
> > fine. Correct?
>
> Yes, this should work fine.  Obviously, one thread is not going to be
> trying to do two database accesses at the same time, especially since your
> software design uses the result from one SELECT row in order to figure out
> what UPDATE to issue.
>
> You could, course, build up an array of pairs in memory while doing the
> SELECT, then consult the array to create all the UPDATE commands once you
> have finalized the SELECT.  If you do do this don't forget to surround the
> UPDATE commands with BEGIN;...COMMIT; .
>
> One technique I have used in a similar situation was to write all my
> UPDATE commands to a long text buffer.  This was in an unusual situation
> where I needed to get the SELECT done as quickly as possible to avoid
> locking up lab equipment.  So the software figured out all the UPDATE
> commands and concatenated them in a text variable:
>
> UPDATE for_row_1;UPDATE for_row_2;UPDATE for_row_3;...
>
> The buffer could get as big as half a megabyte or so.  Then I
> sqlite3_reset() the SELECT command.  Then I submit the entire piece of text
> as one long parameter to sqlite3_exec().  Worked perfectly, very quickly,
> and didn't take up much more memory than storing the parameters in an array.
>
> Simon.
> ___
> 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] [EXTERNAL] Re: database locked on select

[Simon Slavin]

On 28 May 2018, at 7:56pm, Torsten Curdt  wrote:

> Just to clarify: I have a single thread - so intermixing the stepping
> through a resultset and doing an update requires WAL mode but should be
> fine. Correct?

Yes, this should work fine.  Obviously, one thread is not going to be trying to 
do two database accesses at the same time, especially since your software 
design uses the result from one SELECT row in order to figure out what UPDATE 
to issue.

You could, course, build up an array of pairs in memory while doing the SELECT, 
then consult the array to create all the UPDATE commands once you have 
finalized the SELECT.  If you do do this don't forget to surround the UPDATE 
commands with BEGIN;...COMMIT; .

One technique I have used in a similar situation was to write all my UPDATE 
commands to a long text buffer.  This was in an unusual situation where I 
needed to get the SELECT done as quickly as possible to avoid locking up lab 
equipment.  So the software figured out all the UPDATE commands and 
concatenated them in a text variable:

UPDATE for_row_1;UPDATE for_row_2;UPDATE for_row_3;...

The buffer could get as big as half a megabyte or so.  Then I sqlite3_reset() 
the SELECT command.  Then I submit the entire piece of text as one long 
parameter to sqlite3_exec().  Worked perfectly, very quickly, and didn't take 
up much more memory than storing the parameters in an array.

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

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Torsten Curdt]

Just to clarify: I have a single thread - so intermixing the stepping
through a resultset and doing an update requires WAL mode but should be
fine. Correct?

On Mon, May 28, 2018 at 11:01 AM Hick Gunter  wrote:

> As long as you are and remain in serialized mode, you can re-use the same
> connection from different threads.
>
> If you can guarantee that each thread will have it's own, personal,
> connection, you may achieve a performance increase by using multithread
> mode.
>
> In single thread mode, all the checks are turned off and it is up to you
> to make sure that exactly 1 thread does all the calls to SQLite. This mode
> is fastest.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Torsten Curdt
> Gesendet: Montag, 28. Mai 2018 10:38
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: Re: [sqlite] [EXTERNAL] Re: database locked on select
>
> Thanks for the suggestion but I feel that will just make things more
> complex.
> Right now I have one function that does it work and then decides whether
> to update or not.
> That way I would have to split that one function into two (new_data,
> needs_update)
> which is not so easy.
> Plus that also makes things less portable (not this really is a
> requirement though).
>
> WAL mode seems to solve this for me much better. At least I don't see any
> drawbacks yet.
> I am just wondering if having both statements on the same connection is a
> valid way of using this.
>
> cheers,
> Torsten
>
> On Mon, May 28, 2018 at 10:25 AM Hick Gunter  wrote:
>
> > It is possible to bring an external resource into SQlite by writing
> > user-defined functions and/or virtual tables. This would allow
> > something
> > like:
> >
> > UPDATE  set () = new_data() where
> > needs_update();
> >
> > With the UDF returning 1 (TRUE) if the current row (identified by the
> > arguments) needs an update and the row-valued function new_data()
> > returns the new values.
> >
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users
> > [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von Torsten Curdt
> > Gesendet: Montag, 28. Mai 2018 10:04
> > An: sqlite-users@mailinglists.sqlite.org
> > Betreff: [EXTERNAL] Re: [sqlite] database locked on select
> >
> > I have to query an external resource for each row.
> > Unfortunately nothing I can do in a single update query.
> > Would mean keeping a lot of data manually in memory.
> >
> > On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży <
> > abrozynieprzelozy314...@gmail.com> wrote:
> >
> > > BTW why not to update all rows by single update query?
> > >
> > > 2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> > > > I am doing a select, then iterate through the resultset and on
> > > > each row call update on that row.
> > > > I am using the golang driver and ran into the issue that on the
> > > > update
> > > the
> > > > database is still locked from the select.
> > > >
> > > >   https://github.com/mattn/go-sqlite3/issues/569
> > > >
> > > > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
> > > > and
> > > IIUC
> > > > these types of updates should be possible since version 3.3.7
> > > > though
> > > > -
> > > and
> > > > I am using 3.19.3.
> > > >
> > > > Any suggestion on how to track down why the updates fail?
> > > >
> > > > cheers,
> > > > Torsten
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@mailinglists.sqlite.org
> > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-use
> > > > rs
> > > >
> > > ___
> > > 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
> >
> >
> > ___
> >  Gunter Hick | Software Engineer | Scientific Games International GmbH
> > | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013
> > | (O)

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Hick Gunter]

As long as you are and remain in serialized mode, you can re-use the same 
connection from different threads.

If you can guarantee that each thread will have it's own, personal, connection, 
you may achieve a performance increase by using multithread mode.

In single thread mode, all the checks are turned off and it is up to you to 
make sure that exactly 1 thread does all the calls to SQLite. This mode is 
fastest.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Torsten Curdt
Gesendet: Montag, 28. Mai 2018 10:38
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Re: database locked on select

Thanks for the suggestion but I feel that will just make things more complex.
Right now I have one function that does it work and then decides whether to 
update or not.
That way I would have to split that one function into two (new_data,
needs_update)
which is not so easy.
Plus that also makes things less portable (not this really is a requirement 
though).

WAL mode seems to solve this for me much better. At least I don't see any 
drawbacks yet.
I am just wondering if having both statements on the same connection is a valid 
way of using this.

cheers,
Torsten

On Mon, May 28, 2018 at 10:25 AM Hick Gunter  wrote:

> It is possible to bring an external resource into SQlite by writing
> user-defined functions and/or virtual tables. This would allow
> something
> like:
>
> UPDATE  set () = new_data() where
> needs_update();
>
> With the UDF returning 1 (TRUE) if the current row (identified by the
> arguments) needs an update and the row-valued function new_data()
> returns the new values.
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Torsten Curdt
> Gesendet: Montag, 28. Mai 2018 10:04
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] Re: [sqlite] database locked on select
>
> I have to query an external resource for each row.
> Unfortunately nothing I can do in a single update query.
> Would mean keeping a lot of data manually in memory.
>
> On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży <
> abrozynieprzelozy314...@gmail.com> wrote:
>
> > BTW why not to update all rows by single update query?
> >
> > 2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> > > I am doing a select, then iterate through the resultset and on
> > > each row call update on that row.
> > > I am using the golang driver and ran into the issue that on the
> > > update
> > the
> > > database is still locked from the select.
> > >
> > >   https://github.com/mattn/go-sqlite3/issues/569
> > >
> > > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
> > > and
> > IIUC
> > > these types of updates should be possible since version 3.3.7
> > > though
> > > -
> > and
> > > I am using 3.19.3.
> > >
> > > Any suggestion on how to track down why the updates fail?
> > >
> > > cheers,
> > > Torsten
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-use
> > > rs
> > >
> > ___
> > 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
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH
> | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013
> | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Torsten Curdt]

Thanks for the suggestion but I feel that will just make things more
complex.
Right now I have one function that does it work and then decides whether to
update or not.
That way I would have to split that one function into two (new_data,
needs_update)
which is not so easy.
Plus that also makes things less portable (not this really is a requirement
though).

WAL mode seems to solve this for me much better. At least I don't see any
drawbacks yet.
I am just wondering if having both statements on the same connection is a
valid way of using this.

cheers,
Torsten

On Mon, May 28, 2018 at 10:25 AM Hick Gunter  wrote:

> It is possible to bring an external resource into SQlite by writing
> user-defined functions and/or virtual tables. This would allow something
> like:
>
> UPDATE  set () = new_data() where
> needs_update();
>
> With the UDF returning 1 (TRUE) if the current row (identified by the
> arguments) needs an update and the row-valued function new_data() returns
> the new values.
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Torsten Curdt
> Gesendet: Montag, 28. Mai 2018 10:04
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] Re: [sqlite] database locked on select
>
> I have to query an external resource for each row.
> Unfortunately nothing I can do in a single update query.
> Would mean keeping a lot of data manually in memory.
>
> On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży <
> abrozynieprzelozy314...@gmail.com> wrote:
>
> > BTW why not to update all rows by single update query?
> >
> > 2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> > > I am doing a select, then iterate through the resultset and on each
> > > row call update on that row.
> > > I am using the golang driver and ran into the issue that on the
> > > update
> > the
> > > database is still locked from the select.
> > >
> > >   https://github.com/mattn/go-sqlite3/issues/569
> > >
> > > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
> > > and
> > IIUC
> > > these types of updates should be possible since version 3.3.7 though
> > > -
> > and
> > > I am using 3.19.3.
> > >
> > > Any suggestion on how to track down why the updates fail?
> > >
> > > cheers,
> > > Torsten
> > > ___
> > > 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
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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] [EXTERNAL] Re: database locked on select

[Hick Gunter]

It is possible to bring an external resource into SQlite by writing 
user-defined functions and/or virtual tables. This would allow something like:

UPDATE  set () = new_data() where 
needs_update();

With the UDF returning 1 (TRUE) if the current row (identified by the 
arguments) needs an update and the row-valued function new_data() returns the 
new values.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Torsten Curdt
Gesendet: Montag, 28. Mai 2018 10:04
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] database locked on select

I have to query an external resource for each row.
Unfortunately nothing I can do in a single update query.
Would mean keeping a lot of data manually in memory.

On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży < 
abrozynieprzelozy314...@gmail.com> wrote:

> BTW why not to update all rows by single update query?
>
> 2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> > I am doing a select, then iterate through the resultset and on each
> > row call update on that row.
> > I am using the golang driver and ran into the issue that on the
> > update
> the
> > database is still locked from the select.
> >
> >   https://github.com/mattn/go-sqlite3/issues/569
> >
> > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
> > and
> IIUC
> > these types of updates should be possible since version 3.3.7 though
> > -
> and
> > I am using 3.19.3.
> >
> > Any suggestion on how to track down why the updates fail?
> >
> > cheers,
> > Torsten
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users