Library Management LLD: Members, Loans and Fines

Rules that must hold across branches break, because no branch can see the others. A member's book limit, the fine rules and the waiting list all drift apart.

Picture a common setup. A paid lending library has 9 branches, about 6,500 members and 48,000 books. Members pay a yearly plan fee and borrow books to take home, and each branch keeps its own register. The setup is made up, but each problem below is one a shared design must prevent.

• A member on the 2-book Basic plan had 5 books out. He borrowed 2 or 3 at a time at different branches, and no branch could see the others.
• Two branches charged late fines differently. One capped the fine and the other kept counting, so a member owed Rs 900 on a Rs 350 novel.
• One copy of a popular thriller was promised to two members on the waiting list. Both came to collect it on the same evening.

The library wants one system for all nine branches. The first step is a library management LLD, written before any screen or table exists.

Low-level design, or LLD, means deciding the classes, their fields, their methods and how they call each other before you write the real code. A class is a blueprint in code, like Member. An object is one real thing made from it, like the member standing at the desk.

A library management LLD is the set of classes and rules that let a library lend copies of books to members, take them back, keep waiting lists and charge fines. It is a design for one service, not for a whole fleet of servers.

It makes a good interview question for one reason. Everyone knows how borrowing works, so the time goes to rules instead of learning a new business. The real test is keeping those rules correct when members, copies and dates all change together.

Write the scope in plain sentences first. This short list decides what the design must do, and what it can safely leave out.

• A member joins on a plan and renews it every year.
• A librarian issues a copy to a member, and the system sets a due date.
• The member returns the copy at any branch, and the system works out any late fine.
• A member can renew a loan, unless someone else is waiting for that book.
• A member can place a hold on a book with no free copy.
• Members with too many unpaid fines cannot borrow until they pay.

Out of scope for now: buying new books, the online catalogue search and staff payroll. Saying this out loud in an interview shows you know where the design stops.

The main classes are Member, MembershipPlan, Book, BookCopy, Loan, Hold and Fine. Each one owns a small set of facts, and each rule lives next to the facts it needs.

The most important choice is to split Book from BookCopy. Book holds the title and the ISBN (International Standard Book Number). An ISBN names one edition in one format, so a paperback and an ebook of the same novel get different numbers.

BookCopy is one physical item with its own barcode, branch and status. The library owns 3 copies of one popular thriller, so that is one Book object and 3 BookCopy objects.

Without the split, you cannot say which copy is late, which copy is torn or which branch holds it. Loans and returns always happen to a copy. Holds are placed on a book, because the member wants any free copy.

Two small types help a lot. CopyStatus is an enum, a type with a fixed list of values: AVAILABLE, ON_LOAN, ON_HOLD_SHELF, IN_REPAIR and LOST. Money is kept in BigDecimal, Java's exact decimal number type, so amounts in rupees and paise (hundredths of a rupee) stay exact.

Model the member and the plan as two separate classes, and give the plan every number that changes from plan to plan. The member points at one plan and stores its own expiry date.

A prepaid mobile plan is a good picture. The operator's plan card lists the price, the validity and the data per day. Your SIM just points at one plan, and the operator can launch a new plan without changing anyone's phone.

Where the picture breaks: a mobile plan card holds only numbers. A library plan also holds a rule, the fine calculation, so part of the plan is code behind an interface.

The library sells three plans. These numbers are part of the made-up setup.

Here is the first part of one Java file, LibraryDemo.java, that the rest of this guide builds up. A record is a short Java class that only carries fixed values. The file works on Java 17 or later; we compiled and ran it on Java 17.0.20 and Java 21.0.12.

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.List;

record MembershipPlan(String name, int maxBooks, int loanDays, int maxRenewals,
                      FinePolicy finePolicy, BigDecimal fineBlockLimit) {}

final class Member {
    final String id;
    final MembershipPlan plan;
    final LocalDate expiresOn;
    final List<Loan> openLoans = new ArrayList<>();
    final List<Fine> fines = new ArrayList<>();

    Member(String id, MembershipPlan plan, LocalDate expiresOn) {
        this.id = id;
        this.plan = plan;
        this.expiresOn = expiresOn;
    }

