Transactions: Case Study

1. Transactions:Case Study Transactions at the Supermarket’s Pay-Desk Svetlin Nakov National Academy for Software Development academy.devbg.org

2. Session and Transaction: Case Study • We have a supermarket and we need to process orders • An order is a set of order items (product, quantity) entered with a bar-code reader • Processing a set of order items can take few minutes • We should keep the transactions small to allow high concurrency • What we can do?

3. Solution 1 Add Each Item in Separate Transaction

4. Case Study: Solution 1 • Create an order in state active=false, save it and commit the transaction • Commit a transaction for each order item • Persist orders items in the database in active=false state • If save of order item fails, rollback the transaction and correct the invalid item • Finally start an additional transaction, process the payment and commit it • Change the order state to active=true

5. Case Study: Solution 1 • We have a series of small transactions • Don’t keep long transactions in the DB • Works well for Web applications • How to deal with the following: • Customer takes the last bottle of vodka but does not checkout • Next customer comes and no vodka is available and goes off • The first customer cancel his order • We have 2 customers but have no sale

6. Solution 2 Keep Long TransactionPerform Critical Changesin the Last Moment

7. Case Study: Solution 2 • Create an order and keep the transaction open during the processing of this order • For each order item save it in the database and post the changes to DB • If save fails correct the invalid item and post it again (transaction is kept open) • Finally process the payment (update product amounts and cash amounts) and commit the transaction • If something fail, rollback the entire transaction

8. Case Study: Solution 2 • We have only one transaction • We kept it open for a long (few minutes) • We add order items without changing the amount of ordered products • Finally we change shared data (cash amount and product amounts) just before commit, when the customer pays • The transaction is long but the time we keep locked records in small (few milliseconds)

9. Case Study: Solution 2 • At the final stage some products can be unavailable • We still use optimistic locking • This gives good scalability • Good for desktop applications only! • When concurrent users are not too much • Not applicable for Web scenario

10. Solution 3 Disconnected ModelKeep All Changes in the Memory; Small Transaction Commits All of Them at Once

11. Case Study: Solution 3 • Don't start any session and transaction • Create an order in memory only (in transient objects) • For each order item create it in memory only (in transient objects) • Immediate data validation is not possible! • Finally start session and transaction, save the order and order items,process the payment and commit the transaction • If something fail, rollback thetransaction

12. Case Study: Solution 3 • Classical “disconnected model” • Efficient, optimistic locking • Hard to implement • If an order item fails to post in the DB, the entire order should be canceled • No way to correct one item • Good for mobile applications • Avoid in Web and Desktop scenarios

13. Solution 4 Just Pessimistic Locking

14. Case Study: Solution 4 • Start a transaction with serializable isolation level • For each order item immediately post changes in the database • Immediately correct the products availability and cash amount • Finally commit the transaction • Concurrent customers should wait each other • No concurrent transactions

15. Transactions:Case Study Questions?