---
metadata:
  - name: generator
    content: Diplodoc Platform v5.52.0
alternate:
  - https://ydb.tech/docs/en/troubleshooting/performance/queries/transaction-lock-invalidation.md?version=v26.1
  - https://ydb.tech/docs/ru/troubleshooting/performance/queries/transaction-lock-invalidation.md?version=v26.1
  - href: en/troubleshooting/performance/queries/transaction-lock-invalidation.md
    type: text/markdown
    title: Markdown version
  - href: ../../../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/troubleshooting/performance/queries/transaction-lock-invalidation.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Transaction lock invalidation

**[Transaction lock invalidation](https://ydb.tech/docs/en/concepts/glossary.md?version=v26.1#tli)** (TLI) occurs when one transaction (the breaker) writes data and breaks the optimistic locks of another transaction (the victim). The victim detects this at commit time and receives a `transaction locks invalidated` error. The transaction must be retried. Frequent retries degrade application performance.

{% note info %}

The YDB SDK provides a built-in mechanism for handling temporary failures. For more information, see [Handling errors](https://ydb.tech/docs/en/reference/ydb-sdk/error_handling.md?version=v26.1).

{% endnote %}

## Preventing conflicts

- **Shorten transaction duration.** The longer a transaction holds locks, the higher the likelihood of a conflict. Where possible, avoid [interactive transactions](https://ydb.tech/docs/en/concepts/glossary.md?version=v26.1#interactive-transaction): the best approach is a single YQL query with `BEGIN;` and `COMMIT;` to read, modify, and commit data. If interactive transactions are necessary, execute `COMMIT` in the last query.

- **Reduce data overlap between transactions.** The fewer rows a transaction reads, the fewer locks it holds and the lower the chance of a conflict. Avoid reading unnecessary data. If multiple transactions compete for the same rows, reconsider your data model.

- **Use read-only transaction modes.** Transactions in [`Snapshot Read-Only`](https://ydb.tech/docs/en/concepts/transactions.md?version=v26.1#modes) mode read data from a consistent snapshot and do not acquire optimistic locks — such a transaction can never become a TLI victim. If a transaction does not modify data, explicitly set this mode in the SDK.

### Example: transaction conflict during product reservation

In an online store during a flash sale, hundreds of buyers may try to purchase the same product at the same time. A purchase transaction reads the current stock, then the application calculates discounts, waits for payment confirmation, and only after that decrements the stock:

```sql
SELECT available FROM stock WHERE sku = $sku AND warehouse_id = $warehouse_id;
-- application checks: available > 0
-- application calculates discounts, waits for payment ...
UPDATE stock SET available = available - 1 WHERE sku = $sku AND warehouse_id = $warehouse_id;
COMMIT;
```

The `SELECT` acquires an [optimistic lock](https://ydb.tech/docs/en/concepts/glossary.md?version=v26.1#optimistic-locking) on the row. While the transaction is waiting for payment, another transaction may update the same row and commit. This invalidates the lock, and the first transaction fails at commit time with a TLI error.

The solution is to reserve the product immediately in a short transaction, and perform all slower operations afterwards (discount calculation, payment, external service calls):

```sql
BEGIN;
UPDATE stock SET available = available - 1, reserved = reserved + 1
WHERE sku = $sku AND warehouse_id = $warehouse_id AND available > 0;

UPSERT INTO orders (order_id, sku, qty, status)
VALUES ($order_id, $sku, 1, "RESERVED");
COMMIT;
-- afterwards: calculate discounts, process payment, arrange delivery
```

If the payment succeeds, a separate transaction sets `status = "PAID"`. If it fails, another short transaction moves the item from `reserved` back to `available` and sets the status to `"CANCELLED"`. Because the reservation transaction completes quickly, it minimizes the time locks are held and reduces the probability of conflicts.


## Diagnostics

### Monitoring in Grafana

<!-- The include is added to allow partial overrides in overlays  -->
<!-- source: en/troubleshooting/performance/queries/_includes/transaction-lock-invalidation.md -->
1. Open the **[DB overview](https://ydb.tech/docs/en/reference/observability/metrics/grafana-dashboards.md?version=v26.1#dboverview)** Grafana dashboard.

1. See if the **Transaction Lock Invalidation** chart shows any spikes.

    ![](_assets/transactions-locks-invalidation.png)

    This chart shows the number of queries that returned the "transaction locks invalidated" error per second.
<!-- endsource: en/troubleshooting/performance/queries/_includes/transaction-lock-invalidation.md -->

### Diagnostics via TLI logs

When a transaction fails with a TLI error, the error message contains the victim query identifier:

```text
Transaction locks invalidated. ... VictimQuerySpanId: 1111111111111111.
```

Using this `VictimQuerySpanId`, you can find the full conflict context in the server logs: which query acquired the locks and which one broke them. For details on enabling logging, log entry format, event correlation, and the `find_tli_chain` utility for automated log analysis, see [TLI logging](https://ydb.tech/docs/en/troubleshooting/performance/queries/tli-logging.md?version=v26.1).

### Analysis via system views

The following system views are available for analyzing lock conflicts:

#### Query-level analysis

To identify queries with the most conflicts, use the [`.sys/query_metrics_one_minute`](https://ydb.tech/docs/en/dev/system-views.md?version=v26.1#query-metrics) system view:

```sql
SELECT QueryText, LocksBrokenAsBreaker, LocksBrokenAsVictim
FROM `.sys/query_metrics_one_minute`
WHERE LocksBrokenAsBreaker > 0 OR LocksBrokenAsVictim > 0
ORDER BY LocksBrokenAsBreaker + LocksBrokenAsVictim DESC;
```

| Column | Description |
|:-------|:------------|
| `LocksBrokenAsBreaker` | How many times this query broke other transactions' locks |
| `LocksBrokenAsVictim` | How many times this query's locks were broken |

Queries with a high `LocksBrokenAsBreaker` are breakers: they cause other transactions to roll back. Queries with a high `LocksBrokenAsVictim` are victims.

#### Partition-level analysis

To analyze broken locks at the table partition level, use the following system views:

* [`.sys/partition_stats`](https://ydb.tech/docs/en/dev/system-views.md?version=v26.1#partitions) — current partition statistics, contains the cumulative `LocksBroken` field
* [`.sys/top_partitions_by_tli_one_minute`](https://ydb.tech/docs/en/dev/system-views.md?version=v26.1#top-tli-partitions) — top 10 partitions with non-zero broken locks for one-minute intervals
* [`.sys/top_partitions_by_tli_one_hour`](https://ydb.tech/docs/en/dev/system-views.md?version=v26.1#top-tli-partitions) — top 10 partitions with non-zero broken locks for one-hour intervals

Example query to find partitions with the most broken locks:

```sql
SELECT
    Path,
    SUM(LocksBroken) as TotalLocksBroken
FROM `.sys/partition_stats`
GROUP BY Path
ORDER BY TotalLocksBroken DESC
LIMIT 10;
```

Example query to view the history of broken locks by partition:

```sql
SELECT
    IntervalEnd,
    LocksBroken,
    Path
FROM `.sys/top_partitions_by_tli_one_hour`
WHERE IntervalEnd BETWEEN Timestamp("2000-01-01T00:00:00Z") AND Timestamp("2099-12-31T00:00:00Z")
ORDER BY IntervalEnd DESC, LocksBroken DESC
LIMIT 100;
```