    boolean isActiveOn(LocalDate today) { return !today.isAfter(expiresOn); }

    BigDecimal unpaidFines() {
        BigDecimal total = BigDecimal.ZERO;
        for (Fine f : fines)
            if (f.status == FineStatus.UNPAID) total = total.add(f.amount);
        return total;
    }
}

Because the numbers live in MembershipPlan, the library can add a student plan next year without touching the loan code. The loan code only asks the plan questions, like how many books or how many days.

Member answers simple questions about itself, like isActiveOn(today), so other classes never repeat that check. The plan is active up to and including its expiry date.

Expiry needs one careful rule. A member whose plan has expired cannot borrow, but can still return books and pay fines. Blocking returns would only keep copies away from the shelf longer.

Issuing a book means checking the member, checking the copy, then creating a Loan with a due date. Returning a book means closing that Loan, working out any fine, and deciding where the copy goes next.

Follow the diagram from the desk scan to the new loan. Every question box sends the member to the same refusal box, with a reason.

The book limit check now counts open loans across all nine branches, because every branch reads the same data. That alone fixes the member who had 5 books on a 2-book plan.

First come the copy and the loan. The copy guards its own status: moveTo refuses any move the CopyStatus enum does not allow. If you are new to Java, read canMoveTo and canBeIssuedTo first and skim the rest.

record Book(String isbn, String title) {}

enum CopyStatus {
    AVAILABLE, ON_LOAN, ON_HOLD_SHELF, IN_REPAIR, LOST;

    boolean canMoveTo(CopyStatus next) {
        return switch (this) {
            case AVAILABLE     -> next == ON_LOAN || next == IN_REPAIR;
            case ON_LOAN       -> next == AVAILABLE || next == ON_HOLD_SHELF || next == LOST;
            case ON_HOLD_SHELF -> next == ON_LOAN || next == AVAILABLE || next == ON_HOLD_SHELF;
            case IN_REPAIR     -> next == AVAILABLE;
            case LOST          -> false;   // final in this design
        };
    }
}

final class BookCopy {
    final String barcode;
    final Book book;
    private CopyStatus status = CopyStatus.AVAILABLE;
    private Hold heldFor;                  // set only while ON_HOLD_SHELF

    BookCopy(String barcode, Book book) {
        this.barcode = barcode;
        this.book = book;
    }

    CopyStatus status() { return status; }
    Hold heldFor() { return heldFor; }

    void moveTo(CopyStatus next, Hold hold) {
        if (!status.canMoveTo(next))
            throw new IllegalStateException(barcode + " cannot go from " + status + " to " + next);
        status = next;
        heldFor = hold;
    }

    boolean canBeIssuedTo(Member member) {
        if (status == CopyStatus.AVAILABLE) return true;
        return status == CopyStatus.ON_HOLD_SHELF && heldFor.member == member;
    }
}

final class Loan {
    final Member member;
    final BookCopy copy;
    LocalDate dueOn;
    LocalDate returnedOn;                  // null while the loan is open
    int renewals;

    Loan(Member member, BookCopy copy, LocalDate dueOn) {
        this.member = member;
        this.copy = copy;
        this.dueOn = dueOn;
    }
}

class LoanRefused extends RuntimeException {
    LoanRefused(String reason) { super(reason); }
}

Next is LoanService. A service is a class that runs one job across several objects. It is meant to be the only class that changes loans, so no screen can skip a rule. LoanRefused, defined above, is our own exception for a refused action.

final class LoanService {
    private final HoldQueue holds;         // only LoanService calls it, always under this lock

    LoanService(HoldQueue holds) { this.holds = holds; }

    synchronized Loan issue(Member member, BookCopy copy, LocalDate today) {
        MembershipPlan plan = member.plan;
        if (!member.isActiveOn(today))
            throw new LoanRefused("Membership has expired");
        if (member.openLoans.size() >= plan.maxBooks())
            throw new LoanRefused("Loan limit reached");
        if (member.unpaidFines().compareTo(plan.fineBlockLimit()) >= 0)
            throw new LoanRefused("Unpaid fines are too high");
        if (!copy.canBeIssuedTo(member))
            throw new LoanRefused("Copy is not free for this member");

        Hold hold = copy.heldFor();
        if (hold != null) hold.status = HoldStatus.COLLECTED;
        copy.moveTo(CopyStatus.ON_LOAN, null);
        Loan loan = new Loan(member, copy, today.plusDays(plan.loanDays()));
        member.openLoans.add(loan);
        return loan;
    }

