Handling Errors in BATCHSQL for Google Spanner Replicat Operations

The following errors are managed by GoldenGate Replicat when using BATCHSQL with Google Spanner:

• INSERT: Duplicate key error
• UPDATE/DELETE: Row not found error
• Primary Key Update: Key not found error and new Key already exists error

If any DML operation fails with any of these errors, by default the transaction is aborted and Replicat abends. If the user has enabled HANDLECOLLISIONS, OVERRIDEDUPS, INSERT_MISSING_UPDATES or any other REPERROR rules, then it will retry, skip, or modify and then run the DML to resolve the error.

If any DML operation fails with any other error, then the transaction is aborted and Replicat abends. Alternatively, based on the REPERROR rules, Replicat aborts and retries the entire transaction.

A RESOURCE_EXHAUSTED error in Cloud Spanner often indicates that the transaction limit has exceeded for the number of mutations or the total transaction size. An error similar to the following occurs, suggesting to reduce MAXTRANSOPS or BATCHTRANSOPS.

Error: Operation failed on Spanner database due to quota, session limit, or batch size limit error:

Poosible causes :
BATCH LIMIT: Transaction >80,000 mutations or >100 MiB
QUOTA LIMIT: Spanner API quota exhausted (rate-limited)
SESSION LIMIT: Max concurrent sessions (100,000+) exceeded

Action:
For BATCH LIMIT: Reduce batch size or reduce MAXTRANSOPS and restart Replicat.
For QUOTA LIMIT: Increase Spanner quota in Google Cloud Console and restart Replicat.
For SESSION LIMIT: Reduce concurrent sessions to the Spanner and restart Replicat.
Retry using REPERROR with TRANSABORT, MAXRETRIES 5, DELAYSECS 1 is suggested.

Using Spanner Mutations API

Parameter BATCHSQL USEMUTATIONS is required to increase the efficiency when applying DMLs in Spanner, called Spanner Mutations.

This means ExecuteBatchDml is most attractive for initial load seed data where operations are predominantly contiguous inserts for the same table.

For generic workloads with mixed real-time DMLs, the BATCHSQL USEMUTATION mode provides better performance metrics.

Operational Limitations while using USEMUTATIONS option of BATCHSQL

The following limitations exist when using Spanner Mutations:

• Collision statistics : Collision statistics are incomplete because commit-time mutation results do not identify how many inserts effectively became updates, or how many missing-row updates effectively became inserts or how many updates/delete operations were skipped.

• DML-level REPERROR actions: REPERROR actions cannot be applied to individual DML statements in BATCHSQL mode while using Spanner Mutations because Spanner does not return statement-level results for buffered mutations, and we get an error for the entire transaction at commit time.

• Missing row Delete operations: Delete operations on missing rows are skipped by Spanner DeleteMutation semantics, so unlike other databases BATCHSQL with USEMUTATIONS will always return success in case of missing row delete operations.

• Fallback to Normal mode for PK Updates: Replicat falls back to normal mode, for any transaction that contains a Primary Key update, it will be retried in classic mode as soon as it encounters PK Update.

Although the Spanner Mutation behavior does not change due to these limitations, it may cause performance degradation.

Behavior of BATCHSQL Mode with USEMUTATIONS

The following is a summary of how each GoldenGate BATCHSQL mode maps to the Spanner mutation types:

1. Default case:

   1.1. Inserts: Transaction should fail in case of duplicate-key inserts and the error is returned only at commit time.

   1.2. Updates: Transaction should fail in case of missing-row updates and the error is returned only at commit time.

   1.3. Deletes: Skips delete if row is NOT_FOUND

2. HANDLECOLLISIONS/OVERRIDEDUPS:

   2.1. Inserts: Insert should be converted to an Update in case of a duplicate-key error and the error is returned only at commit time, and we would not have any PK Collision statistics.

   2.2. Updates: Updates should get skipped in case of missing-row errors.

    **NOTE:** Spanner locks the row for that specific primary key using Replicat transaction even if it does not exist, which means that Spanner would not allow any other transaction to INSERT that row until Replicat transaction is committed or aborted.  
    {: .infoboxnote}
   

   2.3. Deletes: DELETEMUTATION skips delete if row is NOT_FOUND.

3. Insert Missing Updates:

   3.1. Inserts: Transaction should fail in case of duplicate-key inserts and the error is returned only at commit time.

   3.2. Updates: Update should insert the row in case of missing-row error and the error is returned only at commit time .