[jira] [Commented] (PHOENIX-2909) Surface checkAndPut through UPDATE statement

["James Taylor (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15530934#comment-15530934 ] 

James Taylor commented on PHOENIX-2909:
---------------------------------------

This solution isn't complete because it doesn't handle the potential race between a row being inserted and a row being updated. Instead, the plan is to implement PHOENIX-6. I think this addresses your concern as well, [~cameron.hatfield].

> Surface checkAndPut through UPDATE statement
> --------------------------------------------
>
>                 Key: PHOENIX-2909
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-2909
>             Project: Phoenix
>          Issue Type: Bug
>            Reporter: James Taylor
>
> We can surface atomic checkAndPut like functionality through support of the SQL UPSERT statement.
> For example, the following could use do a get under row lock to perform the row update atomically
> {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 counters.
> 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
(v6.3.4#6332)