sqldatabaseconcurrencygoogle-bigquery

BigQuery - Transaction is aborted due to concurrent update against table


On Google BigQuery I'm running 5 concurrent updates on a partitioned table and then I got the following error:

Transaction is aborted due to concurrent update against table dataset_test.test1

Following Google DML docs, it says that I can have concurrent updates since they don’t modify the same partition.

I'm giving partition location to the statements to ensure that they modify different partitions.

Here is my table:

    CREATE TABLE `dataset_test.test1`
(
  partition_id INT64,
  familia_g STRING,
  target STRING,
  status STRING,
  datetime DATETIME
)
PARTITION BY RANGE_BUCKET(partition_id, GENERATE_ARRAY(0, 5, 1))

Here is a statement example:

UPDATE `dataset_test.test1` 
            SET status = 'processing',
                datetime = current_datetime
            WHERE partition_id = 2 
            AND familia_g = 'familia1' 
            AND target = 'df'

Since I'm running 5 statements, partition_id receives 0 to 4 for each statements.

Here is another statement for other partition:

UPDATE `dataset_test.test1` 
                SET status = 'processing',
                    datetime = current_datetime
                WHERE partition_id = 4 
                AND familia_g = 'familia3' 
                AND target = 'df'

Thanks to advance.


Solution

  • Solved!

    Since I'm running these statements on different BigQuery procedures they open new transaction each.

    According to transactions docs concurrent transactions (conflicting) are cancelled.