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