    synchronized Fine returnCopy(Loan loan, LocalDate today) {
        if (loan.returnedOn != null)
            throw new LoanRefused("Loan is already closed");
        loan.returnedOn = today;
        loan.member.openLoans.remove(loan);
        Fine fine = null;
        BigDecimal amount = loan.member.plan.finePolicy().fineFor(loan.dueOn, today);
        if (amount.signum() > 0) {
            fine = new Fine(loan, amount);
            loan.member.fines.add(fine);
        }
        holds.sendToNextOrShelf(loan.copy, today);
        return fine;
    }

    synchronized void renew(Loan loan, LocalDate today) {
        if (today.isAfter(loan.dueOn))
            throw new LoanRefused("Loan is overdue: return it first");
        if (!loan.member.isActiveOn(today))
            throw new LoanRefused("Membership has expired");
        if (loan.renewals >= loan.member.plan.maxRenewals())
            throw new LoanRefused("No renewals left");
        if (holds.hasWaiting(loan.copy.book))
            throw new LoanRefused("Another member is waiting for this book");
        loan.renewals++;
        loan.dueOn = loan.dueOn.plusDays(loan.member.plan.loanDays());
    }

    synchronized Hold placeHold(Member member, Book book) { return holds.place(member, book); }

    synchronized void expireHold(BookCopy copy, LocalDate today) { holds.expire(copy, today); }
}

Three things in issue matter most. All four checks run before anything changes. The copy decides whether it is free for this member, which covers a copy waiting on the hold shelf for exactly this person. The due date comes from plan.loanDays(), never from a number typed into the code.

The word synchronized means only one thread can run any of these methods on the same LoanService object at a time. A thread is one line of work running alongside others, like two desks served by one program.

returnCopy refuses a loan that is already closed, so a double scan at the desk cannot charge a fine twice. It then hands the copy to the hold queue, which decides between the hold shelf and the open shelf.

In this one-file demo, that rule about LoanService is only a convention: Loan, Member and Fine fields are open to the whole file. Real code makes them private and changes them only through LoanService.

A late fine is the number of days between the due date and the return date, times the plan's rate per day, limited by a cap. If the book comes back within the grace days, the fine is zero.

The cap is exactly what one branch forgot. The Rs 900 fine on a Rs 350 novel came from a fine with no ceiling.

Write the fine rule as its own small piece. FinePolicy is an interface, a list of methods a class promises to have. DailyCappedFine is one class that keeps that promise, with a rate, a cap and grace days.

interface FinePolicy {
    BigDecimal fineFor(LocalDate dueOn, LocalDate returnedOn);
}

final class DailyCappedFine implements FinePolicy {
    private final BigDecimal perDay;
    private final BigDecimal cap;
    private final int graceDays;

    DailyCappedFine(BigDecimal perDay, BigDecimal cap, int graceDays) {
        this.perDay = perDay;
        this.cap = cap;
        this.graceDays = graceDays;
    }

    public BigDecimal fineFor(LocalDate dueOn, LocalDate returnedOn) {
        long daysLate = ChronoUnit.DAYS.between(dueOn, returnedOn);
        if (daysLate <= graceDays) return BigDecimal.ZERO;
        BigDecimal fine = perDay.multiply(BigDecimal.valueOf(daysLate));
        return fine.min(cap);
    }
}

enum FineStatus { UNPAID, PAID, WAIVED }

final class Fine {
    final Loan loan;
    final BigDecimal amount;
    FineStatus status = FineStatus.UNPAID;

    Fine(Loan loan, BigDecimal amount) {
        this.loan = loan;
        this.amount = amount;
    }
}

ChronoUnit.DAYS.between counts whole days from the due date to the return date. Java's documentation says the result is negative when the end comes before the start, so an early return gives a negative number and no fine.

