Real-World Transaction Examples

🎤 TicketPurchaseTransaction()

-- TicketPurchaseTransaction(): Complete ticket purchase with payment
BEGIN TRANSACTION

-- Step 1: Check available inventory
SELECT available_tickets 
FROM Concerts 
WHERE concert_id = 'taylor_swift_123';

-- Step 2: Verify payment authorization  
-- (External payment gateway call happens here)

-- Step 3: Reserve the tickets atomically
UPDATE Concerts 
SET available_tickets = available_tickets - 2 
WHERE concert_id = 'taylor_swift_123';

-- Step 4: Create customer order record
INSERT INTO Orders (user_id, concert_id, num_tickets, total_amount, status)
VALUES ('user_789', 'taylor_swift_123', 2, 150.00, 'confirmed');

-- Step 5: Generate specific ticket records
INSERT INTO Tickets (order_id, user_id, concert_id, seat_number)
VALUES 
  (LAST_INSERT_ID(), 'user_789', 'taylor_swift_123', 'A15'),
  (LAST_INSERT_ID(), 'user_789', 'taylor_swift_123', 'A16');

COMMIT;

🔍 ACID Focus: Atomicity Guarantee

🚧 UserLimitEnforcementTransaction()

-- UserLimitEnforcementTransaction(): Enforce 7-ticket limit per user
BEGIN TRANSACTION

-- Step 1: Get user's current ticket count
SELECT SUM(num_tickets) AS current_tickets
FROM Tickets 
WHERE user_id = 'user_456';

-- Step 2: Validate purchase doesn't exceed limit
-- Business rule: Maximum 7 tickets per user
-- (Application checks: current_tickets + requested_tickets <= 7
-- (Payment processing for additional tickets.)

-- Step 3: If valid, proceed with purchase
UPDATE Concerts 
SET available_tickets = available_tickets - 3
WHERE concert_id = 'concert_789'
AND available_tickets >= 3;

-- Step 4: Record the purchase
INSERT INTO Tickets (user_id, concert_id, num_tickets, purchase_date)
VALUES ('user_456', 'concert_789', 3, NOW());

COMMIT;

🔒 ACID Focus: Isolation Protection

🏦 MoneyTransferTransaction()

-- MoneyTransferTransaction(): Transfer money between accounts
BEGIN TRANSACTION

-- Step 1: Verify sufficient funds
SELECT balance 
FROM Accounts 
WHERE account_id = 'alice_account' 
AND balance >= 100.00;

-- Step 2: Perform atomic transfer
UPDATE Accounts 
SET balance = balance - 100.00 
WHERE account_id = 'alice_account';

UPDATE Accounts 
SET balance = balance + 100.00 
WHERE account_id = 'bob_account';

-- Step 3: Log the transfer
INSERT INTO Transactions (from_account, to_account, amount, type)
VALUES ('alice_account', 'bob_account', 100.00, 'transfer');

COMMIT;

🏦 InterestCalculationTransaction()

-- InterestCalculationTransaction(): Calculate monthly interest
BEGIN TRANSACTION

-- Apply 6% interest to all accounts
UPDATE Accounts 
SET balance = balance * 1.06,
    last_interest_date = NOW();

INSERT INTO Transactions (account_id, amount, type)
SELECT account_id, balance * 0.06, 'interest' 
FROM Accounts;

COMMIT;

🔄 ACID Focus: Consistency Guarantee

📦 InventoryReservationTransaction()

-- InventoryReservationTransaction(): Reserve last item for purchase
BEGIN TRANSACTION

-- Step 1: Check inventory
SELECT quantity 
FROM Products 
WHERE product_id = 'labubu';

-- Step 2: Validate sufficient inventory
-- (Application logic: if quantity >= requested_quantity)

-- Step 3: Reserve inventory and create order
UPDATE Products 
SET quantity = quantity - 1
WHERE product_id = 'labubu'
AND quantity >= 1;  -- Double-check in UPDATE

-- Step 4: Create customer order
INSERT INTO Orders (customer_id, product_id, quantity, status)
VALUES ('customer_alice', 'labubu', 1, 'confirmed');

COMMIT;

🛡️ ACID Focus: Isolation Protection

📱 LikeCounterTransaction()

-- LikeCounterTransaction(): Add like and update counter atomically
BEGIN TRANSACTION

-- Step 1: Prevent duplicate likes (business rule)
SELECT COUNT(*) 
FROM Likes 
WHERE user_id = 'user_12345' 
AND post_id = 'viral_post_456';

-- Step 2: Add like record and update counter atomically
INSERT INTO Likes (user_id, post_id, timestamp)
VALUES ('user_12345', 'viral_post_456', NOW());

-- Step 3: Increment like counter
UPDATE Posts 
SET like_count = like_count + 1,
    last_activity = NOW()
WHERE post_id = 'viral_post_456';

COMMIT;

📊 ACID Focus: Consistency Guarantee