Transactions and Database Performance

transaction

group of operations treated as one unit

ACID

atomicity consistency isolation durability

atomicity

all operations succeed or none do

consistency

database moves between valid states

isolation

concurrent transactions do not improperly interfere

durability

committed data survives failure

commit

save transaction changes

rollback

undo transaction changes

dirty read

read uncommitted data from another transaction

phantom read

new rows appear during transaction

deadlock

two transactions wait forever on each other

read replica

database copy used for reads

write primary

database node that accepts writes

sharding

splitting data across nodes

replication

copying data across nodes

cache

fast storage for frequently accessed data

cache invalidation

removing or updating stale cached data

write heavy workload

many inserts updates or deletes

read heavy workload

many selects or lookups

index tradeoff

faster reads but slower writes

composite index

index on multiple columns