Re: [sqlite] SELECT ... FOR UPDATE

: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Scott Doctor Gesendet: Dienstag, 24. Mai 2016 19:34 An: sqlite-users@mailinglists.sqlite.org Betreff: [sqlite] SELECT ... FOR UPDATE My current project has me bouncing between

Re: [sqlite] SELECT ... FOR UPDATE

On 24 May 2016, at 8:04pm, Igor Tandetnik wrote: > SQLite will report a syntax error. That's pretty easy to confirm > experimentally. Download the command line shell tool as a "precompiled binary" for your platform: and test

Re: [sqlite] SELECT ... FOR UPDATE

On 5/24/2016 1:34 PM, Scott Doctor wrote: SELECT field FROM table FOR UPDATE; My question is, looking through the sqlite documentation, it is not clear how sqlite will handle that statement. SQLite will report a syntax error. That's pretty easy to confirm experimentally. -- Igor Tandetnik

[sqlite] SELECT ... FOR UPDATE

My current project has me bouncing between sqlite and mysql. In mysql, to lock a row of data at the start of a process to hold off concurrent operations, the recommended technique with the innodb engine is to issue SELECT field FROM table FOR UPDATE; This holds off any other process from

[sqlite] Select after update get new or old values

On 11 Sep 2015, at 3:14am, Nguyen Dang Quang wrote: > SYNCHRONOUS = OFF will make new data not visible to select command? "PRAGMA synchronous" is about making sure that the disk (or other storage medium) is updated to reflect changes in the database. Normally changes to files are held in

[sqlite] Select after update get new or old values

, 2015 2:02 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Select after update get new or old values On 10 Sep 2015, at 5:05pm, Nguyen Dang Quang wrote: > In (4) the selected rows will have old or new values? Read this: <https://www.sqlite.org/isolation.html> "W

[sqlite] Select after update get new or old values

Dear all, I am using System.Data.SQLite for my .Net 2.0 application. In the app, I always run a sequence like that: 1. Open connection to sqlite and apply pragma: SYNCHRONOUS = OFF ; JOURNAL_MODE = MEMORY 2. Open a transaction 3. Update a row 4. Select the updated row in

[sqlite] Select after update get new or old values

On 10 Sep 2015, at 5:05pm, Nguyen Dang Quang wrote: > In (4) the selected rows will have old or new values? Read this: "Within a single database connection X, a SELECT statement always sees all changes to the database that are completed prior to the

[sqlite] Select after update get new or old values

which values does it have? By definition it should be old values. On Thu, Sep 10, 2015 at 9:05 AM, Nguyen Dang Quang wrote: > Dear all, > > I am using System.Data.SQLite for my .Net 2.0 application. > > In the app, I always run a sequence like that: > > 1. Open connection to sqlite and

Re: [sqlite] SELECT and UPDATE?

Thanks (to other repliers too), That put me on the right track. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Keith Medcalf Sent: October-18-13 7:34 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SELECT

Re: [sqlite] SELECT and UPDATE?

