Transactions: Case Study

1. Transactions:Case Study Transactions at the Supermarket’s Pay-Desk Svetlin Nakov Telerik Software Academy http://academy.telerik.com Manager Technical Training http://www.nakov.com SQL

2. In the Supermarket: 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? 2

3. Supermarket: Typical Scenario • A customer comes at the pay desk • With a full shopping cart • The cashier starts processing his order • Creates a new order which is unconfirmed • The cashier adds each order item to the order • By scanning its bar-code and entering an amount • In case of mistake, the ordered items can be corrected (added / removed / amount changed) • At this point the order is still unconfirmed (temporary until paid) 3

4. Supermarket: Typical Scenario (2) • Finally the customer pays • The entire order is confirmed • The paid amount is added to the cash amount of the supermarket • All ordered items are removed from the products availability of the supermarket • At anytime the order can be canceled • This cancels the order and all ordered items • No payment is made • Products availability stays unchanged 4

5. Solution 1 Add Each Item in a Separate Transaction

6. Case Study: Solution 1 • Create an order in state confirmed=false, save it and commit the transaction • Add each order item in its own transaction • Persist order items in the database in confirmed=falsestate • If saving 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 confirmed=true • Delete old unconfirmed orders per 1 hour 6

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

8. Solution 2 Keep Long Transaction,but Perform Critical Changesin the Last Moment

9. Case Study: Solution 2 • Create an order and keep the transaction open during the processing of the 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 (this could be complicated) • Finally process the payment and commit the transaction • Update the product amounts and cash amounts • If something fails, rollback the transaction 9

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

11. Case Study: Solution 2 • At the final stage (payment) some products can be unavailable • We still use optimistic locking • This gives good scalability • Works for desktop applications only! • When the transaction could be kept for long • When concurrent users are not too much • Not applicable in Web scenarios 11

12. Disconnected Model:Keep All Changes in the Memory; Small Transaction Commits All of Them at Once Solution 3

13. Case Study: Solution 3 • Don't start a transaction or DB session • Create an order in memory only • Keep it in transient in-memory objects • For each order item create it in memory only • Immediate data validation is not possible! • Integrity constraints cannot be checked in the DB • Finally start session and transaction, save the order and order items, process the payment and commit the transaction • If something fails, rollback thetransaction 13

14. 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 • Very hard to correct a single incorrect item • Good for mobile applications • When the connection is not always available • Avoid in Web and Desktop scenarios 14

15. Solution 4 Pessimistic Locking

16. Case Study: Solution 4 • Start a long-running transaction with serializable isolation level • For each order item immediately post its data in the database • Immediately correct the products availability and cash amount in the supermarket • Finally commit the transaction • Concurrent customers should wait each other • No concurrent transactions  does not scale • Avoid this approach! 16

17. Transactions: Case Study http://academy.telerik.com