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