The grace check means a Reader member who is one day late pays nothing. Two days late, they pay for both days, Rs 8. The call fine.min(cap) returns the smaller of the two amounts, which is how the cap works.

The first is 6 days late at Rs 5, so Rs 30. The second is 40 times Rs 5, which is Rs 200, and the cap brings it down to Rs 150.

What just happened? The rate grows the fine and the cap stops it. Both numbers came from the plan, so the loan code never knew which plan it was serving.

BigDecimal also makes you choose how to round. Dividing Rs 100 by 3 with no rounding rule throws an ArithmeticException; with a scale of 2 and HALF_UP you get 33.33.

The fine is saved as a separate Fine object, linked to the loan, with an amount and a status: UNPAID, PAID or WAIVED. A librarian can waive a fine, and a real system records who waived it and why.

Lost books need a second rule, which the demo code leaves out. In this setup, a copy 60 days overdue is marked LOST and fined at its replacement price plus a Rs 50 handling charge (made up). The late-day fine stops counting at that point.

A reservation, called a hold, puts a member in a first-come queue for a book with no free copy. A renewal pushes a loan's due date forward, but only when nobody is waiting for that book.

Each Hold stores the member, the book and a status: WAITING, READY, COLLECTED or EXPIRED. The queue keeps holds in the order they were placed.

enum HoldStatus { WAITING, READY, COLLECTED, EXPIRED }

final class Hold {
    final Member member;
    final Book book;
    HoldStatus status = HoldStatus.WAITING;
    LocalDate collectBy;                   // last day to collect, set when READY

    Hold(Member member, Book book) {
        this.member = member;
        this.book = book;
    }
}

interface HoldListener {
    void holdReady(Hold hold, BookCopy copy);
}

final class HoldQueue {
    private final List<Hold> holds = new ArrayList<>();   // oldest first
    private final List<HoldListener> listeners = new ArrayList<>();

    void addListener(HoldListener l) { listeners.add(l); }

    Hold place(Member member, Book book) {
        Hold hold = new Hold(member, book);
        holds.add(hold);
        return hold;
    }

    boolean hasWaiting(Book book) {
        for (Hold h : holds)
            if (h.book.equals(book) && h.status == HoldStatus.WAITING) return true;
        return false;
    }

    void sendToNextOrShelf(BookCopy copy, LocalDate today) {
        for (Hold h : holds) {
            if (h.book.equals(copy.book) && h.status == HoldStatus.WAITING) {
                h.status = HoldStatus.READY;
                h.collectBy = today.plusDays(3);
                copy.moveTo(CopyStatus.ON_HOLD_SHELF, h);
                for (HoldListener l : listeners) l.holdReady(h, copy);
                return;
            }
        }
        copy.moveTo(CopyStatus.AVAILABLE, null);
    }

    void expire(BookCopy copy, LocalDate today) {
        Hold hold = copy.heldFor();
        if (hold == null || !today.isAfter(hold.collectBy)) return;
        hold.status = HoldStatus.EXPIRED;
        sendToNextOrShelf(copy, today);
    }
}

When a copy comes back, sendToNextOrShelf looks for the oldest WAITING hold on that book. If it finds one, the hold becomes READY, the copy moves to the hold shelf and every listener hears about it. Otherwise the copy goes back to the open shelf.

HoldQueue has no lock of its own. Only LoanService calls it, and always from inside a synchronized method, so one lock covers both the loan and the queue. placeHold only queues; a real system would first check that no copy is free. A repaired copy should also pass through the waiting check, which the demo leaves out.

The state diagram shows the life of one copy. Watch where a return goes: to the hold shelf for the next member, or straight to the open shelf.

A READY hold carries a collect-by date 3 days after the return. Once that date has passed, expireHold marks the hold EXPIRED. The next waiting member gets the copy, or it goes back to the open shelf if nobody is left.

This fixes the thriller promised to two members. The copy has one heldFor field, so it can wait for only one hold at a time, and issue gives it only to that member.

