BATCH

Combines multiple data modification language (DML) statements (such as INSERT, UPDATE, and DELETE) to achieve atomicity and isolation when targeting a single partition, or only atomicity when targeting multiple partitions.

A batch applies all DML statements within a single partition before the data is available, ensuring atomicity and isolation. A well-constructed batch targeting a single partition can reduce client-server traffic and more efficiently update a table with a single row mutation.

For multiple partition batches, logging ensures that all DML statements are applied. Either all or none of the batch operations will succeed, ensuring atomicity. Batch isolation occurs only if the batch operation is writing to a single partition.

Optionally, a batch can apply a client-supplied timestamp. Before implementing or executing a batch see Batching inserts and updates.

Batches are not isolated among client programs. Other client programs can read the first modified rows from the batch while the other remaining statements in the batch are in progress. There is no batch rollback functionality, which means that a batch cannot be undone.

Synopsis

BEGIN [ ( UNLOGGED | COUNTER ) ] BATCH 
  [ USING TIMESTAMP [ epoch_microseconds ] ]
  dml_statement [ USING TIMESTAMP [ epoch_microseconds ] ] ;
  [ dml_statement [ USING TIMESTAMP [ epoch_microseconds ] ] [ ; ... ] ] ;
  APPLY BATCH ;

A batch can contain these dml_statements:

• INSERT
• UPDATE
• DELETE

UNLOGGED | COUNTER

If UNLOGGED is not specified, the batch is logged. If multiple partitions are involved, batches are logged by default. A logged batch ensures that all or none of the batch operations succeed (atomicity). First the serialized batch is written to the batchlog system table which consumes the serialized batch as blob data. After a successful write, the rows are persisted (or hinted) and the batchlog data is removed. Logging incurs a performance penalty, the batchlog is written to two other nodes. Options for thresholds, warning about or failure due to batch size, are available.

UNLOGGED runs the batch without logging penalties. Unlogged batching issues a warning when too many operations or too many partitions are involved. Single partition batch operations are unlogged by default, and are the only unlogged batch operations recommended.

Although a logged batch enforces atomicity (that is, it guarantees if all DML statements in the batch succeed or none do), there is no other transactional enforcement at the batch level. For example, there is no batch isolation unless the batch operation is writing to a single partition. In multiple partition batch operations, clients are able to read the first updated rows from the batch, while other rows are still being updated on the server. In single partition batch operations, clients cannot read a partial update from any row until the batch is completed.

Use the COUNTER option for batched counter updates. Unlike other updates, counter updates are not idempotent.

USING TIMESTAMPS

Sets the write time for transactions executed in a BATCH.

Restriction: USING TIMESTAMP does not support LWT (lightweight transactions), such as DML statements that have an IF NOT EXISTS clause.

By default, the database applies the same timestamp to all data modified by the batch; therefore statement order does not matter within a batch, thus a batch statement is not very useful for writing data that must be timestamped in a particular order. Use client-supplied timestamps to achieve a particular order.

User-defined timestamp

Specify the epoch time in microseconds after USING TIMESTAMP [epoch_microseconds]. When the time is not specified, the database inserts the current time.

To use the same timestamp for all DMLs, insert on first line of batch:

BEGIN BATCH USING TIMESTAMP [ epoch_microseconds ]
   DML_statement1 ;
   DML_statement2 ;
   DML_statement3 ;
APPLY BATCH ;

For individual transactions, insert at the end of a DML:

BEGIN BATCH
   DML_statement1 ;
   DML_statement2 USING TIMESTAMP [epoch_microseconds] ;
   DML_statement3 ;
APPLY BATCH ;

Examples

BEGIN BATCH USING TIMESTAMP 1481124356754405

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 2, 13.44, 'Lunch', true
  );

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 3, 25.00, 'Dinner', true
  );

APPLY BATCH;

EXPAND ON

SELECT
   cyclist_name, expense_id,
   amount, WRITETIME(amount),
   description, WRITETIME(description),
   paid, WRITETIME(paid)
 FROM
   cycling.cyclist_expenses
  WHERE
    cyclist_name = 'Vera ADRIAN';

@ Row 1
------------------------+------------------
 cyclist_name           | Vera ADRIAN
 expense_id             | 2
 amount                 | 13.44
 writetime(amount)      | 1481124356754405
 description            | Lunch
 writetime(description) | 1481124356754405
 paid                   | True
 writetime(paid)        | 1481124356754405

@ Row 2
------------------------+------------------
 cyclist_name           | Vera ADRIAN
 expense_id             | 3
 amount                 | 25
 writetime(amount)      | 1481124356754405
 description            | Dinner
 writetime(description) | 1481124356754405
 paid                   | False
 writetime(paid)        | 1481124356754405

(2 rows)

BEGIN BATCH USING TIMESTAMP 1481124356754405

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 2, 13.44, 'Lunch', true
  );

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 3, 25.00, 'Dinner', false
  ) IF NOT EXISTS;

APPLY BATCH;

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot provide custom timestamp for conditional BATCH"

BEGIN BATCH

  INSERT INTO purchases (
    user, balance
  ) VALUES (
    'user1', -8
  ) IF NOT EXISTS;

  INSERT INTO purchases (
    user, expense_id, amount, description, paid
  ) VALUES (
    'user1', 1, 8, 'burrito', false
  );

APPLY BATCH;

BEGIN BATCH

  UPDATE purchases
    SET balance = -208
    WHERE user='user1'
    IF balance = -8;

  INSERT INTO purchases (
    user, expense_id, amount, description, paid
  ) VALUES (
    'user1', 2, 200, 'hotel room', false
  );

APPLY BATCH;

BEGIN COUNTER BATCH

  UPDATE UserActionCounts
    SET total = total + 2
    WHERE keyalias = 523;

  UPDATE AdminActionCounts
    SET total = total + 2
    WHERE keyalias = 701;

APPLY BATCH;