Locking Reads (SELECT ... FOR UPDATE / FOR SHARE) and Why Not Everywhere

-- Ordinary SELECT: no lock, snapshot read
SELECT units_available FROM fd_offers WHERE offer_id = 101;
-- Another transaction can change units_available between this read and your update

-- Locking SELECT: exclusive lock held until COMMIT
SELECT units_available FROM fd_offers WHERE offer_id = 101 FOR UPDATE;
-- No other transaction can modify or lock this row until you commit

-- Protecting a fixed-deposit offer allotment at National Bank of India
BEGIN;

-- Step 1: lock the row
SELECT units_available, price_per_unit
FROM fd_offers
WHERE offer_id = 101
FOR UPDATE;

-- Step 2: check availability (only this transaction sees the locked row)
-- units_available = 1

-- Step 3: decrement
UPDATE fd_offers
SET units_available = units_available - 1
WHERE offer_id = 101;

INSERT INTO allotments (offer_id, customer_id, units, allotted_at)
VALUES (101, 'CUST-PRIYA', 1, CURRENT_TIMESTAMP);

COMMIT;  -- lock released

-- FOR SHARE: ensure the account exists and is active while inserting a transaction
BEGIN;

SELECT account_id, status FROM accounts
WHERE account_id = 'SAV2001'
FOR SHARE;
-- Shared lock: others can also read FOR SHARE, but nobody can UPDATE or DELETE

-- Now safely insert a transaction knowing the account cannot be closed
INSERT INTO transactions (account_id, amount, txn_type, created_at)
VALUES ('SAV2001', 50000.00, 'CREDIT', CURRENT_TIMESTAMP);

COMMIT;

-- DANGEROUS: no lock, overselling possible
-- Priya and Rajesh both read units_available = 1
SELECT units_available FROM fd_offers WHERE offer_id = 101;  -- both see 1

-- Both pass the check
-- Both decrement
UPDATE fd_offers SET units_available = units_available - 1 WHERE offer_id = 101;
-- units_available = 0 after first update, then -1 after second!
-- Oversold: two allotments for one unit

-- WRONG: FOR UPDATE on a read-only reporting query
SELECT a.account_id, a.balance, c.full_name
FROM accounts a
JOIN customers c ON a.customer_id = c.customer_id
WHERE a.branch = 'Mumbai-Central'
FOR UPDATE;  -- locks ALL accounts in Mumbai-Central!
-- Dashboard users block each other and block tellers doing real debits

-- CORRECT: plain read for reporting, no lock needed
SELECT a.account_id, a.balance, c.full_name
FROM accounts a
JOIN customers c ON a.customer_id = c.customer_id
WHERE a.branch = 'Mumbai-Central';

-- Deadlock scenario
-- Transaction A: locks SAV2001, then tries to lock SAV2002
-- Transaction B: locks SAV2002, then tries to lock SAV2001
-- Both block forever → database detects → one is aborted

-- Prevention: always lock accounts in sorted order
-- Transfer from SAV2001 to SAV2002:
BEGIN;
SELECT * FROM accounts WHERE account_id IN ('SAV2001', 'SAV2002')
ORDER BY account_id FOR UPDATE;  -- consistent order prevents deadlock
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 'SAV2001';
UPDATE accounts SET balance = balance + 50000 WHERE account_id = 'SAV2002';
COMMIT;

package com.bank.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class FdAllotmentService {

    // WRONG: holds lock while calling external payment gateway
    public void allotUnsafe(Connection conn, int offerId,
                             String customerId) throws Exception {
        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement(
            "SELECT units_available FROM fd_offers WHERE offer_id = ? FOR UPDATE");
        ps.setInt(1, offerId);
        ResultSet rs = ps.executeQuery();
        rs.next();
        // Lock is held while calling external API!
        callExternalPaymentGateway(customerId);  // may take seconds
        // Other transactions blocked for seconds
        conn.commit();
    }

    // CORRECT: minimize lock duration
    public void allotSafe(Connection conn, int offerId,
                           String customerId) throws Exception {
        // External call first, outside the lock
        callExternalPaymentGateway(customerId);

        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement(
            "SELECT units_available FROM fd_offers WHERE offer_id = ? FOR UPDATE");
        ps.setInt(1, offerId);
        ResultSet rs = ps.executeQuery();
        rs.next();
        int units = rs.getInt(1);
        if (units <= 0) {
            conn.rollback();
            throw new IllegalStateException("Sold out");
        }
        conn.prepareStatement(
            "UPDATE fd_offers SET units_available = units_available - 1 WHERE offer_id = " + offerId
        ).executeUpdate();
        conn.commit();  // lock held for milliseconds, not seconds
    }

    private void callExternalPaymentGateway(String customerId) {
        // simulated external call
    }
}