Understanding and Resolving the ER_LOCK_CONFLICT Error in Distributed Transactions

{question}

When do we encounter the ER_LOCK_CONFLICT transaction error, and how can we resolve it?

{question}

{answer}

The ER_LOCK_CONFLICT error occurs during the execution of a DML statement (such as UPDATE or DELETE) inside a distributed transaction, when it attempts to acquire a row lock that is already held by another transaction.

Before discussing the specifics of ER_LOCK_CONFLICT, it's essential to understand how lock conflicts typically occur. For example, the ER_LOCK_WAIT_TIMEOUT error is seen when a query waits too long to acquire a lock due to contention, often caused by uncommitted or idle transactions. These open transactions hold locks on the affected rows until they are either committed or rolled back. If another query attempts to modify the same rows and the wait time exceeds the lock_wait_timeout (default: 60 seconds), The query fails with ER_LOCK_WAIT_TIMEOUT.

With the introduction of distributed transactions, enabled when UPDATE or DELETE operations span across multiple partitions under global versioning, a new error, ER_LOCK_CONFLICT, may occur. This error indicates that the statement could not obtain a required lock because another transaction already holds it.

To control the behavior of this error, a global variable row_lock_conflict_forces_implicit_rollback was introduced:

• Default behavior (false): When a statement returnsER_LOCK_CONFLICT, only that statement is rolled back, not the entire distributed transaction. This mirrors the behavior of ER_LOCK_WAIT_TIMEOUT where, in the event of a deadlock, the statement fails, but the transaction remains active unless it is explicitly rolled back.

• When set to true: If a statement hits ER_LOCK_CONFLICT, the entire distributed transaction is implicitly rolled back. This guarantees that the conflicting transaction can proceed, as all locks held by the rolled-back transaction are released.

Additionally, for single-statement distributed transactions, or the first statement of a multi-statement distributed transaction, the system automatically retries the statement when it encounters ER_LOCK_CONFLICT. This retry mechanism continues until one of the following occurs:

• A limited number of attempts have been made.

OR

• The total elapsed time since the first attempt exceeds lock_wait_timeout.

{answer}