phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Cameron Hatfield (JIRA)" <>
Subject [jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement
Date Wed, 24 Aug 2016 22:37:20 GMT


Cameron Hatfield commented on PHOENIX-2909:

I would agree, and I would say is a good argument for having something else within the merge
statement call out the use of whether this will be a fast / slow version. My basic worry is
that when I see something like UPDATE vs UPSERT or INSERT vs INSERT ... ON DUPLICATE KEY IGNORE,
I always expect the more complicated / does more for me statement to be either the same speed
or slower in the general case. With the proposed change, the more constrained case UPDATE
is actually slower. Whether that is a valid concern is up to debate.

How does this update statement interact with transaction support?

> Surface checkAndPut through UPDATE statement
> --------------------------------------------
>                 Key: PHOENIX-2909
>                 URL:
>             Project: Phoenix
>          Issue Type: Bug
>            Reporter: James Taylor
>            Assignee: James Taylor
>             Fix For: 4.9.0
> We can surface atomic checkAndPut like functionality through support of the SQL UPSERT
> For example, the following could use do a get under row lock to perform the row update
> {code}
> UPDATE  my_table SET counter=coalesce(counter,0) + 1 
> FROM my_table WHERE pk1 = 1 AND pk2 = 2;
> {code}
> To force prior MVCC transactions to complete (making it serializable as an Increment
is), we'd have code like this:
> {code}
>     mvcc = region.getMVCC();
>     mvcc.completeMemstoreInsert(mvcc.beginMemstoreInsert());
> {code}
> By users setting auto commit to true and issuing an UPDATE statement over a non transactional
table, they'd get a way for row updates to be atomic. This would work especially well to support
> An UPDATE statement would simply be translated to an equivalent UPSERT SELECT with a
flag being passed to the server such that the row lock and read occurs when executed. For
example, the above statement would become:
> {code}
> UPSERT INTO  my_table(pk1,pk2,counter) SELECT pk1, pk2, coalesce(counter,0) + 1 
> FROM my_table WHERE pk1 = 1 AND pk2 = 2;
> {code}
> Note that the coalesce call above handles the case where counter is null. This could
be made prettier with support for the DEFAULT clause at CREATE TABLE time (PHOENIX-476).

This message was sent by Atlassian JIRA

View raw message