Managing Transactions

Transactions keep related changes together. If one statement fails, the whole group rolls back, preserving a consistent state. The marketplace schema benefits from transactions when placing orders, recording payments, or adjusting inventory.

Learning Goals

• Use BEGIN, COMMIT, and ROLLBACK to wrap multi-statement changes
• Understand ACID guarantees in the context of the marketplace database
• Apply savepoints and error handling to partial operations
• Choose isolation levels appropriate for commerce workflows

Transaction Basics

Place an order atomically

START TRANSACTION;

INSERT INTO orders (order_id, customer_id, order_date, status, order_total, shipping_city)
VALUES (1001, 5, '2025-04-10', 'Paid', 89.98, 'Hyderabad');

INSERT INTO order_item (order_id, product_id, quantity, unit_price, discount)
VALUES
  (1001, 1, 1, 59.99, 0),
  (1001, 8, 1, 29.99, 0);

UPDATE product SET stock_qty = stock_qty - 1 WHERE product_id = 1;
UPDATE product SET stock_qty = stock_qty - 1 WHERE product_id = 8;

INSERT INTO transactions (order_id, transaction_time, payment_method, amount, status)
VALUES (1001, '2025-04-10 11:30', 'Card', 89.98, 'Captured');

COMMIT;

If any statement fails (for example, a duplicate order_id), switch the final COMMIT to ROLLBACK to undo every change.

Rolling back on failure

START TRANSACTION;

INSERT INTO orders (order_id, customer_id, order_date, status, order_total, shipping_city)
VALUES (1002, 5, '2025-04-10', 'Paid', 59.99, 'Hyderabad');

-- Suppose stock is too low
SELECT stock_qty
FROM product
WHERE product_id = 1;

-- If stock_qty < 1 then
ROLLBACK;
-- ELSE
--   COMMIT;

Savepoints

Savepoints allow partial rollbacks while keeping earlier work.

Mixing seed data with optional inserts

START TRANSACTION;

INSERT INTO category (category_id, category_name) VALUES (9, 'Outdoor Gear');

SAVEPOINT after_category;

INSERT INTO product (product_id, sku, product_name, price, stock_qty, category_id)
VALUES (22, 'OG-9001', 'Camping Lantern', 35.00, 60, 9);

-- If we detect a duplicate SKU
ROLLBACK TO after_category;

-- Continue with other statements
INSERT INTO seller (seller_id, store_name, first_name, last_name, email, joined_at, rating)
VALUES (21, 'PeakSupply', 'Rita', 'Narayan', '[email protected]', '2025-04-01', 4.6);

COMMIT;

Isolation Levels (Conceptual Overview)

Most teaching scenarios can run at the default READ COMMITTED level. For high-stakes operations - like batch posting of refunds - you may choose higher isolation:

• READ COMMITTED: Prevents reading uncommitted payment changes, usually sufficient.
• REPEATABLE READ: Ensures each analyst sees the same order totals during reconciliation.
• SERIALIZABLE: Strictest; transactions behave as if they run one after another.

Engines set isolation differently:

PostgreSQL example

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- payout reconciliation statements here
COMMIT;

MySQL example

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- critical updates
COMMIT;

MySQL (InnoDB) defaults to REPEATABLE READ. If you need a different isolation level for a session, set it explicitly before START TRANSACTION.

Best Practices in the Marketplace Schema

• Keep transactions short: Complete order placement quickly to avoid locking tables while analytics queries run.
• Validate first: Run SELECT statements to confirm prerequisites (e.g., stock availability) before starting the transaction.
• Log changes: Insert into an audit_log table within the transaction so you can trace who created an order or issued a refund.
• Handle errors: Higher-level application code should catch exceptions and issue ROLLBACK.

Summary

• Group related statements with transactions so marketplace data remains consistent.
• Use savepoints when you can recover from a portion of the work failing.
• Pick an isolation level that balances accuracy with concurrency needs.
• Always match every BEGIN with either COMMIT or ROLLBACK.

Quiz

Show quiz

1. Which property ensures that both inserts succeed or fail together when placing an order?
   A) Consistency
   B) Atomicity
   C) Isolation
   D) Durability

2. Why would you use a savepoint when seeding new products?
   A) To speed up inserts
   B) To roll back a subset of statements while keeping the rest
   C) To change isolation level mid-transaction
   D) To enable parallel writes

3. In MySQL, how do you request a higher isolation level at the start of a transaction?
   A) SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION;
   B) BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   C) LOCK TABLE orders;
   D) SET GLOBAL tx_isolation = 'REPEATABLE-READ';

4. True or False: If a transaction finishes without ROLLBACK or COMMIT, the database automatically commits.
   A) True
   B) False

---

Answers

1. B
2. B
3. A
4. B