Long Transaction with User Interaction (Locks Held for Minutes)

-- Wrong: Wait for user input during transaction

BEGIN;

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;

-- Show confirmation dialog to user: "Confirm purchase?"

-- User takes 5 minutes to click OK (locks held for 5 minutes)

-- Other users trying to buy same product are blocked

COMMIT;

-- Correct: Check inventory before transaction, no user interaction during transaction

-- Step 1: Check inventory (no locks)

SELECT quantity FROM inventory WHERE product_id = 100;

-- Result: 10 available

-- Step 2: Show confirmation dialog to user (no transaction active)

-- User takes 5 minutes to click OK (no locks held)

-- Step 3: Start transaction after user confirms

BEGIN;

UPDATE inventory SET quantity = quantity - 1

WHERE product_id = 100

AND quantity > 0; -- Recheck inventory (might have changed)

GET DIAGNOSTICS rows_updated = ROW_COUNT;

IF rows_updated = 0 THEN

ROLLBACK;

RETURN 'Product out of stock';

END IF;

COMMIT; -- Transaction held for < 1 second

Never wait for user interaction during transaction. User might take minutes (or hours) to respond, holding locks and blocking other transactions. Example: E-commerce checkout - user adds item to cart, transaction starts, user goes to lunch, transaction holds inventory lock for 1 hour, other customers cannot buy same product. Prevention: (1) Check inventory before transaction (no locks), (2) Show confirmation to user (no transaction active), (3) Start transaction after user confirms (locks held for < 1 second), (4) Recheck inventory in transaction (might have changed while user was deciding). Real-world: Amazon checks inventory before checkout, starts transaction only when user clicks "Place Order" (locks held for < 100ms).

Never wait for user input during transaction. Check data before transaction, show confirmation to user, start transaction after user confirms. Keep transactions under 1 second.

Critical

Transaction holds locks for 5 minutes while waiting for user input. Other users trying to buy same product are blocked (poor user experience). High risk of deadlock (other transactions waiting for locks). Database connection might timeout (transaction rolled back automatically).

graph TB

subgraph "Wrong: User Interaction in Transaction"

W1["BEGIN: Transaction starts"]

W2["UPDATE inventory: Lock acquired"]

W3["Show dialog: Wait for user<br/>5 minutes, locks held"]

W4["Other users blocked<br/>Cannot buy product"]

W5["COMMIT: Locks released<br/>After 5 minutes"]

end

subgraph "Correct: User Interaction Before Transaction"

C1["SELECT inventory: No locks"]

C2["Show dialog: Wait for user<br/>5 minutes, no locks"]