Thank you for all the answers. Clemens, The counterid in my case is a text field and not an integer. That's why I need to sanitize.
Clemens and Keith, As each of my process has its own connection to the database, I tried your solution using BEGIN IMMEDIATE and it worked successfully. Thank you. Richard, I found your solution pretty elegant and I tried to implement it. But after solving a lot of building issues with the sqlite3ext header I was not able to figure out how to use the function sqlite3_remember_init even reading the MACROS definition. My problem is with the parameter : const sqlite3_api_routines *pApi No matter what value I pass I keep receiving the signal SIGSEV i.e. Segmentation fault. Do you have another reference to give to me? Thanks for your help. Regards. Paulo Roberto. On Tue, Aug 9, 2016 at 9:07 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > > "BEGIN EXCLUSIVE TRANSACTION;" > > "SELECT counter FROM mytable WHERE counterid = ?;" > > "UPDATE mytable SET counter=? WHERE counterid = ?;" > > "COMMIT TRANSACTION;" > > > I have a counter that I need to increment and get its previous value in > one > > operation. > > To access this counter I must pass as a parameter to the query a specific > > WHERE condition. > > To paraphrase: > > I want to return the current value of a counter identified by an id and > then increment it. This operation must be atomic (have repeatable read > isolation). > > BEGIN IMMEDIATE; > SELECT counter FROM mytable WHERE counterid = ?; > UPDATE mytable SET counter = counter + 1 WHERE counterid = ?; > COMMIT; > > Of course, the 1 in the increment does not need to be a constant but can > be a ? if you are incrementing by some arbitrary value. > > You need to prepare and execute the statements one after each. > > And yes, the select and update, performed inside the same transaction, on > a connection not being simultaneously used for "other purposes" is executed > with repeatable read isolation. > > > > > _______________________________________________ > 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