Renewal follows from the same data. LoanService checks that the loan is not overdue, the membership is active, renewals are left and nobody is WAITING for the book. The new due date counts from the old due date.

The overdue check protects the fine. Without it, a Basic loan due on 15 March could be renewed on 20 March, moving the due date to 29 March. A return on 21 March would then pay Rs 0 instead of Rs 30.

Strategy, a state machine, Observer and Repository fit this design. Use each one only where it removes a real if-else chain or a real tangle between classes, not to fill the diagram.

Strategy means swapping one rule object for another behind the same method. If a new plan only changes numbers, it is new data, not a new class. A rule with a different shape, like Rs 10 per started week, is one more FinePolicy class.

CopyStatus is a state machine: a fixed set of statuses and the allowed moves between them. The full State pattern would give each status its own class. With five statuses and no behaviour beyond the allowed moves, the enum is enough.

Observer means other parts subscribe to an event instead of being called directly. When a hold becomes READY, the queue tells its listeners, and the SMS sender is just one of them. In the demo, listeners run inside returnCopy, under the lock. In the real service, record the message and send it after the return commits, so a slow SMS cannot hold the lock and a rolled-back return sends nothing.

Repository means one object that saves and loads a class, such as loans, from the database. The demo keeps everything in memory, so it has no repository; the real service would.

Paste the six Java blocks of this guide, in order, into one file called LibraryDemo.java. The last block below holds the main method, which plays out the whole story on the Basic plan.

public class LibraryDemo {
    public static void main(String[] args) {
        MembershipPlan basic = new MembershipPlan("Basic", 2, 14, 1,
                new DailyCappedFine(new BigDecimal("5"), new BigDecimal("150"), 0),
                new BigDecimal("100"));
        Book thriller = new Book("demo-isbn-1", "A popular thriller");
        BookCopy c1 = new BookCopy("C1", thriller);
        BookCopy c2 = new BookCopy("C2", thriller);
        BookCopy c3 = new BookCopy("C3", thriller);
        LocalDate expiry = LocalDate.of(2027, 3, 31);
        Member m1 = new Member("M1", basic, expiry);
        Member m2 = new Member("M2", basic, expiry);
        Member m3 = new Member("M3", basic, expiry);

        HoldQueue queue = new HoldQueue();
        queue.addListener((hold, copy) -> System.out.println(
                "SMS to " + hold.member.id + ": " + copy.barcode + " is ready until " + hold.collectBy));
        LoanService desk = new LoanService(queue);

        LocalDate mar1 = LocalDate.of(2026, 3, 1);
        Loan first = desk.issue(m1, c1, mar1);
        desk.issue(m1, c2, mar1);
        System.out.println("C1 due on " + first.dueOn);
        attempt(() -> desk.issue(m1, c3, mar1));            // a third book on a 2-book plan
        desk.issue(m3, c3, mar1);

        desk.placeHold(m2, thriller);                       // all three copies are out
        attempt(() -> desk.renew(first, LocalDate.of(2026, 3, 10)));

        Fine fine = desk.returnCopy(first, LocalDate.of(2026, 3, 21));
        System.out.println("Fine for 6 days late: Rs " + fine.amount);
        System.out.println("C1 is now " + c1.status());
        attempt(() -> desk.issue(m3, c1, LocalDate.of(2026, 3, 22)));
        desk.issue(m2, c1, LocalDate.of(2026, 3, 22));
        System.out.println("C1 is now " + c1.status() + " with M2");

        FinePolicy policy = basic.finePolicy();
        LocalDate due = LocalDate.of(2026, 3, 10);
        System.out.println("Returned 2 days early: Rs " + policy.fineFor(due, due.minusDays(2)));
        System.out.println("Returned 40 days late: Rs " + policy.fineFor(due, due.plusDays(40)));
    }

    static void attempt(Runnable action) {
        try {
            action.run();
        } catch (LoanRefused e) {
            System.out.println("Refused: " + e.getMessage());
        }
    }
}

Compile with javac, then run. Up to Java 21, the shortcut java LibraryDemo.java runs only the first class in the file, MembershipPlan, and fails, as we saw on Java 17 and 21. From Java 22, the shortcut also accepts a class named after the file, says JEP 458, the official proposal for that change. Using javac first works on every version.

