Combined Bank Assistant Programmer Exam 31 October 2026 — Question Solution | সমন্বিত ব্যাংক (ব্যাংকার্স সিলেকশন কমিটি) সহকারী প্রোগ্রামার নিয়োগ পরীক্ষা ২০২৬ — প্রশ্ন সমাধান

Transaction: a logical unit of work made of one or more database operations, e.g. a fund transfer = debit one account + credit another. ACID is the set of four properties that a DBMS guarantees for every transaction, so that data stay correct despite failures, crashes and many concurrent users.

Property	Meaning	How the DBMS provides it
Atomicity	All operations of the transaction take effect, or none do ("all or nothing").	Undo (rollback) log: on an error or ROLLBACK every change is undone.
Consistency	A transaction takes the database from one valid state to another; no integrity rule (PK, FK, CHECK, business rule) is left broken.	Constraints and triggers checked by the DBMS, plus correct transaction logic.
Isolation	Concurrent transactions do not see each other's unfinished work; the result is as if they ran one after another (fully so at SERIALIZABLE).	Concurrency control: locking (two-phase locking) or multiversioning (MVCC).
Durability	Once COMMIT succeeds, the change survives a crash or power failure.	Write-ahead (redo) log forced to disk at commit; recovery replays it.

Worked example: transfer Tk 7,000 from SB-101 to SB-202

