Re: [sqlite] C API - Parameterized Atomic Transactions

[James K. Lowden]

On Tue, 9 Aug 2016 17:09:39 -0300
Paulo Roberto  wrote:

> I would like something like this:
> 
> "BEGIN EXCLUSIVE TRANSACTION;"
>  "SELECT counter FROM mytable WHERE counterid = ?;"
>  "UPDATE mytable SET counter=? WHERE counterid = ?;"
>   "COMMIT TRANSACTION;"

begin transaction;

UPDATE mytable SET counter = (
select 1 + max(counter)
from mytable where counterid = ? )
WHERE counterid = ?;

select counter - 1 as counter 
from mytable where counterid = ?;

commit transaction;

Standard SQL.  Doesn't rely on BEGIN EXCLUSIVE.  Should be just as
fast.  

--jkl

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

Re: [sqlite] C API - Parameterized Atomic Transactions

[Dominique Devienne]

On Thu, Aug 11, 2016 at 4:34 AM, Paulo Roberto 
wrote:

> Thank you very much, it worked!


Just remember that exposing a SQL function that de-references a
"user"-supplied integer value as a pointer is inherently unsafe.
Anyone can select remember(val, 0) or select remember(val, 101) and crash
(at best) your app, or worse silently corrupt it in some way.

A safer (but slower) approach would be to add a level of indirection, and
not use the int as a "pointer" directly, but as a *key* in a map
(a global) used to lookup the memory address of the counter, so you *can*
error out (instead of crash/corrupt) on a bad input/key. My $0.02.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Paulo Roberto]

Thank you very much, it worked!

On Tue, Aug 9, 2016 at 11:49 PM, Richard Hipp  wrote:

> On 8/9/16, Paulo Roberto  wrote:
> >
> > I found your solution pretty elegant and I tried to implement it.
> > But after solving a lot of building issues with the sqlite3ext header
>
> It does not have to be implemented as a loadable extension.  Just copy
> the lines https://www.sqlite.org/src/artifact/8440f8d0b4?ln=41-53 into
> your application, then invoke
> https://www.sqlite.org/src/artifact/8440f8d0b4?ln=65-66 on the
> database connection right after you get it back from sqlite3_open().
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] C API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Paulo Roberto  wrote:
>
> I found your solution pretty elegant and I tried to implement it.
> But after solving a lot of building issues with the sqlite3ext header

It does not have to be implemented as a loadable extension.  Just copy
the lines https://www.sqlite.org/src/artifact/8440f8d0b4?ln=41-53 into
your application, then invoke
https://www.sqlite.org/src/artifact/8440f8d0b4?ln=65-66 on the
database connection right after you get it back from sqlite3_open().

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Paulo Roberto]

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

Re: [sqlite] C API - Parameterized Atomic Transactions

[Keith Medcalf]

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

Re: [sqlite] C API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Richard Hipp  wrote:

> Or, you could make remember() a two argument function:
>
>UPDATE mytable SET counter=remember(counter, $ptr)+1 WHERE counterid=$id
>

A sample implementation for this function can now been seen at
https://www.sqlite.org/src/artifact/8440f8d0b452c5cd
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Richard Hipp  wrote:
>
> UPDATE mytable SET counter=remember(counter)+1 WHERE counterid=?
>

Or, you could make remember() a two argument function:

   UPDATE mytable SET counter=remember(counter, $ptr)+1 WHERE counterid=$id

Then bind $ptr to the address of the variable in which you want to
store the original value (and also bind $id to the particular counter
you want to look up, of course).  That way, the same remember()
function could be reused with multiple variables for storing the
result - just rebind the $ptr value each time.

This requires casting a pointer into a 64-bit integer, which is not
guaranteed to work according to various C/C++ standards but which does
in fact work on all modern architectures.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Richard Hipp]

On 8/9/16, Paulo Roberto  wrote:
>
> I need some help to ... increment a counter and get its
> former value.

> My question is: Preparing 4 statements, binding then and calling
> *sqlite3_step
> *for each one of then in order, would have the expected atomic operation
> behavior or not? If not, how could I achieve this?
>

Yes.

Maybe another technique would be to create an application-defined
function named "remember()" that takes a single integer argument and
returns the same value, but remembers the value in a variable in your
application.  Then run:

UPDATE mytable SET counter=remember(counter)+1 WHERE counterid=?

That way you would only have a single prepared statement to deal with.
On the other hand, the results get written into a single variable, so
it wouldn't work to use this from multiple threads, unless each thread
had its own remember() function.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C API - Parameterized Atomic Transactions

[Simon Slavin]

On 9 Aug 2016, at 9:09pm, Paulo Roberto  wrote:

> My question is: Preparing 4 statements, binding then and calling *sqlite3_step
> *for each one of then in order, would have the expected atomic operation
> behavior or not?

You might be happier with BEGIN IMMEDIATE.

No other connections can make changes between your BEGIN IMMEDIATE and your 
COMMIT.

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

Re: [sqlite] C API - Parameterized Atomic Transactions

[Clemens Ladisch]

Paulo Roberto wrote:
> I need some help to do a simple operation, increment a counter and get its
> former value.
> I could have some race condition, so the transaction must be atomic.
>
> I also would like to use prepared statements to accomplish that, so I have
> less effort sanitizing inputs.

Integer numbers would not need to be sanitized.

> "BEGIN EXCLUSIVE TRANSACTION;"
> "SELECT counter FROM mytable WHERE counterid = ?;"
> "UPDATE mytable SET counter=? WHERE counterid = ?;"
> "COMMIT TRANSACTION;"

> My question is: Preparing 4 statements, binding then and calling *sqlite3_step
> *for each one of then in order, would have the expected atomic operation
> behavior?

Yes; that is what transactions are for.
(You have to ensure that errors in the middle statements are handled correctly.)


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

[sqlite] C API - Parameterized Atomic Transactions

[Paulo Roberto]

Hello,

I need some help to do a simple operation, increment a counter and get its
former value.
I could have some race condition, so the transaction must be atomic.

I also would like to use prepared statements to accomplish that, so I have
less effort sanitizing inputs.

My problem:

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.

I would like something like this:

"BEGIN EXCLUSIVE TRANSACTION;"
 "SELECT counter FROM mytable WHERE counterid = ?;"
 "UPDATE mytable SET counter=? WHERE counterid = ?;"
  "COMMIT TRANSACTION;"

The *sqlite3_exec* doesn't allow me to pass parameters.
And the documentation of *sqlite3_prepare_v2 *says: "*These routines only
compile the first statement in zSql, so *pzTail is left pointing to what
remains uncompiled*"

My question is: Preparing 4 statements, binding then and calling *sqlite3_step
*for each one of then in order, would have the expected atomic operation
behavior or not? If not, how could I achieve this?

Thank you in advance.

Regards.

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