bigquery/ERR/2023_004

BigQuery query job do not encounter dml concurrency issue when mutating concurrently.

Product: BigQuery
Rule class: ERR - Something that is very likely to be wrong

Description

Mutating DML Queries that run concurrently, end up conflicting with each other. For these DML queries to maintain consistency, in case there are multiple queries that run at roughly the same time, it’s possible that one of them failed.

resource.type="bigquery_resource"
severity=ERROR
protoPayload.methodName="jobservice.jobcompleted"
protoPayload.status.message:"Could not serialize access to"

Remediation

The failure here is due to the concurrent DML updates. Such kind of failure is by design if there are multiple conflict DML running against the same table. The BigQuery system retries internally for up to 3 times.

Recommendation:

The best way to avoid the failure is by trying to issue such kind of jobs in a serialized way. For this suggestion would be to either space out the concurrent dml or serialize dml updates such that subsequent dml updates are triggered after the previous one completes.

Another suggestion to have parallelism along with less chance of conflict is for the users to use Partitioned table. In such a case, if the parallel running DMLs touch separate partition tables then they would not lead to conflicts .

More information on how BQ handles DML queries