MVCC ve Kilitler — Eski Satır Sürümleri Nerede Yaşar?

1
@Service
2
class TransferService {
3

4
    private final FraudClient fraud;
5
    private final TransactionTemplate tx;
6
    private final AccountRepository accounts;
7

8
    TransferService(FraudClient fraud, TransactionTemplate tx, AccountRepository accounts) {
9
        this.fraud = fraud;
10
        this.tx = tx;
11
        this.accounts = accounts;
12
    }
13

14
    TransferResult transfer(String fromIban, String toIban, BigDecimal amount, String currency) {
15
        // The slow HTTP call runs first, while no transaction is open.
16
        if (fraud.check(fromIban, toIban, amount, currency).rejected()) {
17
            return TransferResult.REJECTED;
18
        }
19
        // The transaction starts only now and holds two UPDATEs.
20
        return tx.execute(status -> {
21
            // debit: UPDATE account SET balance = balance - :amount
22
            //         WHERE iban = :iban AND currency = :currency AND balance >= :amount
23
            if (accounts.debit(fromIban, amount, currency) == 0) {
24
                status.setRollbackOnly();
25
                return TransferResult.INSUFFICIENT_FUNDS;
26
            }
27
            accounts.credit(toIban, amount, currency);
28
            return TransferResult.BOOKED;
29
        });
30
    }
31
}

1
-- Oldest open transactions first; 'idle in transaction' is the usual suspect.
2
SELECT pid, application_name, state,
3
       now() - xact_start AS xact_age,
4
       left(query, 60)    AS last_query
5
  FROM pg_stat_activity
6
 WHERE xact_start IS NOT NULL
7
 ORDER BY xact_start
8
 LIMIT 5;
9

10
-- Dead row versions on the ledger table, waiting for VACUUM.
11
SELECT relname, n_dead_tup, last_autovacuum
12
  FROM pg_stat_user_tables
13
 WHERE relname = 'account_movement';
14

15
-- Safety net: an idle open transaction of the transfer service is closed.
16
ALTER ROLE transfer_app SET idle_in_transaction_session_timeout = '30s';

1
-- Readers stop waiting for writers: READ COMMITTED reads row versions instead.
2
ALTER DATABASE CoreBanking SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
3

4
-- The old versions now live in tempdb; this is where their size shows up.
5
SELECT DB_NAME(database_id) AS db, reserved_space_kb
6
  FROM sys.dm_tran_version_store_space_usage;

1
@Service
2
class SlowTransferService {
3

4
    @Transactional  // the transaction opens when the method starts
5
    TransferResult transfer(String fromIban, String toIban, BigDecimal amount, String currency) {
6
        accounts.debit(fromIban, amount, currency);
7

8
        // While the fraud service thinks, this transaction keeps the debited account's
9
        // row lock, keeps its snapshot open (VACUUM waits) and holds a pooled connection.
10
        if (fraud.check(fromIban, toIban, amount, currency).rejected()) {
11
            throw new TransferRejectedException();   // rolls back the debit
12
        }
13
        accounts.credit(toIban, amount, currency);
14
        return TransferResult.BOOKED;
15
    }
16
}