Multilevel database and cache optimization in high-contention distributed systems: performance determinants and time-bounded event delivery

Introduction

High-contention distributed systems operate under conditions of intensive concurrent data access, where multiple transactions compete for the same database rows, indexes, cache keys, and event queues. Under these conditions, system performance is determined not only by computational capacity but also by the characteristics of concurrent resource access. The primary performance determinants include transaction duration, resource acquisition order, request concentration on hot keys, cache miss rate, and latency of event-driven communication. An additional challenge is ensuring that events are processed within a predefined time window while preserving data consistency.

The aim of this study is to systematize the performance determinants of high-contention data operations, present a multilevel optimization model, and evaluate its effectiveness using a computational scenario.

Materials and methods

The study is based on an analysis of scientific literature on concurrent data access, caching, and distributed event-driven platforms, supplemented by a computational scenario representing a typical workload. The scenario assumes a system throughput of 1,200 operations per second, with reads accounting for 75% of all operations. The average read latency is set at 12 ms for database access and 1 ms for cache access, while the target event-processing window is 100 ms. For the hot key, the request rate is set at 40 accesses per second, with a lock hold time of 15 ms.

The following relationships were used for the evaluation:

The database load after applying caching is calculated using the following equation:

, (1)

Where is the resulting database load (operations/s); is the write through put (operations/s); is the read throughput (operations/s); and is the cache hit ratio, representing the proportion of requests successfully served from the cache.

The average read latency is calculated as follows:

, (2)

Where is the average read latency (ms); is the cache access time (ms); and is the data read time from the primary database (ms).

The total event processing time is calculated as follows:

, (3)

Subject to the following constraint:

, (4)

Where is the total event processing time (ms); is the lock wait time (ms); is the time required to record an event in the transactional outbox (ms); is the message transmission time through the message broker (ms); is the event processing time at the consumer (ms); is the cache update or invalidation time (ms); and is the allowable event processing time window (ms).

Results and discussion

The performance determinants at the database layer can be grouped into several categories:

1. Request distribution, where a significant proportion of requests targeting a limited number of records leads to the hot-key effect [5, p. 143-157; 6, p. 191-208; 8, p. 3531-3544].
2. Transaction characteristics, including transaction duration and the order of data updates. The longer a transaction holds a row-level lock, the greater the likelihood of lock waiting and deadlocks [7, p. 2325-2338; 8, p. 3531-3544].
3. Indexing efficiency and cache miss rate, since every cache miss results in an additional database read and increases the database load [4, p. 886-897; 6, p. 191-208].
4. Event-driven communication, where delays in message publication, acknowledgment, and consumption contribute to the overall completion time of a business operation [1, p. 298-309; 9, p. 3110-3121; 10, p. 74-80].

The overall classification of database-layer performance determinants is presented in figure 1.

Fig. 1. Database-Layer Performance Determinants

The mechanisms for reducing lock contention can be classified as follows:

1. Transaction-level measures (reducing transaction duration, eliminating external calls within transactions, and enforcing a consistent object update order).
2. Structural measures (hot-record segmentation, partitioning, and queue separation) [5, p. 143-157; 8, p. 3531-3544].
3. Caching measures (cache-aside, TTL, cache prewarming, and cache stampede prevention) [4, p. 885-897; 5, p. 143-157; 6, p. 191-208].
4. Event-driven measures (transactional outbox, idempotency, deduplication, and consumer lag monitoring) [3; 9, p. 3110-3121].
5. Application-level measures (limited retries, backoff strategies, and prevention of cascading retry storms).

The proposed multilevel optimization model (fig. 2) comprises four interconnected layers. At the database layer, optimization is achieved through short transactions, efficient indexing, and segmentation of highly contended objects. At the cache layer, the model reduces database read load, while the event layer ensures orderly and predictable message delivery. At the application layer, idempotency and control of the overall operation time budget are maintained [2, p. 374-398; 3].

Fig. 2. Multilevel Optimization Model for a Distributed System with Time-Window Control

The model calculations showed that, with a total through put of 1,200 operations per second and a read ratio of 75%, the system performs 900 read operations and 300 write operations per second. At a cache hit ratio of 85%, the database load is reduced to 435 operations per second, compared with 1,200 operations per second in the absence of caching. This corresponds to a 63.8% reduction in database load. The average read latency decreases from 12 ms to 2.65 ms, representing a 77.9% reduction.

For the hot key, the estimated lock wait time is 22.5 ms at an initial request rate of 40 accesses per second. After segmenting the key into four logical partitions, the lock wait time decreases to 2.65 ms, representing an 88.2% reduction. Thus, even a simple structural partitioning of a highly contended object can substantially reduce resource contention.

The final evaluation of the event time budget showed that, in the baseline configuration, the total processing time – including database latency, lock wait time, outbox persistence, message broker transmission, consumer processing, and cache invalidation – amounts to 101.5 ms, thereby exceeding the target time window. After applying the proposed multilevel optimization model, the total processing time is reduced to 49.65 ms, providing a time margin of 50.35 ms (fig. 3).

Fig. 3. Results of the High-Contention Scenario Simulation

The results demonstrate that the performance of high-contention operations depends on the coordinated optimization of multiple system layers. Optimizing only SQL queries or only the cache is insufficient to achieve the desired performance gains, as the overall improvement is realized only when read latency is reduced, lock contention is minimized, and the total event processing time is controlled across all stages of the processing pipeline.

Conclusion

The findings indicate that the performance determinants of high-contention data operations in distributed systems are primarily associated with locking behavior, request distribution, caching efficiency, and the latency of event-driven communication. The simulation results demonstrate that a multilevel optimization approach can substantially reduce database load, mitigate lock contention, and ensure event processing within the specified time window. From a practical perspective, the most effective measures include short transactions, hot-object segmentation, efficient caching, the transactional outbox pattern, and idempotent application-layer processing. The combined application of these techniques significantly improves the performance of distributed systems operating under intensive concurrent workloads.