Skip to main content

Run UPDATEs and DELETEs 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), UPDATEs and DELETEs 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, UPDATEs and DELETEs will run slower, as compared to when the variable is set to ON. However, when disable_update_delete_distributed_transactions is set to ON, UPDATEs and DELETEs are not atomic operations and consistency across all partitions is not guaranteed.

Locking

Running multiple UPDATEs and DELETEs 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 UPDATEs (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 by u2.

  • u2 attempts to lock the rows in partition 1, but cannot, because the rows have already been locked by u1.

  • Deadlock has occurred, because neither u1 nor u2 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 UPDATEs and DELETEs 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.

In this section: