Session09 Additional Info

⚠ Important Tip

If your agent.ts uses useAuthStore().token at the top level, remember:

• On very first load, Zustand will rehydrate from storage after initial render, so token may be null until rehydration is done.
• Fix: either delay access until after hasHydrated, or refactor agent to inject token per request.

To safely handle concurrency in your CreateTicketOrderAsync method — particularly for seat reservation on the same Transportation — you need to prevent race conditions where two users might reserve the same seat or oversell capacity.

This is a classic critical section problem, and you can solve it using application-level locking, database-level locking, or both.

---

✅ Recommended: Database-level concurrency control + optional distributed lock

👇 Here’s what you should do:

---

✅ 1. Use pessimistic concurrency (row-level locking) on the Transportation row

Why?

You need to lock the transportation row to prevent multiple reservations from modifying the related seats/capacity at the same time.

How?

Use EF Core’s raw SQL or transaction-scoped SELECT … FOR UPDATE (which translates to SQL row locks):

using var transaction = await _context.Database.BeginTransactionAsync(IsolationLevel.Serializable);
 
var transportation = await _context.Transportations
    .FromSqlRaw("SELECT * FROM Transportations WITH (UPDLOCK, ROWLOCK) WHERE Id = {0}", dto.TransportationId)
    .FirstOrDefaultAsync();

• UPDLOCK: prevents others from reading for update

• ROWLOCK: locks only the specific row

If using PostgreSQL or MySQL, you’d use FOR UPDATE instead.

---

✅ 2. Alternatively, use distributed locking if you have multiple app instances

Use something like:

• Redis distributed lock

• SQL application locks (sp_getapplock)

• SemaphoreSlim static lock (only for single-instance apps — not safe in web farms)

Example using sp_getapplock (SQL Server):

EXEC sp_getapplock @Resource = 'lock_transportation_123', @LockMode = 'Exclusive', @LockTimeout = 10000;

EF version:

await _dbContext.Database.ExecuteSqlRawAsync(
    $"EXEC sp_getapplock @Resource = 'transportation_lock_{dto.TransportationId}', @LockMode = 'Exclusive', @LockTimeout = 10000;");

✅ This will ensure only one thread gets to proceed per TransportationId.

---

✅ 3. Wrap the reservation logic in a transaction

using var transaction = await _context.Database.BeginTransactionAsync(IsolationLevel.Serializable);
 
// locking query here
// seat validation
// insert tickets
// save ticket order
// commit
 
await _unitOfWork.SaveChangesAsync();
await transaction.CommitAsync();

Use IsolationLevel.Serializable or RepeatableRead if you’re not doing explicit locking.

---

✅ 4. Add a unique constraint on SeatId + TransportationId (if not already)

To enforce uniqueness at DB level and prevent race conditions from succeeding:

ALTER TABLE Tickets ADD CONSTRAINT UQ_Tickets_Seat_Transport UNIQUE (SeatId, TicketStatusId)
WHERE TicketStatusId != 3; -- exclude cancelled

Or make a logic-safe alternative depending on your schema.

---

✅ Final version: Summary of changes

In your service:

using var transaction = await _dbContext.Database.BeginTransactionAsync(IsolationLevel.Serializable);
 
// Optional: Lock the transportation row to prevent race
await _dbContext.Database.ExecuteSqlRawAsync(
    $"EXEC sp_getapplock @Resource = 'transportation_lock_{dto.TransportationId}', @LockMode = 'Exclusive', @LockTimeout = 10000;");
 
// Re-fetch transportation + tickets
var transportation = await _transportationRepository.GetByIdWithTickets(dto.TransportationId);
 
// Validate seats
var seatCheck = ValidateTransportationAndSeats(transportation, dto.Travelers);
if (!string.IsNullOrEmpty(seatCheck)) return Result<long>.Error(0, seatCheck);
 
// Proceed with reservation
...
 
await _unitOfWork.SaveChangesAsync();
await transaction.CommitAsync();

---

🧠 Summary Table

Strategy	Prevents Race?	Web-farm Safe	Recommended For
sp_getapplock	✅ Yes	✅ Yes	SQL Server web apps
EF raw SQL WITH (UPDLOCK)	✅ Yes	✅ Yes	SQL Server monoliths
SemaphoreSlim	✅ Yes	❌ No	Local-only apps
FOR UPDATE	✅ Yes	✅ Yes	PostgreSQL, MySQL

---

Let me know your DB provider and environment (single-instance or multi-instance), and I can generate exact code tailored for that. Perfect — you’re absolutely right to want the whole process to be atomic:
→ Seat allocation,
→ Ticket creation,
→ Balance checking & payment,
→ Transaction creation,
→ Traveler creation/upsert, etc.

This needs more than just database-level locks. You want to serialize execution for the same transportation to avoid concurrency conflicts — across the entire flow, not just the DB writes.

---

✅ Your Goal

For a given TransportationId, only one call to CreateTicketOrderAsync should execute at a time, regardless of app instance or user.

This includes logic outside EF (like payment, seat assignment, person creation, etc.).

---

🛠️ The Right Solution: Distributed Application Locking

Use SQL Server’s sp_getapplock to lock by TransportationId

Why?

• It locks application-level logic, not just SQL rows

• It works across threads, processes, and even app servers

• It ensures only one process can work on that transportation at a time

---

✅ How to Implement in Your Method

🔒 Step 1: Lock by Transportation ID

Add this at the start of your method:

await _dbContext.Database.ExecuteSqlRawAsync(
    $"EXEC sp_getapplock @Resource = 'transportation_lock_{dto.TransportationId}', " +
    "@LockMode = 'Exclusive', @LockTimeout = 10000;");

This ensures that only one call per transportation runs at a time.

If a second request tries to lock the same ID, it waits up to 10 seconds.

---

🔁 Step 2: Begin EF Transaction

This will include all EF operations in a single atomic unit:

using var transaction = await _dbContext.Database.BeginTransactionAsync();

---

🧠 Step 3: Keep the Full Logic As Is, But Within Transaction

Like this:

public async Task<Result<long>> CreateTicketOrderAsync(long accountId, CreateTicketOrderDto dto)
{
    // 🔒 Lock to prevent concurrency per transportation
    await _dbContext.Database.ExecuteSqlRawAsync(
        $"EXEC sp_getapplock @Resource = 'transportation_lock_{dto.TransportationId}', " +
        "@LockMode = 'Exclusive', @LockTimeout = 10000;");
 
    // 🧾 Begin transaction
    using var transaction = await _dbContext.Database.BeginTransactionAsync();
 
    // 💰 Load account and transportation
    var account = await _accountRepository.GetByIdAsync(accountId);
    if (account == null) return Result<long>.Error(0, "Account not found");
 
    var transportation = await _transportationRepository.GetByIdAsync(dto.TransportationId);
    if (transportation == null) return Result<long>.Error(0, "Transportation not found");
 
    // 💵 Check balance
    var price = transportation.BasePrice * dto.Travelers.Count;
    if (account.CurrentBalance < price)
        return Result<long>.Error(0, "Not enough money");
 
    // ✅ Validate seat availability
    var seatCheck = ValidateTransportationAndSeats(transportation, dto.Travelers);
    if (!string.IsNullOrEmpty(seatCheck))
        return Result<long>.Error(0, seatCheck);
 
    // 🪑 Assign seats (if dynamic logic)
    await AssignSeatsIfDynamic(transportation.VehicleId, dto.Travelers);
 
    // 👤 Upsert travelers (can also use same transaction)
    await UpsertTravelers(account.Id, dto.Travelers);
 
    // 🎟️ Create ticket order
    var ticketOrder = new TicketOrder
    {
        BuyerId = account.Id,
        CreatedAt = DateTime.UtcNow,
        Description = "",
        SerialNumber = Guid.NewGuid().ToString("N"),
        TransportationId = dto.TransportationId,
    };
    await _ticketOrderRepository.AddAsync(ticketOrder);
 
    // 🎫 Add tickets
    foreach (var traveler in dto.Travelers)
    {
        var ticket = new Ticket
        {
            CreatedAt = DateTime.UtcNow,
            Description = traveler.Description,
            SeatId = traveler.SeatId!.Value,
            SerialNumber = Guid.NewGuid().ToString("N"),
            TicketOrder = ticketOrder,
            TicketStatusId = 1,
            TravelerId = traveler.Id,
        };
        await _ticketRepository.AddAsync(ticket);
    }
 
    // 💳 Commit everything
    await _unitOfWork.SaveChangesAsync();
 
    // 💸 Withdraw money and create transaction
    await _accountService.PayForTicketOrderAsync(account.Id, ticketOrder.Id, price);
 
    // ✅ Commit transaction
    await transaction.CommitAsync();
 
    return Result<long>.Success(ticketOrder.Id);
}

---

🧱 Summary of Protection

Step	Protected?	How
Prevent concurrent seat assignment	✅	sp_getapplock per TransportationId
Prevent concurrent DB changes	✅	EF DbTransaction
Prevent double-spend	✅	Balance check inside the same lock + transaction
Ensure traveler upserts/tickets are safe	✅	Wrapped in same transaction

---

⚠️ Important Note

Make sure all the operations (AssignSeatsIfDynamic, UpsertTravelers, etc.):

• Use the same DbContext/UnitOfWork

• Don’t commit changes prematurely (no SaveChangesAsync() before you’re ready to commit the whole transaction)

---

✅ Optional (Safety): Add retry logic

In case of a deadlock or lock timeout, you might want to retry the whole operation once.

---

Let me know if you want me to extract this into a reusable helper like:

await _appLockService.ExecuteWithLockAsync($"transportation_lock_{dto.TransportationId}", async () => {
    // your reservation logic
});

That would clean things up nicely. Sure. Let’s break down everything you need to know about transactions in Entity Framework Core (EF Core) — especially for your case where you’re doing complex operations that must all succeed or fail together.

---

🧾 What is a Transaction?

A transaction is a unit of work that is treated as a single, indivisible operation. Either all of it succeeds (committed), or none of it does (rolled back).

🧠 Properties of a transaction (ACID):

Property	Meaning
Atomicity	All steps succeed or none do
Consistency	DB remains valid before and after
Isolation	Transactions don’t interfere with each other
Durability	Once committed, data is persisted permanently

---

✅ Why You Need Transactions

In your case, you’re:

• Assigning seats

• Creating or updating people

• Creating a TicketOrder

• Creating multiple Tickets

• Updating account balance & transaction history

All of these must either:

• Complete fully and safely, or

• Fail entirely, with no partial data left behind.

---

🧰 Transaction in EF Core: Full Example

🔧 Step-by-step Example:

public async Task<Result<long>> CreateTicketOrderAsync(long accountId, CreateTicketOrderDto dto)
{
    // Begin EF Core transaction
    using var transaction = await _dbContext.Database.BeginTransactionAsync();
 
    try
    {
        // Load required entities
        var account = await _accountRepository.GetByIdAsync(accountId);
        if (account == null)
            return Result<long>.Error(0, "Account not found");
 
        var transportation = await _transportationRepository.GetByIdAsync(dto.TransportationId);
        if (transportation == null)
            return Result<long>.Error(0, "Transportation not found");
 
        var price = transportation.BasePrice * dto.Travelers.Count;
        if (account.CurrentBalance < price)
            return Result<long>.Error(0, "Not enough money");
 
        // Validate seat assignment
        var seatCheck = ValidateTransportationAndSeats(transportation, dto.Travelers);
        if (!string.IsNullOrEmpty(seatCheck))
            return Result<long>.Error(0, seatCheck);
 
        await AssignSeatsIfDynamic(transportation.VehicleId, dto.Travelers);
        await UpsertTravelers(account.Id, dto.Travelers);
 
        // Create ticket order
        var ticketOrder = new TicketOrder
        {
            BuyerId = account.Id,
            CreatedAt = DateTime.UtcNow,
            SerialNumber = Guid.NewGuid().ToString("N"),
            TransportationId = dto.TransportationId,
        };
        await _ticketOrderRepository.AddAsync(ticketOrder);
 
        foreach (var traveler in dto.Travelers)
        {
            await _ticketRepository.AddAsync(new Ticket
            {
                CreatedAt = DateTime.UtcNow,
                Description = traveler.Description,
                SeatId = traveler.SeatId.Value,
                SerialNumber = Guid.NewGuid().ToString("N"),
                TicketOrder = ticketOrder,
                TicketStatusId = 1,
                TravelerId = traveler.Id,
            });
        }
 
        // Save changes
        await _unitOfWork.SaveChangesAsync();
 
        // Withdraw money from account
        await _accountService.PayForTicketOrderAsync(account.Id, ticketOrder.Id, price);
 
        // Commit transaction
        await transaction.CommitAsync();
 
        return Result<long>.Success(ticketOrder.Id);
    }
    catch (Exception ex)
    {
        // Rollback if anything failed
        await transaction.RollbackAsync();
        return Result<long>.Error(0, "Reservation failed: " + ex.Message);
    }
}

---

🔄 Isolation Levels (Advanced)

You can specify isolation level when beginning a transaction:

using var transaction = await _dbContext.Database.BeginTransactionAsync(IsolationLevel.Serializable);

Common levels:

Isolation Level	Description	Use Case
ReadCommitted (default)	No dirty reads	Usually enough
RepeatableRead	Prevents non-repeatable reads	Seat assignment logic
Serializable	Full isolation (slower, safer)	Financial or reservation systems

---

🧩 When SaveChangesAsync() Commits vs. Not

Calling SaveChangesAsync() does not commit the outer transaction — it just writes pending changes to the database.

The real commit happens only when you call:

await transaction.CommitAsync();

If anything fails before that, the changes are not persisted.

---

✅ Best Practices for Transactions in EF Core

Best Practice	Why
Keep transactions short	Locks and contention increase with time
Catch exceptions and rollback	Prevent dirty/incomplete data
Use only one DbContext inside the transaction	EF tracks entities via one unit of work
Don’t call SaveChanges too early	May break the atomicity
Use appropriate isolation level	Based on your concurrency needs
Consider retry policy for transient errors	Especially in distributed systems

---

❗ Common Pitfalls

Mistake	Problem
Mixing DbContext instances inside one transaction	Breaks consistency
Committing too early	Later failure still leaves partial data
Forgetting rollback in catch	Leaves transaction open or inconsistent
Async code outside transaction scope	Could break atomicity or introduce race

---

🧠 Summary: Transaction Essentials

Concept	Description
BeginTransactionAsync()	Starts a manual transaction
SaveChangesAsync()	Writes changes but doesn’t commit outer transaction
CommitAsync()	Finalizes all DB changes
RollbackAsync()	Reverts all changes since the transaction began
Scope	All EF/DB actions done within the transaction block

---

If you’d like, I can create a generic transaction helper, or even make this a middleware or interceptor, depending on how reusable you want this to be.

Let me know! Great! These questions show deep thinking about how transactions and locks actually work in EF Core and layered architecture. Let’s answer them one by one with real clarity and direction.

---

❓ 1. “I have this method in my TicketOrderService, and I don’t have access to _dbContext.”

✅ Solution:

If you’re using Repository + UnitOfWork pattern, your UnitOfWork should expose a method to access the underlying DbContext or allow you to start a transaction.

👉 Extend your IUnitOfWork interface like this:

public interface IUnitOfWork
{
    Task<int> SaveChangesAsync();
    Task<IDbContextTransaction> BeginTransactionAsync(IsolationLevel level = IsolationLevel.ReadCommitted);
    DbContext Context { get; } // optional
}

And in your implementation:

public class UnitOfWork : IUnitOfWork
{
    private readonly AppDbContext _context;
 
    public UnitOfWork(AppDbContext context) => _context = context;
 
    public Task<int> SaveChangesAsync() => _context.SaveChangesAsync();
 
    public Task<IDbContextTransaction> BeginTransactionAsync(IsolationLevel level = IsolationLevel.ReadCommitted)
        => _context.Database.BeginTransactionAsync(level);
 
    public DbContext Context => _context;
}

✅ Then in your TicketOrderService:

using var transaction = await _unitOfWork.BeginTransactionAsync();
// your operations
await _unitOfWork.SaveChangesAsync();
await transaction.CommitAsync();

This keeps your Service clean and architecture-respecting.

---

❓ 2. “How does the transaction hold stuff? Wouldn’t unitOfWork.SaveChangesAsync() just save everything?”

🔍 Clarification:

• SaveChangesAsync() writes the changes to the database, but…

• If you’re in a transaction, the database holds those changes in a temporary, invisible state until you explicitly call:

await transaction.CommitAsync();

💡 If you don’t commit, or you rollback, then those changes are discarded, even if you called SaveChangesAsync().

👇 Timeline of how EF works in a transaction:

1. EF tracks entities in memory.

2. You call SaveChangesAsync().

3. Changes are written to the DB — but:

   • If inside a transaction, they are not visible to others yet.

   • They are held until CommitAsync().

4. If you call RollbackAsync(), all writes are undone.

---

❓ 3. “How would the lock know that the work with that transportation is finished?”

🔒 About sp_getapplock:

This SQL Server feature locks a named resource (like transportation_lock_123) for the duration of the current SQL connection and transaction.

Here’s how it works:

Action	Result
You call sp_getapplock	SQL Server grants an exclusive lock for the resource
You complete the transaction (commit or rollback)	Lock is automatically released
Your connection is closed or disposed	Lock is released

🔍 So you don’t need to manually release the lock.

Just make sure:

• The lock and transaction use the same connection

• You commit or rollback after the work is done

✅ EF Core handles all that automatically when using Database.BeginTransactionAsync().

---

✅ Final Advice Recap

Question	Answer
I don’t have access to _dbContext	Expose BeginTransactionAsync on your IUnitOfWork
How does transaction matter if I save mid-way?	It isolates changes — no one sees them until committed
How does lock get released?	Automatically when transaction completes or connection closes

---

Let me know if you want:

• Sample UnitOfWork and Repository setup for full support

• A lock + transaction helper method

• Unit tests to simulate concurrent reservations

