Database Transaction Concurrency: Failures, Logs, and Schedules

Posted on Feb 19, 2025 in Computers

Homework #9

Lauri Burke

CINS 370

4/29/2016

21.1. Concurrent Execution in Multiuser Systems

A multiuser system is a system which can be used by more than one user at a time.

Why is Concurrency Control Needed?

Concurrency control is used to prohibit transaction conflicts. Here are some examples of problems that can occur without it:

  • The Lost Update Problem: Two or more transactions try to read and update the same data concurrently. The last update overwrites previous updates, resulting in lost data.
  • Temporary Update Problem (Dirty Read): A transaction updates a database item, but then fails before committing. Another transaction reads the uncommitted (dirty) data, leading to incorrect results if the first transaction rolls back.
  • The Incorrect Summary Problem: A transaction calculates an aggregate summary function on a number of database items while other transactions are updating some of these items. The aggregate function may calculate some values before they are updated and others after they are updated, leading to an inconsistent summary.

21.2. Types of Database Failures

Failures in database management systems are categorized as transaction, system, and media failures.

  • Computer Failure: During transaction execution, the computer hardware, media, software, or network may crash.
  • Transaction or System Error: Errors such as divide by zero or integer overflow can cause a transaction to fail. Logical programming errors or erroneous parameter values can also lead to failures. User interruption is another cause.
  • Local Errors: Exceptions or errors detected by the transaction itself.
  • Concurrency Control Enforcement: Deadlock transactions will be aborted.
  • Disk Failure: Data stored on the disk may be lost due to a read-write error or a read/write head crash.
  • Physical Problems and Catastrophes: Power failure, robbery, fires, etc.

Catastrophic Failure

A catastrophic failure is a very rare event, such as hard drive damage, fire, power surges, destruction of hardware, or theft.

21.3. Read_item and Write_item Operations

Read_item:

  1. Find the address of the disk block that contains item X.
  2. Copy the disk block into a buffer in main memory if that disk is not already in some main memory buffer.
  3. Copy X from the buffer into program variable x.

Write_item:

  1. Find the address of the disk block that contains item X.
  2. Copy the disk block into a buffer in main memory if that is not already in some main memory buffer.
  3. Copy item X from the program variable named X into its correct location in the buffer.
  4. Store the updated block from the buffer back to disk.

21.5. The System Log

What is the System Log Used For?

A system log is used to keep track of what happens in your system. It may contain answers to errors or a history of changes.

Typical Records in a System Log:

  • Start_transaction – start
  • Commit – finish
  • Read – read
  • Write – write
  • Abort – quit or don’t change anything.

Transaction Commit Points

Transaction commit points are where a change is permanent; you are committed to the change. It’s where the transaction has completed and all the reads and writes that go along with them.

21.9. Serial and Serializable Schedules

Serial Schedule

A serial schedule is one that will run and terminate the way we want it to. It will leave the database in a consistent, stable condition.

Serializable Schedule

A serializable schedule is one that is computationally equivalent to a serial execution.

Why are Serial and Serializable Schedules Considered Correct?

A serial schedule is always correct since we assume transactions do not depend on each other and we assume that each transaction when run in isolation transforms a consistent database into a new consistent state and therefore a set of transactions executed one at a time must also be correct.

Exercises

Submit answers to the following 3 Exercises

21.17.

21.22. Which of the following schedules is (conflict) serializable? For each serializable schedule, determine the equivalent serial schedules.

  1. r1(X); r3(X); w1(X); r2(X); w3(X); Not Serializable
  2. r1(X); r3(X); w3(X); w1(X); r2(X); Not Serializable
  3. r3(X); r2(X); w3(X); r1(X); w1(X); Serializable
  4. r3(X); r2(X); r1(X); w3(X); w1(X); Not Serializable

21.23. Consider the three transactions T1, T2, and T3, and the schedules S1 and S2 given below. Draw the serializability (precedence) graphs for S1 and S2, and state whether each schedule is serializable or not. If a schedule is serializable, write down the equivalent serial schedule(s).

T1: r1 (X); r1 (Z); w1 (X);

T2: r2 (Z); r2 (Y); w2 (Z); w2 (Y);

T3: r3 (X); r3 (Y); w3 (Y);

S1: r1 (X); r2 (Z); r1 (Z); r3 (X); r3 (Y); w1 (X); w3 (Y); r2 (Y); w2 (Z); w2 (Y); Serializable

S2: r1 (X); r2 (Z); r3 (X); r1 (Z); r2 (Y); r3 (Y); w1 (X); w2 (Z); w3 (Y); w2 (Y); Not Serializable.





<img alt= “/> <img alt= “/> <img alt= “/> <img alt= “/>