[PHOENIX-2909] Surface checkAndPut through UPDATE statement

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: James R. Taylor
• Created: 18/May/16 01:53
• Updated: 28/Sep/16 21:30

Description

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

UPDATE  my_table SET counter=coalesce(counter,0) + 1 
FROM my_table WHERE pk1 = 1 AND pk2 = 2;

To force prior MVCC transactions to complete (making it serializable as an Increment is), we'd have code like this:

    mvcc = region.getMVCC();
    mvcc.completeMemstoreInsert(mvcc.beginMemstoreInsert());

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:

UPSERT INTO  my_table(pk1,pk2,counter) SELECT pk1, pk2, coalesce(counter,0) + 1 
FROM my_table WHERE pk1 = 1 AND pk2 = 2;

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

Issue Links

• is related to: PHOENIX-2271 Upsert - CheckAndPut like functionality