javac LibraryDemo.java
java LibraryDemo

You should see exactly these ten lines. Dates print as year, month, day, and amounts are in rupees.

C1 due on 2026-03-15
Refused: Loan limit reached
Refused: Another member is waiting for this book
SMS to M2: C1 is ready until 2026-03-24
Fine for 6 days late: Rs 30
C1 is now ON_HOLD_SHELF
Refused: Copy is not free for this member
C1 is now ON_LOAN with M2
Returned 2 days early: Rs 0
Returned 40 days late: Rs 150

What just happened? The third book was refused by the limit check, and the renewal was refused because M2 was waiting. The return was 6 days late, so the fine was Rs 30, and C1 went to the hold shelf for M2. M3 could not take it, and M2 could.

You stop it with row locks while the issue runs, plus a rule the database itself enforces. The synchronized keyword in the demo is not enough once the service runs on more than one server.

Here is why. synchronized only makes threads wait inside one running Java program. Two app servers sharing one database never see each other's locks, so the race moves into the database.

It holds two open loans for one copy; our naive two-desk test ended exactly so. The same thing happens with the book limit: two desks each see 1 open loan on a 2-book plan, and both issue one more.

Two guards fix this. The steps below assume PostgreSQL's default isolation level, Read Committed. An isolation level is the rule for what one transaction, a group of changes that all succeed or all fail, sees of other transactions. At Read Committed, each plain SELECT sees data committed before it started, while a FOR UPDATE that had to wait reads the newest committed version of the row.

1. 1

   Lock the member, then the copy

   Run the issue inside one transaction. Read the member row and the copy row with SELECT ... FOR UPDATE.

2. 2

   Let the second desk wait

   Other transactions cannot lock, change or delete those rows until this transaction ends. Plain reads are not blocked. A second desk's SELECT ... FOR UPDATE waits, then sees the new status.

3. 3

   Count and check after the locks

   Count the member's open loans only after both locks are held. Then the second desk counts the first desk's loan too.

4. 4

   Add a partial unique index

   A unique index on the loan table's copy_id, limited to rows where returned_on is empty, allows only one open loan per copy, even if the code has a bug.

5. 5

   Turn the error into a clear message

   If the index rejects a loan, the service catches the error and tells the librarian the copy is already on loan.

Here is the schema with the index, followed by one issue transaction. A partial index is an index that covers only the rows matching its WHERE condition. Closed loans for the same copy can repeat as often as needed.

The schema is cut down to what the race needs. The book limit sits on the member row here; the real schema would read it from a plan table.

CREATE TABLE member (
    id        bigint PRIMARY KEY,
    max_books int    NOT NULL
);

CREATE TABLE book_copy (
    id      bigint PRIMARY KEY,
    book_id bigint NOT NULL,
    status  text   NOT NULL DEFAULT 'AVAILABLE'
);

CREATE TABLE loan (
    id          bigserial PRIMARY KEY,
    member_id   bigint NOT NULL REFERENCES member (id),
    copy_id     bigint NOT NULL REFERENCES book_copy (id),
    due_on      date   NOT NULL,
    returned_on date                   -- empty while the loan is open
);

-- At most one open loan per copy; closed loans for the same copy are fine
CREATE UNIQUE INDEX one_open_loan_per_copy
    ON loan (copy_id) WHERE returned_on IS NULL;

INSERT INTO member VALUES (1, 2);
INSERT INTO book_copy (id, book_id) VALUES (10, 7), (11, 7), (12, 7);

BEGIN;
SELECT max_books FROM member WHERE id = 1 FOR UPDATE;    -- lock the member first
SELECT status FROM book_copy WHERE id = 10 FOR UPDATE;   -- then the copy
SELECT count(*) FROM loan WHERE member_id = 1 AND returned_on IS NULL;
-- The service checks: status is AVAILABLE and the count is below max_books
UPDATE book_copy SET status = 'ON_LOAN' WHERE id = 10;
INSERT INTO loan (member_id, copy_id, due_on) VALUES (1, 10, DATE '2026-03-15');
COMMIT;

