Transaction lock invalidation

YDB uses optimistic locking to find conflicts with other transactions being executed. If the locks check during the commit phase reveals conflicting modifications, the committing transaction rolls back and must be restarted. In this case, YDB returns a transaction locks invalidated error. Restarting a significant share of transactions can degrade your application's performance.

Diagnostics

1. Open the DB overview Grafana dashboard.

2. See if the Transaction Locks Invalidation chart shows any spikes.

   

   This chart shows the number of queries that returned the transaction locks invalidation error per second.

Recommendations

Consider the following recommendations:

• The longer a transaction lasts, the higher the likelihood of encountering a transaction locks invalidated error.

  If possible, avoid interactive transactions. A better approach is to use a single YQL query with begin; and commit; to select data, update data, and commit the transaction.

  If you do need interactive transactions, append commit; to the last query in the transaction.

• Analyze the range of primary keys where conflicting modifications occur, and try to change the application logic to reduce the number of conflicts.

  For example, if a single row with a total balance value is frequently updated, split this row into a hundred rows and calculate the total balance as a sum of these rows. This will drastically reduce the number of transaction locks invalidated errors.