Run UPDATE
s and DELETE
s as Distributed Transactions
The engine variable disable_update_delete_distributed_transactions
controls whether UPDATEs and DELETEs will run as distributed transactions. When this variable is set to OFF
(the default value), UPDATE
s and DELETE
s are atomic operations; the updates made by these operations will succeed or fail completely across all partitions.
Caution
When disable_update_delete_distributed_transactions
is set to OFF
, UPDATE
s and DELETE
s will run slower, as compared to when the variable is set to ON
. However, when disable_update_delete_distributed_transactions
is set to ON
, UPDATE
s and DELETE
s are not atomic operations and consistency across all partitions is not guaranteed.
Locking
Running multiple UPDATE
s and DELETE
s as distributed transactions increases the likelihood that these operations will block one another, potentially leading to deadlock.
The following scenario demonstrates how deadlock would occur, without the wait-die algorithm enabled. This algorithm is explained in the next section.
Two UPDATE
s (call them u1
and u2
), are scheduled to be run at nearly the same time. u1
and u2
will each run in their own distributed transaction, and both will update the same rows in the same two database partitions. These events occur sequentially:
u1
locks the rows in partition 1 and starts updating the rows in partition 1.u2
locks the rows in partition 2 and starts updating the rows in partition 2.u1
attempts to lock the rows in partition 2, but cannot, because the rows have already been locked byu2
.u2
attempts to lock the rows in partition 1, but cannot, because the rows have already been locked byu1
.Deadlock has occurred, because neither
u1
noru2
can make any further progress.
Avoiding Deadlock by Using the Wait-Die Algorithm
SingleStoreDB Cloud uses a wait-die algorithm to avoid deadlocks that could occur when multiple UPDATE
s and DELETE
s run as distributed transactions. When such a transaction t
attempts to acquire a row lock, but that lock is already being held by another transaction, the algorithm decides whether t
should wait for the row lock to be freed, or rollback t
and retry t
later. This decision is based on the transaction's age. Older transactions will always wait. Newer transactions will not wait and rollback.
In the scenario described in the previous section if the wait-die algorithm were enabled, u2
would die because it is a newer transaction than u1
. Then, u1
would update the rows in partition 2 and following that, u2
be retried in a new distributed transaction.
Multi-statement transactions (statements enclosed between BEGIN ... COMMIT
) will always wait and never die.
The next section explains how to enable the wait-die algorithm.