You’re doing great — this is how robust, real-world software gets built.

You’ve unpacked crucial concerns around transactions, unit-of-work (UoW), and application-wide consistency. Here’s a polished breakdown with clear answers and guidance:

---

✅ 1. Is it okay to use both Transactions and Unit of Work?

• Yes, absolutely. UoW is a design pattern that groups multiple repository operations into one logical unit, while a transaction ensures these operations either all succeed or all fail atomically (antondevtips.com).

• In EF Core, the DbContext itself implements UoW, collecting changes in memory until SaveChangesAsync() is called (reddit.com).

• When you execute multiple SaveChanges, or include non-repository logic (e.g., balance updates, payment creation, seat locking), explicit transactions ensure the entire flow remains atomic .

---

✅ 2. How exactly to open/close transactions? Where to be careful?

✨ Use EF to manage transactions in your UoW:

1. Extend your IUnitOfWork to include transaction methods:

   Task<IDbContextTransaction> BeginTransactionAsync(IsolationLevel level = ReadCommitted);
   Task<int> SaveChangesAsync();

2. In your service, do:

   using var tx = await _unitOfWork.BeginTransactionAsync();
   try {
     // perform all operations (seat locks, entity changes, balance, tickets)
     await _unitOfWork.SaveChangesAsync();
     await tx.CommitAsync();
   } catch {
     await tx.RollbackAsync();
     throw;
   }

3. Know the boundaries: Keep the transaction as short as possible. Open it before critical operations and close it after the final CommitAsync().

💡 Use (IsolationLevel.Serializable) for scenarios like seat reservation where concurrent writes must be prevented (milanjovanovic.tech, learn.microsoft.com).

---

✅ 3. Why did things “work” without explicit transactions?

• Simple SaveChanges calls act like atomic units—if they fail, nothing is saved. With just one SaveChanges call, EF wraps it in a default transaction (stackoverflow.com).

• However, as soon as your workflow involves multiple SaveChanges, external services, or cross-repository updates, the default behavior is no longer sufficient. A failure midway could leave your data in a partial state.

• That’s where explicit transaction scopes come in—to guarantee everything is batched and rolled back if any part fails (antondevtips.com).

---

🧠 TL;DR Summary

Concept	Purpose	When Needed
Unit of Work	Organizes multiple EF operations into one commit	Always used with EF DbContext
Implicit transaction	EF wraps each SaveChanges automatically	Ok for single SaveChanges calls
Explicit transaction	Manually control commit/rollback across many operations	Needed when multiple SaveChanges, external services, concurrency, etc.
IsolationLevel.Serializable	Highest locking level to prevent concurrent conflicts	Use for critical resource locks (e.g., seat booking)
Be careful	Keep transactions short to avoid locks and deadlocks	Avoid long-running operations inside the transaction

---

If you want, I can provide:

• Code templates for UoW with transaction management

• Interceptor or decorator to automate transaction handling in your services

• Concurrency demos to test seat reservation under load

Just say the word! Absolutely! Let’s clarify both of your statements with authoritative sources:

---

1. If I haven’t called BeginTransaction and call SaveChanges, will changes be saved?

✅ Yes. EF Core automatically wraps every SaveChanges() call in its own transaction. This means:

• If all operations succeed, the data is saved.
• If anything fails, the entire set is rolled back, leaving the DB unchanged. citeturn0search0turn0search9

🔁 So even without explicitly starting a transaction, SaveChanges() ensures atomicity for that batch of changes.

---

2. If I have called BeginTransaction, and then call SaveChanges, do changes go to the DB immediately?

✅ Yes, but context matters. When you wrap operations in an explicit transaction:

• Each SaveChanges() writes within that transaction.
• However, none of these writes become visible to other connections until you call Commit().

EF also uses savepoints internally (for multiple SaveChanges() calls) to enable partial rollbacks without rolling back the entire transaction. citeturn0search0turn0search2turn0search3

---

🧩 In Plain English

Scenario	SaveChanges Only	BeginTransaction + SaveChanges
No transaction	Auto-wrapped in its own transaction–immediate.	—
With explicit transaction	Writes are within the transaction, not yet committed.	✔
Rollback before commit	Not possible. SaveChanges is final.	✔ Rolls everything back.

---

✅ TL;DR

• ✅ Without BeginTransaction: SaveChanges() is atomic per call—commits or rolls back automatically.
• ✅ With BeginTransaction: SaveChanges() writes into the transaction, but the changes are held until you call Commit(); Rollback() discards them all.

---

Want some ready-to-use code to introduce BeginTransaction() into your UnitOfWork and services? Just let me know and I’ll supply a clean implementation!