Transactions & Advanced — Series 2

Preview — 3 of 10 questions

What does raising the isolation level from the usual READ COMMITTED buy here?

javascript

await this.dataSource.transaction('REPEATABLE READ', async manager => {
  const total = await manager.sum(Order, 'amount', { userId });
  // ... decide, then write
});

AIt locks every table the transaction touches for its whole duration

BIt guarantees the transaction will never fail or need retrying

CRe-reading the same rows inside the transaction yields the same values even if another transaction commits changes meanwhile — removing non-repeatable reads, so a decision made from the first read stays consistent with the data it was based on

DIt makes the transaction visible to other sessions before it commits

What is wrong with the second call?

javascript

await this.dataSource.transaction(async manager => {
  await manager.save(Order, order);
  await this.itemsRepo.save(items);   // injected repository — ✗
});

AThe injected repository is bound to the default entity manager and therefore its own connection, so the item writes run outside the transaction — if the transaction later rolls back, the order disappears while the items remain

BNothing; TypeORM detects the surrounding transaction automatically

CIt deadlocks, because two connections write to related tables

DIt throws, since a repository may not be used inside a transaction callback

Why can these two transactions deadlock, and what is the standard fix?

javascript

// transaction A
await manager.findOne(Account, { where: { id: from }, lock: { mode: 'pessimistic_write' } });
await manager.findOne(Account, { where: { id: to },   lock: { mode: 'pessimistic_write' } });
// transaction B does the same with `from` and `to` swapped

AThey cannot: pessimistic_write locks are non-blocking

BThe deadlock comes from the isolation level; lowering it to READ COMMITTED resolves it

CThe fix is to wrap each lock acquisition in its own transaction

DEach transaction holds one row's lock while waiting for the other's, so neither can proceed — acquiring locks in a deterministic order (for example sorted by id) means one transaction always gets both and the other simply waits

Answer all 10 questions (7 more), see explanations for every answer, and track your score.