-- A buggy second insert for the same copy is refused by the index
INSERT INTO loan (member_id, copy_id, due_on) VALUES (1, 10, DATE '2026-03-15');

We ran both blocks on PostgreSQL 16.15. The transaction commits, and the buggy second insert fails with error 23505, duplicate key value violates unique constraint one_open_loan_per_copy. Closing the loan and lending copy 10 again then works.

What our two-desk tests showed

We ran psql, PostgreSQL's command-line client, in two sessions side by side to play two desks, with a pause before each commit. One row uses Repeatable Read, a stricter level where the whole transaction reads one frozen view taken at its first statement. Another shows a deadlock: two transactions each waiting for a lock the other holds. These are the results from our runs, as of 19 October 2026.

The Repeatable Read row matters if your service runs at that level. There the second desk gets an error instead of the new status, and PostgreSQL's documentation says to retry the whole transaction.

The deadlock row is why the steps lock the member first, then the copy, every time. PostgreSQL's documentation calls a consistent lock order generally the best defence against deadlocks.

Without a lock, both returns can hand their copy to the same waiting member, while the next member keeps waiting. Lock the hold row while you pick it; adding SKIP LOCKED lets the second return take the next member without waiting.

Here is the hold table and the claim that each return runs. The second index makes sure one copy can be READY for only one hold.

CREATE TABLE hold (
    id        bigserial PRIMARY KEY,
    member_id bigint NOT NULL REFERENCES member (id),
    book_id   bigint NOT NULL,
    placed_at timestamptz NOT NULL DEFAULT now(),
    status    text   NOT NULL DEFAULT 'WAITING',
    copy_id   bigint REFERENCES book_copy (id)
);

-- At most one READY hold per copy
CREATE UNIQUE INDEX one_ready_hold_per_copy
    ON hold (copy_id) WHERE status = 'READY';

-- Run inside the return transaction for copy 11 of book 7
UPDATE hold SET status = 'READY', copy_id = 11
WHERE id = (
    SELECT id FROM hold
    WHERE book_id = 7 AND status = 'WAITING'
    ORDER BY placed_at, id             -- oldest hold first
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
RETURNING id, member_id;

Now compare the naive return: the same UPDATE without the FOR UPDATE SKIP LOCKED line. Two returns in the same second both pick hold 1. In our test, the second update simply overwrote the first: hold 1 ended up READY with copy 12, and hold 2 stayed WAITING.

The reason is timing. The subquery, the SELECT inside the brackets, picked hold 1 before the second return waited for the first. After the wait, PostgreSQL re-checked only the outer WHERE, id equals 1, and that still matched.

FOR UPDATE locks the hold row the subquery picks. SKIP LOCKED tells PostgreSQL to skip rows another transaction has already locked, instead of waiting for them. PostgreSQL's documentation suggests it for queue-like tables with many consumers.

In our runs, plain FOR UPDATE without SKIP LOCKED was also correct: the second return waited until the first committed, then took hold 2. SKIP LOCKED gave the same result, and the second return finished at once without waiting.

SKIP LOCKED has a cost, and the documentation states it. Skipping locked rows gives an inconsistent view of the data, so use it only for picking work from a queue, never for counts or reports.

Explain it as scope, rules, classes, the two main flows, the fine rule and then the race between desks. That order shows the interviewer you think from rules to code.

1. 1

   Agree the scope

   List what the system does: plans, issue, return, renew, holds and fines. Say what you are leaving out, like buying books.

2. 2

   Ask for the rules

   Ask about book limits, loan length, fine rate, fine cap and grace days. Write them down as plan fields.

3. 3

   Draw the classes

   Member, MembershipPlan, Book, BookCopy, Loan, Hold and Fine. Stress the Book and BookCopy split early.

4. 4

   Walk through issue and return

   Show the four checks for issue, then the fine and hold steps for return. Keep all changes inside LoanService.

5. 5

   Write one small method

   Write the fine rule or the issue checks in code. Pass dates in and use BigDecimal for money.

6. 6

   Handle the race

   Explain row locks in a fixed order and one open loan per copy. Then mention SKIP LOCKED for holds if time allows.