For multiple consumers you might do something like: BEGIN; UPDATE processingqueue SET status = 'failed' WHERE status = 'processing' AND processor = :processor; UPDATE processingqueue SET status = 'processing', processor = :processor, started = strftime('%s') WHERE queueid = (SELECT queueid from

Re: [sqlite] SELECT and UPDATE?

On Fri, 18 Oct 2013 13:57:18 -0400 "Normand Mongeau" wrote: > Also, the consuming should be a 2-step process because the processing > is involved and may fail for reasons too long to explain here. So in > essence, select a record, modify it to indicate it's being

Re: [sqlite] SELECT and UPDATE?

On 10/18/2013 1:57 PM, Normand Mongeau wrote: Also, the consuming should be a 2-step process because the processing is involved and may fail for reasons too long to explain here. So in essence, select a record, modify it to indicate it's being processed, and once the processing is done delete

[sqlite] SELECT and UPDATE?

Hi, I have a scenario where I'm using an sqlite table as a queue for inter-process communication. One or more processes feed the table, and one or more different processes are meant to consume the data. What's my best scenario in terms of minimizing conflicts? Also, the consuming

Re: [sqlite] SELECT and UPDATE in single query

> If you want to know which rows were updated regardless of the key, what > you need is a column to hold a unique value for each update transaction, > and set it as part of the UPDATE. You could add a datetime column, > for example, if the time resolution is fine enough. Good idea. I found

Re: [sqlite] SELECT and UPDATE in single query

>> There is sqlite3_update_hook() function that returns rowid of changed record. > sounds promising, but apparently not supported by the python wrapper. Not the included sqlite3/pysqlite dbiapi wrapper -- though it is supported by Roger Binns APSW wrapper. http://code.google.com/p/apsw/ ---

Re: [sqlite] SELECT and UPDATE in single query

On Fri, Jan 25, 2013 at 1:36 AM, Yongil Jang wrote: > There is sqlite3_update_hook() function that returns rowid of changed > record. sounds promising, but apparently not supported by the python wrapper. ___ sqlite-users mailing

Re: [sqlite] SELECT and UPDATE in single query

On Thu, 24 Jan 2013 16:47:02 +1100 Richard Baron Penman wrote: > How to find which keys have been updated from this query? That's the problem with "limit N", right? It's not based on the data. Not only do you not know which rows were updated, you don't know which ones were

Re: [sqlite] SELECT and UPDATE in single query

Penman wrote: How to find which keys have been updated from this query? There is sqlite3_update_hook() function that returns rowid of changed record. Regards, Yongil Jang. On Jan 24, 2013 11:10 PM, "Igor Tandetnik" wrote: > On 1/24/2013 12:47 AM, Richard Baron Penman wrote:

Re: [sqlite] SELECT and UPDATE in single query

On 1/24/2013 12:47 AM, Richard Baron Penman wrote: How to find which keys have been updated from this query? You can't, really. If you need a list of keys (or in fact a list of anything from the database), you need to run a SELECT statement. At which point you are back where you started and

Re: [sqlite] SELECT and UPDATE in single query

> Why process only N at a time, Richard? There are a number of workers who request unprocessed jobs from the queue. But the queue is too big to hold in memory all at once. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SELECT and UPDATE in single query

Thanks for tip about the redundant index. How to find which keys have been updated from this query? On Thu, Jan 24, 2013 at 3:32 PM, Keith Medcalf wrote: >> I have a table like this: >> >> CREATE TABLE queue ( >> key TEXT NOT NULL PRIMARY KEY UNIQUE, >> status

Re: [sqlite] SELECT and UPDATE in single query

On Wed, 23 Jan 2013 21:32:20 -0700 "Keith Medcalf" wrote: > > And then I process it like this, N keys at a time: > > > > SELECT key FROM queue WHERE status=0 LIMIT N; > > BEGIN TRANSACTION; > > for key in keys: > > UPDATE queue SET status=1 WHERE key=key; > > END

Re: [sqlite] SELECT and UPDATE in single query

On 1/23/2013 11:22 PM, Richard Baron Penman wrote: And then I process it like this, N keys at a time: SELECT key FROM queue WHERE status=0 LIMIT N; BEGIN TRANSACTION; for key in keys: UPDATE queue SET status=1 WHERE key=key; END TRANSACTION; How can this SELECT and UPDATE be combined

Re: [sqlite] SELECT and UPDATE in single query

> I have a table like this: > > CREATE TABLE queue ( > key TEXT NOT NULL PRIMARY KEY UNIQUE, > status INTEGER > ); > CREATE INDEX IF NOT EXISTS keys ON queue (key); Your index is redundant. There is already a unique index on key since it is a primary key. It should probably be:

Re: [sqlite] SELECT FOR UPDATE

Hi, We are newbie to SQLite and we would like to lock a table through a select + update. Something like that: BEGIN TRANSACTION currentValue = select currentValue from sequence_transactions; update sequence_transactions set currentValue = currentValue + 1; COMMIT; END TRANSACTION

[sqlite] SELECT FOR UPDATE

Hi, We are newbie to SQLite and we would like to lock a table through a select + update. Something like that: BEGIN TRANSACTION currentValue = select currentValue from sequence_transactions; update sequence_transactions set currentValue =