CODEWITHSUNDEEP
sqldatabaseconcurrencygoogle-bigquery
joao kleiber
joao kleiber

Reputation: 37

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.

Upvotes: 0

Views: 3883

Answers (1)

joao kleiber
joao kleiber

Reputation: 37

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.

Upvotes: 1

Related Questions