[
https://issues.apache.org/jira/browse/PHOENIX-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15435991#comment-15435991
]
James Taylor commented on PHOENIX-2909:
---------------------------------------
bq. How does this update statement interact with transaction support?
It wouldn't impact transactional tables since they have an optimistic
concurrency model for conflict detection. This JIRA is more about surfacing a
simple model for things like counters where you may not need full blown
transactions. It'll be interesting to measure the perf of the equivalent
counter code with transactions versus w/out (using this new mechanims).
> 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
> Assignee: James Taylor
> Fix For: 4.9.0
>
>
> 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)
