[PHOENIX-7411] Atomic Delete: PhoenixStatement API to return row if single row is atomically deleted

• Type: New Feature
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 5.3.0
• Assignee: Viraj Jasani
• Reporter: Viraj Jasani
• Created: 25/Sep/24 05:31
• Updated: 14/Nov/24 09:12
• Resolved: 09/Oct/24 16:57

Description

PHOENIX-7398 introduces new PhoenixStatement API to return row for Atomic/Conditional Upserts. Phoenix already supports Upserts as Atomic operation by using ON DUPLICATE KEY clause. However, Deletes are not supported as atomic and single Delete query can also delete multiple rows based on the WHERE clause used.

HBase does not provide API to return row state for atomic updates with Put and Delete mutations. HBase API checkAndMutate() also supports returning Result for Append and Increment mutations only, not for Put and Delete mutations.

The purpose of this Jira is to introduce support for atomic delete of single row that can return the row only if it existed before executing the Delete mutation. If the row is already deleted, the API support is not expected to return any row. For single row delete to be atomic, IndexRegionObserver needs to take row lock for to be deleted row and also scan the row, similar to Atomic Put mutation(s).

 

PhoenixStatement API signature is same as of PHOENIX-7398:

/**
 * Executes the given SQL statement similar to JDBC API executeUpdate() but also returns the
 * updated or non-updated row as Result object back to the client. This must be used with
 * auto-commit Connection. This makes the operation atomic.
 * If the row is successfully updated, return the updated row, otherwise if the row
 * cannot be updated, return non-updated row.
 *
 * @param sql The SQL DML statement, UPSERT or DELETE for Phoenix.
 * @return The pair of int and Tuple, where int represents value 1 for successful row
 * update and 0 for non-successful row update, and Tuple represents the state of the row.
 * @throws SQLException If the statement cannot be executed.
 */
public Pair<Integer, Tuple> executeUpdateReturnRow(String sql) throws SQLException {

Issue Links

• is related to: PHOENIX-7398 New PhoenixStatement API to return row for Atomic/Conditional Upserts
• relates to: PHOENIX-7434 Extend atomic support to single row delete with condition on non-pk columns
• links to: GitHub Pull Request #1985