Database Transactions and Concurrency
Database transactions group operations into atomic units that either fully complete or fully roll back. Concurrency control mechanisms — locks, MVCC, isolation levels — coordinate simultaneous access so that transactions execute correctly without corrupting shared data.
itDatabases and data storage | OpenSkills.info
Intro
Database Transactions and Concurrency
Applications rarely change one value in isolation. A purchase reserves stock, records payment, and creates an order. A transfer debits one account and credits another. These steps describe one business action, even when they require several SQL statements.
A transaction groups those statements into one unit of work. The database either commits the unit or rolls it back. A commit accepts the changes. A rollback discards them.
Transactions also control what happens when many sessions work at the same time. That problem is concurrency. Without a concurrency strategy, valid operations can combine into an invalid result.
Imagine two customers buying the last item. Each session reads a quantity of one. Each then subtracts one. Both purchases may appear successful even though only one item existed. Each statement made sense alone. Their interleaving broke the business rule.
The database gives you mechanisms to prevent or detect that outcome. Your job is to choose the right mechanism, keep transactions bounded, and handle conflicts as expected events.
The transaction boundary
A transaction begins before a related set of reads and writes. It ends with either commit or rollback.
The boundary should match the smallest complete business action. If the boundary is too small, partial work can escape. If it is too large, locks and row versions live longer, conflicts become more likely, and recovery costs grow.
Do not hold a database transaction open while a person reviews a screen or while a service waits on a slow external API. Gather external input first. Then start the transaction, validate current database state, make the changes, and finish.
Savepoints provide a finer control point inside a transaction. You can roll back work performed after a savepoint without discarding earlier work. A savepoint does not make that earlier work visible to other transactions. Only the final commit does that.
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://www.postgresql.org/docs/current/tutorial-transactions.html
Supports
- Transactions group multiple steps into an atomic all-or-nothing operation
- Commit makes a completed transaction durable under PostgreSQL's guarantees
- Other transactions do not see intermediate states
- Rollback discards the transaction's updates
- Savepoints support partial rollback inside a transaction
- https://www.postgresql.org/docs/current/transaction-iso.html
Supports
- The SQL isolation levels are defined through prohibited concurrency phenomena
- Dirty reads, nonrepeatable reads, phantom reads, and serialization anomalies have distinct definitions
- Serializable execution has an effect consistent with some serial order
- PostgreSQL Read Committed uses a new snapshot for each command
- PostgreSQL Repeatable Read and Serializable transactions can require complete retries
- https://www.postgresql.org/docs/current/mvcc-intro.html
Supports
- PostgreSQL uses MVCC to provide statements with snapshots of data
- Reading and writing do not conflict in PostgreSQL's MVCC model
- Explicit locks remain available where application-controlled conflict points are needed
- https://www.postgresql.org/docs/current/explicit-locking.html
Supports
- PostgreSQL provides table, row, page, and advisory locking facilities
- Conflicting lock modes cannot be held by different transactions on the same resource
- Row-level locks block writers and lockers rather than ordinary readers
- Deadlocks can arise from explicit or row-level locks
- PostgreSQL detects a deadlock and aborts one participating transaction
- Consistent lock acquisition order is the primary defense against deadlocks
- https://www.postgresql.org/docs/current/sql-update.html
Supports
- UPDATE supports conditions based on current column values
- UPDATE reports the number of rows affected
- A version predicate and affected-row count can implement an application-level optimistic check
- https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html
Supports
- InnoDB implements Read Uncommitted, Read Committed, Repeatable Read, and Serializable
- InnoDB defaults to Repeatable Read
- InnoDB isolation levels change snapshot and locking behavior
- https://dev.mysql.com/doc/refman/8.4/en/innodb-locking-reads.html
Supports
- InnoDB provides FOR SHARE and FOR UPDATE locking reads
- Locking reads protect selected current rows until commit or rollback
- NOWAIT returns an error instead of waiting for a lock
- SKIP LOCKED omits locked rows and is unsuitable for a general consistent view
- https://dev.mysql.com/doc/refman/8.4/en/innodb-deadlocks.html
Supports
- InnoDB detects deadlocks and rolls back a victim transaction
- Applications must handle complete transaction retries
- Short transactions, consistent operation order, and useful indexes reduce deadlock likelihood
- https://dev.mysql.com/doc/refman/8.4/en/innodb-error-handling.html
Supports
- InnoDB deadlocks roll back the entire transaction
- Lock wait timeout rollback scope differs from deadlock rollback scope by default
- Applications should classify deadlocks and lock timeouts and retry at the correct scope
- https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver17
Supports
- SQL Server combines locking and row-version-based isolation
- Isolation levels control allowed concurrency effects and read behavior
- Row versioning can provide statement-level or transaction-level snapshots
- Row versioning reduces shared read locks but consumes version-store resources
- Higher isolation can increase blocking and resource use
- https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver17
Supports
- Consistent access order and short transactions reduce deadlock likelihood
- User interaction inside transactions increases lock duration and blocking
- Deadlock victims should be handled by application retry logic
- https://learn.microsoft.com/en-us/azure/architecture/databases/guide/transactional-out-box-cosmos
Supports
- A transactional outbox stores a business object and its event in the same transaction
- A worker publishes pending events after the transaction commits
- Consumers should be idempotent because message delivery can repeat