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