This paper examines core database management challenges in a retail boutique setting, organized into two parts. Part 1 addresses concurrency control, explaining why atomic transactions are essential for maintaining data consistency, how lost updates can occur when multiple transactions access the same field simultaneously, and how locking mechanisms prevent such errors. It also analyzes deadlock scenarios under pessimistic locking and compares optimistic versus pessimistic locking strategies. Part 2 covers database security and backup/recovery, including assigning appropriate access rights to different user groups and evaluating the strengths and weaknesses of the existing backup plan, with recommendations for adopting an automated rollback/rollforward recovery system.
Managing a retail database requires careful attention to how multiple transactions interact when they share the same data. When several operations read and write the same fields simultaneously, issues such as lost updates, deadlocks, and data inconsistency can arise. The following questions examine these challenges in the context of Bev's Boutique, a retail store whose database handles sales, inventory restocking, and price adjustments concurrently.
The necessity of an atomic transaction is to maintain state consistency in the database system. Since various transactions require the use of the same fields and data within the database, a sequential update to each field — prioritized based on the order in which transactions are conducted — could lead to an imbalance in the overall system.
For example, assuming a few items are sold to a customer and the sales information is updated, there is a need for an immediate update to the QuantityOnHand field (a reduction, since a certain number of items have been sold), along with updates to the Total Sales, Profit, and other relevant fields. Atomic implementation of transactions also allows for easy recall and rollback of temporary updates to fields during the transaction processing period. This ensures that the database is left unchanged in the case of a failed event — for example, if the customer's card is declined — which maintains the correct state of the database.
In the case of Bev's store, a concurrency problem in database updates can arise when the store is being restocked due to the arrival of new items from vendors. Store personnel are expected to stock the items and run an Item Quantity Received Transaction, which updates the QuantityOnHand field. Since this transaction is independent of the sales transaction — which also updates QuantityOnHand — a concurrency conflict is possible.
Suppose one of the items being restocked is a hand-crafted handbag, and a sales transaction is initiated at the same time as an Item Quantity Received Transaction for five extra units of that bag. Both the sales transaction and the Item Quantity Received Transaction read a copy of the QuantityOnHand field value for the bag into temporary memory within their own local function scopes.
If the sales transaction has priority — perhaps because it was initiated slightly earlier — it decrements the QuantityOnHand field by the input quantity value and updates the field accordingly. However, the Item Quantity Received Transaction still operates on the earlier copied variable value of QuantityOnHand and increments it by five. Upon update, the new QuantityOnHand will be overstated by a factor equal to the sales input quantity, because it does not reflect the decrement caused by the sale.
Resource locking disallows sharing of a field value that has been requested for update. Once one transaction has requested an update on a field, that field is temporarily made unavailable to other transactions until the update is registered as either failed or successful. This directly solves the problem described above, because resource locking allows only one transaction at a time to access the QuantityOnHand field.
The sales transaction, having requested access first, must finish its update on QuantityOnHand — decrementing it by the input quantity — before the Item Quantity Received Transaction can access the field. When the Item Quantity Received Transaction then increments QuantityOnHand by five, the value will accurately reflect the prior decrement, yielding a correct result.
Under a pessimistic locking strategy in which entire tables are locked before access and all locks are held until the end of a transaction, a deadlock between two concurrent sale transactions is possible if there is no defined order in which transactions obtain and release locks.
For example, suppose Customer A orders a shirt and then adds a tie. Customer A's transaction would proceed as follows: lock shirt; decrement QuantityOnHand for shirt by the input quantity and update; lock tie; decrement QuantityOnHand for tie by the input quantity and update; release all locks.
Now suppose Customer B's transaction requests the tie first and then the shirt: lock tie; decrement QuantityOnHand for tie by the input quantity and update; lock shirt; decrement QuantityOnHand for shirt by the input quantity and update; release all locks.
When both transactions run concurrently, the database receives the following requests: lock shirt for A; lock tie for B; decrement QuantityOnHand for shirt and update for A; decrement QuantityOnHand for tie and update for B; wait for the lock on tie to be released by B before updating for A; wait for the lock on shirt to be released by A before updating for B; release all locks for A and then B.
As this sequence illustrates, each transaction is waiting on the other to release a lock it holds, creating an indefinite waiting period — that is, a deadlock.
Under the same pessimistic locking approach, a deadlock between a sale transaction and an Item Quantity Received Transaction is not possible. These two transactions share only the QuantityOnHand field. Since the pessimistic approach locks a resource before updating it, whichever process first requests the lock must complete its update and release the lock before the other can proceed. Regardless of the order in which the transactions arrive, there can be no circular waiting, and therefore no deadlock. There may, however, be a delay if one transaction takes a long time to complete.
Considering all the scenarios above, the optimistic locking approach is the more suitable option for Bev's Boutique. The optimistic locking technique checks for consistency between the data about to be updated and the current value of the same field. If no changes have occurred since the transaction was initiated, the lock is applied, the update is accepted, and a verification confirms a successful update.
"Comparing locking strategies for retail transactions"
"Access rights table for four user groups"
"Problems with current backup plan and recommendations"
Always verify citation format against your institution’s current style guide requirements.