Multi-step operation without a transaction

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';

UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';

UPDATE accounts SET balance = balance + 100 WHERE id = 'B';

COMMIT;

Every multi-step operation that must succeed or fail as a unit needs to be wrapped in BEGIN/COMMIT. This is the single most important ACID rule in practice.

A rule of thumb: if two statements must always happen together, they belong in one transaction.

Critical

If the process crashes between the two UPDATEs, $100 is subtracted from A and never added to B.

Leaving a transaction open across user interaction

BEGIN;

SELECT seat FROM flights WHERE id = 101 FOR UPDATE;

-- show user the seat selection UI (30 seconds)

UPDATE flights SET seat_status = 'booked' WHERE id = 101;

COMMIT;

Use optimistic locking with a version column instead, or lock only at the final confirm step, not during the browsing step.

Transactions should surround the database writes, not the user's thinking time. Long open transactions cause lock pile-ups under concurrent load.

Design UX so the transaction starts only when the user clicks "Confirm", not when they start browsing.

High

The FOR UPDATE lock is held for 30 seconds during user interaction, blocking every other transaction that needs to access flight 101.

Using MyISAM in MySQL for critical data

CREATE TABLE orders (...) ENGINE=MyISAM;

CREATE TABLE orders (...) ENGINE=InnoDB;

MySQL historically had two major storage engines. InnoDB is the correct choice for any data that matters. MyISAM is only appropriate for read-heavy, non-critical, non-transactional data like full-text search indexes.

Set innodb_file_per_table=ON and default_storage_engine=InnoDB in MySQL configuration.

Critical

MyISAM has no transactions, no foreign key support, and no crash recovery. A mid-write crash can corrupt the table entirely.

Not retrying on deadlock

cursor.execute("BEGIN; UPDATE ...; UPDATE ...; COMMIT")

# Application crashes on deadlock error

for attempt in range(3):

try:

execute_transfer()

break

except DeadlockError:

sleep(backoff(attempt))

Deadlocks are a normal, expected event in any concurrent system. The database engine selects one transaction as the victim and rolls it back. The application must detect the specific deadlock error code and retry.

SQLSTATE 40P01 (PostgreSQL), error 1213 (MySQL), error 1205 (SQL Server). Always retry deadlock victims with exponential backoff.