Transaction Management in DBMS

This set of vocabulary flashcards covers the fundamental concepts of transaction management, the ACID properties, concurrency control, and recovery mechanisms in MySQL as presented in the Week 12 lecture notes.

Interleaving

A method used in high-performance DBMS like MySQL where the CPU switches to another task instead of waiting for slow Disk I/O, gaining efficiency through simultaneous action.

CPU Actions

Actions within the DBMS that involve calculations, logic, and memory management.

I/O Actions

Actions within the DBMS that involve reading from or writing to physical storage.

Atomicity

The ‐‐‐All or Nothing‐‐‐ rule of the ACID model; if one part of a transaction fails, the entire unit is aborted.

Consistency

A property of the ACID model ensuring a transaction transforms the database from one valid state to another while maintaining all defined constraints.

Isolation

A property of the ACID model where transactions occur independently, and partial effects of incomplete transactions are not visible to others.

Durability

A property of the ACID model ensuring that once changes are committed, they are persistent and survive system crashes or power failures.

Lost Updates

A concurrency problem where two transactions read the same data and then update it, causing the second update to overwrite the first.

Uncommitted Dependency (Dirty Read)

A concurrency problem where a transaction reads data that has been changed by another transaction but has not yet been committed.

Inconsistent Analysis

A concurrency problem where a transaction reads several values to calculate a total while another transaction is mid-way through updating those values.

Phantom Reads

A concurrency problem where a query returns a set of rows, but when re-run, finds different rows inserted by a recently committed transaction.

Locking

A procedure used to control concurrent access to data by requiring a transaction to claim a request on a data item before a read or write operation.

Shared Lock (S)

A type of lock that allows a transaction to read a row and can be held by multiple transactions at once.

Exclusive Lock (X)

A lock required to update or delete data that blocks all other lock requests until it is released; only one transaction can hold it at a time.

Deadlock

A state where Transaction A waits for a lock held by B, and B waits for a lock held by A, which MySQL automatically detects and resolves by rolling back one transaction.

START TRANSACTION

The SQL command that begins a logical unit of work in MySQL.

COMMIT

The SQL command that saves all changes made during a transaction permanently to the disk.

ROLLBACK

The SQL command that undoes all changes made since the current transaction started, returning the database to its previous state.

Autocommit

The default MySQL behavior where every single SQL statement is treated as an individual transaction.

Redo Log (Transaction Log)

A physical file recording every change used by MySQL to ‐‐‐redo‐‐‐ committed changes that had not yet hit the main data files during a system crash.

Undo Log

A record of how to reverse changes, used for ROLLBACK commands and providing consistent reads for other users.

Binary Log (Binlog)

A log used specifically for database replication and point-in-time recovery.

Transaction (SQL)

A group of one or more database operations, usually Data Manipulation Language (DML), treated as a single unit of work that either fully completes or has no effect at all.