Transaction Management and Concurrency Control

Question–Answer flashcards covering key concepts from the lecture on transaction management, ACID properties, logging, locking mechanisms, deadlocks, timestamping, optimistic control, and IBM DB2 isolation levels.

Transaction

A logical unit of work that must be entirely completed or entirely aborted, with no intermediate states.

What does a database request correspond to in an application program?

A single SQL statement.

When a transaction is successful, how does it affect the database?

It moves the database from one consistent state to another consistent state.

How does a DBMS handle a transaction that cannot complete?

It restores the database to its previous consistent state (rollbacks).

Who is ultimately responsible for a transaction’s correctness?

The end user or programmer.

Name the five key properties summarized by ACID + S.

Atomicity, Consistency, Isolation, Durability, and Serializability.

All operations must complete; otherwise the transaction is aborted.

Atomicity

A transaction must preserve database integrity; if violated, the transaction is aborted.

Consistency

Data being used by one transaction cannot be used by another until the first finishes

Isolation

Once a transaction commits, its changes are permanent even if the system fails.

Durability

That interleaved (concurrent) schedules of transactions yield consistent results equivalent to some serial order.

Serializability

Which SQL commands terminate a transaction?

COMMIT and ROLLBACK.

What happens when any action within a transaction fails?

The DBMS issues a ROLLBACK, returning the database to its pre-transaction state.

What is the purpose of the transaction log?

To keep a record of all transactions that update the database for recovery and auditing.

List three pieces of information stored for each log entry of an update.

The operation (insert/update/delete), the affected table, and the before-and-after values of changed fields.

The coordination of simultaneous transaction execution in a multi-user environment.

Concurrency Control

Two concurrent transactions update the same data; the update made by the first transaction is overwritten (lost)

Lost Update Problem.

A transaction reads data modified by another transaction that later rolls back, leaving inconsistent data.

Uncommitted Data (dirty read) problem.

A transaction reads data before and after another concurrent transaction changes it, producing inconsistent results.

Inconsistent Retrievals problem.

It orders the operations of concurrent transactions to ensure serializability. S

It orders the operations of concurrent transactions to ensure serializability. Scheduler

What does a lock guarantee?

Exclusive use of a data item to the transaction that holds the lock.

The level at which data items are locked: database, table, page, row, or field.

lock granularity.

When is database-level locking suitable?

For batch processing where only one transaction needs the database.

Why is database-level locking unsuitable for multi-user environments?

It prevents all other transactions from accessing any table, greatly reducing concurrency.

What does table-level locking allow other transactions to do?

Access other tables in the same database that are not locked.

What happens if many row locks on the same page are requested?

The DBMS may escalate row locks to a page lock.

What is a binary lock?

A lock that can be in only two states: locked (1) or unlocked (0).

What is an exclusive lock?

A lock that gives a single transaction sole access to a data item, used when updates may conflict.

What is a shared lock?

A lock that allows multiple transactions to read the same data item simultaneously, provided no exclusive lock exists.

Why do shared/exclusive locks increase lock-manager overhead?

The manager must check lock types, handle READLOCK, WRITELOCK, UNLOCK, and support upgrades/downgrades.

What are the two phases of the Two-Phase Locking (2PL) protocol?

Growing phase (acquiring locks) and shrinking phase (releasing locks).

Does Two-Phase Locking prevent deadlocks?

No; it guarantees serializability but still allows deadlocks to occur.

State one rule of the 2PL protocol regarding unlock operations.

No unlock operation may precede a lock operation in the same transaction.

Define a deadlock.

A situation where two transactions wait indefinitely for each other to release locks they need.

Name the three basic deadlock control techniques.

Deadlock prevention, deadlock detection, and deadlock avoidance.

What is the main idea behind timestamping methods for concurrency control?

Assign each transaction a unique, increasing timestamp to order all operations.

What two properties must transaction timestamps satisfy?

Uniqueness and monotonicity (always increasing).

Briefly differentiate Wait/Die from Wound/Wait schemes.

Both decide which transaction rolls back in conflict: Wait/Die lets older wait or younger die; Wound/Wait lets older wound (force rollback) younger, younger waits for older.

What assumption underlies optimistic concurrency control?

Most transactions will not conflict, so they run without locks until commit time.

List the three phases of an optimistic concurrency protocol.

Read phase, Validation phase, and Write phase.

Which four problems does IBM DB2 identify without concurrency control?

Lost update, Uncommitted read, Non-repeatable read, and Phantom read.

Name the four DB2 isolation levels (acronyms).

UR, CS, RS, RR.

What is Uncommitted Read (UR) also called, and what risk does it allow?

Dirty read; it allows reading data modified but not yet committed by other transactions.

What does Cursor Stability (CS) lock and for how long when a row is read only?

It locks the current cursor row until another row is fetched or the unit of work ends.

What enhancement does Currently Committed (CC) add to Cursor Stability?

Readers can see the last committed version even while a writer holds the row, improving concurrency.

What rows are locked under Read Stability (RS)?

All rows retrieved within the unit of work and all rows that qualify for the cursor's result set.

Which isolation level offers the highest locking and least concurrency in DB2?

Repeatable Read (RR).

At what levels can isolation be set in DB2?

Session, connection, or individual SQL statement.

How is statement-level isolation specified in SQL?

By appending WITH {UR | CS | RS | RR} to the SELECT statement.

What is lock escalation in DB2?

Automatic replacement of many row locks with a table lock when memory thresholds are exceeded.

Which two configuration parameters affect lock escalation?

LOCKLIST (total lock memory) and MAXLOCKS (percentage one application can use).

What does the LOCKTIMEOUT parameter control?

How long an application waits for a lock before timing out; default is -1 (infinite).

How can a single connection override LOCKTIMEOUT at runtime?

By setting the CURRENT LOCK TIMEOUT register with SET LOCK TIMEOUT.