Error "abort transaction due to concurrent update" Is Reported During SQL Execution

Symptom

The error message "abort transaction due to concurrent update" is reported indicating that lock waiting times out when SQL statements are executed.

Possible Causes

Concurrent operations from two transactions are executed on a single row in a table. As a result, the transaction that is operated later is rolled back.

For example:

1. Open a connection session A, connect to the GaussDB(DWS) database as common user u1, create a test table u1.test in SCHEMA u1, and insert data into the table.

   1 2	CREATE TABLE test (id int, name varchar(50)); INSERT INTO test VALUES (1, 'lily');

2. Open a new connection session session B, start transaction 1, connect to the GaussDB(DWS) database as the system administrator dbadmin, and perform the UPDATE operation.

   1 2 3 4

   START TRANSACTION; UPDATE u1.test SET id = 3 WHERE name = 'lily'; UPDATE 1

3. Start transaction 2 in session A and execute the same UPDATE statement. An error is reported.

   1 2 3 4

   START TRANSACTION; UPDATE test SET id = 3 WHERE name = 'lily'; ERROR: dn_6003_6004: abort transaction due to concurrent update test 289502.

In the preceding case, two different transactions concurrently update the same record. There is no lock waiting. Instead, an error is reported: abort transaction due to concurrent update.

In practice, an error may be reported not only when the same record is concurrently updated. For other concurrent SQL operations such as SELECT and DELETE, the error "abort transaction due to concurrent update" may also be reported.

Handling Procedure

• Adjust the execution sequence of service logic and the SQL statements.

  Do not place the SQL statement that holds the lock for a long time in the front.

• Avoid large transactions.

  Split a large transaction into multiple small transactions for processing. Small transactions shorten the resource locking time and have a lower probability of conflicts.

• Control concurrency.

  Reduce the number of concurrent sessions as much as possible to reduce the probability of conflicts.