CREATE TABLE account (
  acc_no   CHAR(6)       NOT NULL PRIMARY KEY,
  holder   VARCHAR(40)   NOT NULL,
  balance  DECIMAL(12,2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO account VALUES ('SB-101', 'Nadia', 5000), ('SB-202', 'Tanvir', 2000);

BEGIN TRANSACTION;
UPDATE account SET balance = balance + 7000 WHERE acc_no = 'SB-202';  -- succeeds
UPDATE account SET balance = balance - 7000 WHERE acc_no = 'SB-101';  -- fails: CHECK (balance >= 0)
ROLLBACK;                                                            -- program reacts to the error

Moment	SB-101	SB-202	Total
Before	5000	2000	7000
Inside, after the credit	5000	9000	14000 (not visible to other sessions)
After ROLLBACK	5000	2000	7000
After a valid transfer of 1500 and COMMIT	3500	3500	7000

Each property with its example

1. Atomicity: the credit of 7000 to SB-202 succeeded but the debit failed, so the whole transaction is rolled back and the credit is undone; money is not created. Similarly, if the server crashes after an ATM debit but before the credit, the half-done transfer is undone on restart.
2. Consistency: the rule "balance ≥ 0" (CHECK) blocks the overdraft, and the bank's invariant "total money = 7000" holds before and after every transaction.
3. Isolation: while the transfer runs, another session (e.g. a balance enquiry) must not read the temporary 9000; reading uncommitted data is a dirty read. Likewise, two tellers withdrawing from the same account at once must not both read 5000 and overwrite each other's update (lost update).
4. Durability: after the 1500 transfer is committed and the customer gets the SMS, the balances 3500/3500 remain even if the power fails a second later, because the commit record is already in the log on disk.

Note: in SQLite, MySQL and SQL Server a failed statement undoes only itself, so the program must issue ROLLBACK to undo the earlier credit; PostgreSQL marks the whole transaction as failed, and it too is ended with ROLLBACK.

Isolation levels (SQL standard)

Level	Dirty read	Non-repeatable read	Phantom
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	prevented	possible	possible
REPEATABLE READ	prevented	prevented	possible
SERIALIZABLE	prevented	prevented	prevented

Transaction states

stateDiagram-v2
    [*] --> Active
    Active --> PartiallyCommitted: last statement done
    PartiallyCommitted --> Committed: log written to disk
    Active --> Failed: error
    PartiallyCommitted --> Failed: write fails
    Failed --> Aborted: rollback
    Committed --> [*]
    Aborted --> [*]

Exam tip: give each property with its mechanism (undo log, constraints, locking/MVCC, redo log) and a bank-transfer example; ACID "consistency" means integrity rules are kept, not the replica consistency of the CAP theorem.

A backup is a copy of data kept for restoring after deletion, corruption, hardware failure, ransomware or disaster. The three types differ in what they copy:

• Full backup: copies all selected data every time. It is the baseline that the other two depend on.
• Incremental backup: copies only the data changed since the last backup of any type (full or incremental).
• Differential backup: copies all data changed since the last full backup, so each differential grows until the next full backup.

graph LR
    F["Friday: FULL"] --> I1["Sun INC: Sun changes"]
    I1 --> I2["Mon INC: Mon changes"]
    I2 --> I3["Tue INC: Tue changes"]
    F --> D1["Sun DIFF: Sun changes"]
    F --> D2["Mon DIFF: Sun + Mon changes"]
    F --> D3["Tue DIFF: Sun + Mon + Tue changes"]

Differences

Basis	Full	Incremental	Differential
Copies	Everything	Changes since the last backup of any type	Changes since the last full
Size of each backup	Largest	Smallest, roughly constant	Grows daily until the next full
Backup time / bandwidth	Longest	Shortest	Medium, rising through the week
Total storage	Highest	Lowest	Medium (changes are copied again and again)
Sets needed to restore	1	Full + every incremental since it, in order	Full + the latest differential (2)
Restore time	Fastest	Slowest	Fast
Restore risk	Lowest	Highest: one missing or corrupt set breaks the chain	Low

Worked example

A 100 GB core banking database; about 5 GB of it changes each working day (Sunday–Thursday); a full backup is taken on Friday night. The server fails on Wednesday at 14:00, after Tuesday night's backup.

Night	Full every night	Incremental	Differential
Sun	100 GB	5 GB	5 GB
Mon	100 GB	5 GB	10 GB
Tue	100 GB	5 GB	15 GB
Wed	100 GB	5 GB	20 GB
Thu	100 GB	5 GB	25 GB
Total	500 GB	25 GB	75 GB

(Friday's 100 GB baseline full is common to all schemes and not counted.)

Restore on Wednesday: full-only = Tuesday's full (1 set); incremental = Friday full + Sun + Mon + Tue (4 sets); differential = Friday full + Tuesday differential (2 sets). Yet any nightly scheme loses every transaction since Tuesday night (about 14 hours): the backup type mainly affects recovery time, so data loss must be cut by other layers.

The two targets: RPO and RTO

• RPO (Recovery Point Objective): the maximum data loss allowed, measured in time (how far back the restored data may be). Minimising data loss = a very small RPO.
• RTO (Recovery Time Objective): the maximum downtime allowed before service is restored. Minimising recovery time = a small RTO.

Illustrative bank targets: core banking, ATM, card and mobile banking RPO ≈ 0 and RTO in minutes; HR and reporting, hours.

Recommended strategy for the bank

No single backup type meets both targets, so the bank needs layers:

Layer	Frequency	Minimises	How
1. Synchronous replication to a standby database in a nearby data centre	Continuous	Data loss (RPO ≈ 0) and time (RTO in minutes)	A commit completes only when the standby has it; on failure the standby is promoted
2. Asynchronous replication to a far-away DR site (another seismic zone/city)	Continuous	RPO of seconds; RTO under an hour	Survives a regional disaster
3. Transaction-log (journal) backups	Every 5–15 minutes	RPO of minutes for point-in-time recovery	Point-in-time recovery
4. Weekly full + nightly differential backups (after the end-of-day batch)	Weekly / nightly	RTO: a restore needs only 2 sets + logs	Short, reliable restore chain
5. Offline or immutable copy + storage snapshots	Daily/weekly	Recovery from ransomware or malicious deletion	Attackers cannot encrypt or delete it

Recovery path for each failure (how the targets are met):

Failure	Recovery	Data lost (RPO)	Downtime (RTO)
Database server or storage fails	Promote the synchronous standby	None	Minutes
Whole primary data centre lost	Fail over to the DR site	Seconds (async lag)	Under an hour
Logical corruption, bad batch run, accidental DELETE (replicas copy it instantly)	Restore weekly full + latest differential, then replay logs to just before the error	Minutes	A few hours
Ransomware encrypts systems and online backups	Restore from the immutable/offline copy, then logs	Since the last clean copy/logs	Hours

Justification

1. Minimum data loss: replication and frequent log backups capture every committed transaction; nightly backups alone would lose up to a day.
2. Minimum recovery time: failover to a hot standby takes minutes; for restores, differential beats incremental because only two sets are read before logs are replayed, and a short chain has fewer points of failure. Differentials also need far less storage than nightly fulls.
3. Replication is not a backup: it copies corruption, deletions and ransomware instantly, so point-in-time backups and an immutable copy are still essential.
4. Protection: follow the 3-2-1-1-0 rule (3 copies, 2 media, 1 off-site, 1 offline/immutable, 0 restore errors); encrypt backups, restrict and log access, and test restores and DR drills regularly to prove the RPO and RTO are met, as Bangladesh Bank's ICT security requirements expect. Month-end and year-end fulls are kept for audit retention.

Exam tip: incremental = since the last backup; differential = since the last full. Bank: replication + DR site + frequent log backups + weekly full/nightly differential + an immutable copy, tested regularly.