Hotspot (databases)

from Wikipedia, the free encyclopedia

A hotspot in a database is the name given to data elements that are used and modified by (almost) all transactions.

Illustrative example of a hotspot

There are often data records in databases that want to be changed by all transactions. The article uses the following example for illustration:

  • The many telephone orders are recorded at the same time in a call center of a home shopping station. The following sequence is used:
    • At the beginning, the customers indicate the number of the desired article, which is then reserved, i.e. the available stock is reduced.
    • Then the customer data is recorded.
    • Finally, a summary is read out to the customer, which he must confirm in order for the order to be completed.

If the customer decides otherwise at the end of the call, the entire process must be reversed and the items that have been marked must be released again. Usually, such processes are dealt with in one transaction to ensure that half-completed orders do not block inventory. The hotspot is formed here above the inventory of the product that is currently being advertised. It must be ensured that all telephone operators have current data on the inventory and can work with it at the same time and that no more items can be sold if the quota has been used up.

Solution approaches and their problems

Separate read and write access

You could first read out the number of available articles in order to reduce the desired number and write the result back into the database. 

$bestand= sql select bestand from lager where produkt_id = $id
$bestand-=3
sql update lager set bestand = $bestand where produkt_id = $id

Problems: With this approach there is an acute risk of a deadlock : After having calculated the inventory, the transaction of customer A wants to extend the existing read lock (others can still read the table) into a write lock (others cannot access the table) to write the new inventory in the table. However, transaction of customer B has in the meantime received a read lock and thus the two transactions block each other, as transaction A only receives the write lock when all read locks are lifted, but transaction B can only continue when A has lifted its write lock. The two transactions block each other. It is important to know that a transaction can only tighten its locks before the end of the transaction ( commit or abort ), but cannot loosen or remove them.

Combined read and write access

The inventory is changed directly in the database in one step 

sql update lager set bestand = bestand - 3 where produkt_id = $id

Improvements & problems: This means that deadlocks can no longer occur. However, everyone else must wait for the transaction to unlock. The transactions can only be processed one after the other or, in other words, only one operator can process an order at a time.

Field calls

Example with field calls

The idea behind field access is to split the hotspot request into two parts, whereby in some cases one of the two is not required:

  1. a precondition (predicate) (in our example: passed> 3)
  2. a transformation ( in our example: consisted = consisted - 3)

Field accesses are processed according to the following system:

  1. Immediate test of the precondition. The data element is given a read lock and is released again unchanged
  2. Cancellation and rollback of the transaction if the test results in "false". (In the example, the stock would be less than 3 and the operator would receive a message that there are no longer enough items)
  3. An entry with a precondition and conversion is created in the rollback log, which is executed at the end of the transaction.
  4. The completion of the transaction consists of two phases:
    1. All rollback entries for the current transaction are processed and locks for field access are requested (read locks for access without transformation, write locks for access with transformation). Then all preconditions are tested one more time. If one or more tests fail here, the transaction is rolled back, otherwise phase 2 continues:
    2. Apply all transformations and complete the transaction by releasing all locks
sql update hotspot lager set bestand = bestand - 3 where produkt_id = $id and bestand > 3

Improvements & problems: In the case of field access, all blocking actions are collected and carried out shortly before completion. This prevents the transactions from blocking each other for a long time. This creates the problem that in phase 1 in the fourth step a precondition that was successful at the time of the actual check still fails and can thus lead to the transaction being aborted. It can happen that a caller, who takes a long time to record the data, goes away empty-handed despite previous availability, because the stock has been used up in the meantime. Another problem is reading the data. They always contain the original value. In the example, this would mean that with a stock of 6 items and three operators, whose customers want to order two items each, a fourth operator can apparently still sell 6 items to a customer, since with this solution the stocks only change when the transaction is completed become.

Escrow locks

Example with escrow lock

The escrow block also logs the inquiries of the transactions and calculates an interval in which the actual value must be. The upper limit of the interval corresponds to the case that all transactions terminate, the lower limit occurs when all transactions terminate. So if the lower limit is less than the precondition for a transaction, then this transaction cannot be ended at this point in time. Depending on the implementation, it may, however, if its precondition is below the upper limit of the interval, waits until it is clear whether the transaction can be ended or whether it will be aborted directly. Logically, the escrow lock only works with values ​​that can be sorted into a clear order, usually numbers.

Improvements & problems: The escrow lock fixes both the problem with the abortions in phase 1 during field access and the "unfair" order in the example.

See also

literature

  • Jim Gray , Andreas Reuter: Transaction Processing: Concepts and Techniques (Morgan Kaufmann Series in Data Management Systems) . Morgan Kaufmann, ISBN 1558601902

Sources / web links