Re: [sqlite] database locked on select

[Torsten Curdt]

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

Re: [sqlite] database locked on select

[Keith Medcalf]

or 1 thread...or 6000 threads...makes no difference whatever.  Unless you 
changed the default from SERIALIZED to something that does not apply.


---
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 Deon Brewis
>Sent: Sunday, 27 May, 2018 16:50
>To: SQLite mailing list
>Subject: Re: [sqlite] database locked on select
>
>By one connection doing SELECT and UPDATE, do you mean multi-threaded
>mode and using the connection from 2 threads?
>
>- Deon
>
>-Original Message-
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Simon Slavin
>Sent: Sunday, May 27, 2018 3:39 PM
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] database locked on select
>
>On 27 May 2018, at 7:30pm, Torsten Curdt <tcu...@vafer.org> wrote:
>
>> 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.
>
>Are you usihg one connection to do both the SELECT and the UPDATE ?
>Or are you using two different connections, one for the SELECT and
>one for the UPDATE ?
>
>The second method will not work.  The SELECT connection has the
>database locked until you tell your interface you have finished with
>the SELECT.
>
>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] database locked on select

[Abroży Nieprzełoży]

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

Re: [sqlite] database locked on select

[Torsten Curdt]

There is no multi threading. Just a single thread and only one
connection/handle.
While iterating through the resultset I am also trying to execute an update
for every row. Along the lines of:

  resultset = db.exec(`select`)
  foreach row in resultset {
db.exec(`update`)
  }

I don't want to read the whole resultset into memory.

After a lot of searching and trial and error I found that

  PRAGMA journal_mode=WAL

seems to do the trick.
I don't fully understand why though.

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

Re: [sqlite] database locked on select

[Simon Slavin]

On 27 May 2018, at 11:49pm, Deon Brewis  wrote:

> By one connection doing SELECT and UPDATE, do you mean multi-threaded mode 
> and using the connection from 2 threads?

A connection cannot lock the database against itself.  If you are doing two 
operations with one connection, there can be no SQLite lock.

However, there may be another mechanism which is doing another kind of locking. 
 And it may be something that the golang driver is doing, rather than something 
built into SQLite.  I hope someone else knows.

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

Re: [sqlite] database locked on select

[Deon Brewis]

By one connection doing SELECT and UPDATE, do you mean multi-threaded mode and 
using the connection from 2 threads?

- Deon

-Original Message-
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of 
Simon Slavin
Sent: Sunday, May 27, 2018 3:39 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] database locked on select

On 27 May 2018, at 7:30pm, Torsten Curdt <tcu...@vafer.org> wrote:

> 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.

Are you usihg one connection to do both the SELECT and the UPDATE ?  Or are you 
using two different connections, one for the SELECT and one for the UPDATE ?

The second method will not work.  The SELECT connection has the database locked 
until you tell your interface you have finished with the SELECT.

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] database locked on select

[Simon Slavin]

On 27 May 2018, at 7:30pm, Torsten Curdt  wrote:

> 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.

Are you usihg one connection to do both the SELECT and the UPDATE ?  Or are you 
using two different connections, one for the SELECT and one for the UPDATE ?

The second method will not work.  The SELECT connection has the database locked 
until you tell your interface you have finished with the SELECT.

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