Database Principles Chapter 5 Transactions and Concurrency Control

1. Basic concepts of transactions

1.1 Why do we need transactions? What is a transaction?

Database Transaction is a collection of database operations that are either all executed successfully or all not executed. Transactions are designed to ensure data consistency and integrity in a database.

1.2 The four main characteristics of database transactions (ACID)

There are four key characteristics of database transactions, often referred to simply asACID：

1. Atomicity: All operations in a transaction either succeed or fail. If an operation in a transaction fails, all operations in the transaction are rolled back and the database is restored to the state it was in before the transaction began.

2. Consistency: The state of the database should be consistent before and after transaction execution. If the transaction executes successfully, the data should transition from one consistent state to another.

3. Isolation: Transactions execute without interference from other transactions. Even if multiple transactions execute concurrently, each transaction feels that it has exclusive access to the database and that the operations of other transactions are not visible until it commits. Isolation levels between transactions can be adjusted, usually four: read uncommitted, read committed, repeatable reads and serialization.

4. Durability: Once a transaction commits, changes to the database are permanent and will not be lost even if the system crashes.

1.3 Basic concepts involved in transactions

In the database management system.transaction(Services),rollback(Rollback),commit(submitted) andsavepoint(savepoints) are central concepts in managing and controlling database operations. They help ensure the consistency, integrity and reliability of data.

1.3.1 Transaction

(political, economic etc) affairsis a collection of operations in a database that are treated as a single unit of work. The basic characteristic of a transaction is that it either executes completely (commit) or does not execute at all (rollback), thus ensuring data consistency.

The life cycle of a transaction typically includes the following steps:

• Initiating transactions (e.g., performing certain database operations)
• Perform a series of operations (insert, update, delete, etc.)
• Commit the transaction (if all operations are successful)
• Or roll back a transaction (if an operation fails)

1.3.2. Rollback

rolling backoperation is used to undo all changes in a transaction. Rollback restores the database to the state it was in before the transaction started. It is usually triggered in the following situations:

• An error or exception occurred during transaction execution.
• The user decides to undo the operation in the transaction (e.g., there is a data validation failure).

The rollback operation ensures that operations in a transaction do not have an inconsistent effect on the database.

Example: Suppose you are doing a bank transfer operation. If the debit from account A succeeds but the deposit to account B fails, then performing a rollback operation will undo the debit operation, ensuring that both operations succeed together or fail together.

1.33. Commit

submit (a report etc)operation is used to confirm and save all changes in a transaction. After a commit, all changes to the database are saved permanently and cannot be rolled back. Commit means that the operation of the transaction has been successfully executed and the state of the database has been updated.

Once the transaction execution is complete, call thecommit will make all changes in the transaction effective and visible to other users.

Example: If all the steps in a bank transfer operation are completed successfully (e.g., both the debit and the deposit were successful), you would call thecommit to save the results of these two operations, ensuring that the effects of these operations on the database are permanent.

1.3.4. Savepoint

save pointis a marker in a transaction that allows you to specify an "intermediate state" during transaction execution. By setting a savepoint, you can create a recovery point at some point in the transaction. If subsequent operations fail, you can roll back to this save point instead of rolling back the entire transaction.

Savepoints provide a fine-grained rollback mechanism that is more efficient than therollback More flexibility because rolling back to a savepoint does not affect operations prior to the savepoint.

Example: In a complex transaction, you may perform multiple steps. For example, a transfer operation consists of several sub-steps: checking the account balance, debiting, depositing, and so on. You can set a save point at the "check account balance" step. If an error occurs during the "Deposit" process, you can roll back only to the save point, thus undoing the deposit operation without affecting the other steps (e.g., account balance check or debit).

1.3.5 Relationship Summary

conceptual	descriptive
Transaction	A set of database operations that either all succeed (commit), or fail all together (rollback）。
Rollback	A rollback operation that undoes all operations in a transaction and restores the state before the transaction began.
Commit	A commit operation that saves all changes in the transaction and ensures that the changes are permanent.
Savepoint	A midpoint in a transaction that allows rollback to a specified location in the transaction, rather than rolling back the entire transaction.

1.4 A Complete Example of MySQL Transaction Management

Suppose we need to perform a simple transfer operation to transfer $100 from account A to account B. The following is the complete transaction operation, including the usage of savepoints and rollbacks.

Example: Transfer operation

-- start transaction
START TRANSACTION.

-- Step 1: Check the balance of account A
SELECT balance FROM accounts WHERE account_id = 'A';

-- Step 2: Set the save point
SAVEPOINT check_balance.

-- Step 3: Deduct the balance of account A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';

-- Step 4: Deposit to account B
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- Step 5: Commit the transaction
COMMIT.

-- If an error occurs during the process, you can roll back to the save point and undo the subsequent operation.
-- If account A has an insufficient balance, roll back to the save point to undo the deposit operation
ROLLBACK TO SAVEPOINT check_balance;

-- Full rollback in case of transaction failure
-- ROLLBACK.

Description of service management

1. Open a transaction: BySTART TRANSACTION starts the transaction, and all subsequent SQL operations are executed in this transaction until you execute theCOMMIT maybeROLLBACK。

2. save point: UseSAVEPOINT Setting savepoints allows you to set multiple savepoints in a transaction. Rolling back to a savepoint will only undo operations from after that savepoint, and will not affect operations before the savepoint.

3. rolling back: If an operation in a transaction fails, it is possible to pass theROLLBACK maybeROLLBACK TO SAVEPOINT Performs a rollback to restore the state before the transaction started (or to a certain save point).

4. Submission of transactions: If all operations in the transaction are successful, you can use theCOMMIT Commit the transaction and all changes will be permanent.

2. Malfunctions

2.1 Types of faults

In computer science, especially in database management systems (DBMS), a fault is usually an anomaly or error in the operation of the system that prevents it from working as expected. Below are specific explanations for several of the faults you mentioned:

2.1.1. Database Transaction Failure

A transaction failure is a failure of a transaction to complete successfully during a database operation. A transaction is usually a series of database operations that are either fully executed or not executed at all, which is called "atomicity". Transaction failures can be caused by program errors, logical conflicts, constraint violations, and so on.

• rationale：

  • application program error
  • Database constraints are not satisfied (e.g., primary key conflicts, uniqueness constraints, etc.)
  • Deadlocks (multiple transactions waiting for resources from each other)

• affect (usually adversely): The transaction was not successfully committed and the data integrity of the database may be compromised.

• cure：

  • Use transaction rollback to undo operations that have already been performed and ensure that the database is restored to a consistent state.

---

2.1.2. System Failure

A system failure is a serious problem with computer hardware or software that prevents the entire system, including the database management system, from functioning properly. Such a failure may affect the availability or integrity of the database.

• rationale：

  • Operating system crash
  • Database software crashes
  • Hardware problems such as insufficient memory, CPU overload, etc.

• affect (usually adversely): The database may not start, some or all data may be lost, or the database may not respond to user requests.

• cure：

  • Regularly back up the database to ensure that data is recoverable.
  • Use high availability system designs (e.g., master-slave replication, failover mechanisms) to minimize the impact of system failures.

---

2.1.3. Media Failure

Media failure refers to damage or failure of storage media (e.g., hard disk, tape, etc.). Database data is stored on hard disks or other storage devices, and media failure can result in data loss, corruption, or inaccessibility.

• rationale：

  • Damage to storage devices (e.g., hard disk damage, battery failure, etc.)
  • Disk file system corruption
  • External environment (e.g. power failures, natural disasters, etc.)

• affect (usually adversely): Data may become unreadable or lost, or even the system may not boot.

• cure：

  • Use RAID technology or redundant storage systems to ensure that data can be recovered in the event of media corruption.
  • Conduct regular data backup and recovery exercises to ensure that lost data can be recovered from backups.

---

2.1.4. Computer Virus

A computer virus is a type of malicious software that replicates itself and spreads, usually causing damage to a computer system, file or program. Viruses can infect database systems and may result in data loss, disclosure, modification, or system paralysis.

• rationale：

  • Infected files, programs or operating systems
  • Downloaded or installed external programs or files containing viruses
  • Web-borne viruses

• affect (usually adversely)：

  • Data tampering, loss or leakage
  • System crashes, performance degradation
  • Unauthorized remote access and control

• cure：

  • Use anti-virus software to scan and remove viruses regularly.
  • Implement secure protection strategies such as firewalls, access control, and regular software updates to prevent virus intrusion.

---

2.1.5 Summary

• transaction failureRelated to the operational logic and data consistency of the database.
• system failureInvolves the collapse or unavailability of the entire system.
• Media FailureIt is the damage to the storage hardware that results in data loss or inaccessibility.
• computer virusOn the other hand, it refers to damage caused by malware that may affect the integrity of data and the security of the system.

All of these failures are important factors that must be considered when designing and maintaining a database system, and therefore require appropriate preventive measures such as backups, fault tolerance, encryption, and security policies.

3. Recovery technologies

Key issues involved in recovery mechanisms

1. How to create redundant data
   • Data dump (backup)
   • Logging log files (logging)
2. How to implement database recovery with this redundant data

The core purpose of database recovery mechanism is to be able to restore data integrity and consistency as much as possible in case of failure (such as system crash, hardware damage, software error, etc.) to ensure the normal operation of the database. The establishment of effective redundant data, log file mechanisms and reasonable data dump strategy is the key to database recovery.

3.1.Creation of redundant data

Redundant data refers to storing data from a database in multiple forms on different physical media so that the data can be recovered in the event of a failure. Creating redundant data can effectively prevent data loss and speed up the recovery process. The following are common redundant data strategies:

(1) Data Replication (Replication)

• Master-Slave Replication (MSR): Synchronizes all changes to the database to one or more slave database nodes. If the master database fails, you can switch to the slave databases to continue operations.
• Multi-Master Replication (MMR): Multiple database nodes are available for write operations, and data on all nodes is always synchronized. This strategy is suitable for scenarios with high availability and high fault tolerance requirements.

(2) RAID（Redundant Array of Independent Disks）

• RAID 1 (mirroring): By copying data to two hard disks, even if one hard disk fails, the data can still be recovered from the other.
• RAID 5 (with parity): Utilizing data and parity information distributed across multiple disks, when one disk fails, the data can be recovered from the remaining disks and parity information.
• RAID 10 (mirroring + striping): Combines the features of RAID 1 and RAID 0 to provide higher read/write performance and data redundancy.

(3) Database snapshots (Snapshots)

• A snapshot is a full backup of the current state of a database, usually created when the database is updated. Snapshots can be stored in different storage locations and can be quickly rolled back to the state of the database at the time of the snapshot in the event of a failure.

3.2. Data dump (Backup)

Data dumping is the process of copying the current state of a database or data at a given point in time to another secure storage location (e.g., tape, cloud storage, external hard disk, etc.) to prevent data loss. Common backup strategies include:

(1) Full Backup

• Backup of the entire database, including all tables, indexes, views, etc. A full backup ensures the integrity of the backed up data, but usually requires a longer time and more storage space.

(2) Incremental Backup (IB)

• Backs up only data that has changed since the last backup. Incremental backups save storage space and are faster than full backups, but restoration relies on the order of the full backup and all incremental backups.

(3) Differential Backup

• Backs up data that has changed since the last full backup. When restoring, you usually need only the full backup and the latest differential backup.

(4) Log Backup (Transaction Log Backup)

• A backup of the database's transaction log records all transactions since the last log backup. Log backups can help restore the database to a point in time and ensure data consistency.

3.3. Log files (Logging)

The log file records all transactional activity in the database, ensuring data consistency and integrity. Log files are critical for database recovery, especially in the event of a system crash, where logs can be utilized to roll back (Undo) or redo (Redo) transactions. Log files are usually categorized into the following two types:

(1) Transaction Log

• Each transaction records the start, commit, and rollback information in a log file. The role of the transaction log is to ensure database persistence and consistency.
• In the event of a crash, logging can help us get back to some consistent state before or after the transaction commits.
• pass (a bill or inspection etc)WAL（Write-Ahead Logging）The transaction log is written to disk before the database operation, ensuring that all committed transactions can be redone even in the event of a crash.

(2) Redo Logs and Rollback Logs (Redo/Undo Logs)

• Redo Log: Record the modification operations of a transaction that has already been committed, and you can redo these operations after a crash to ensure that the data of the committed transaction is not lost.
• Rollback Log (Undo Log): Records the modification operation of an uncommitted transaction and is used to roll back modified data to its original state when the recovery transaction fails.

3.4. Implement recovery with redundant data, logs and backups

Database failure recovery can be achieved by utilizing redundant data, log files, and data dumps. The main recovery methods include:

(1) Full Recovery

• Ideal for database failures or crashes where full, incremental, and log backups are utilized to restore the database to its most recent state prior to the failure.
• Recovery Steps:
  1. Restore the last full backup.
  2. Restore all related incremental or differential backups.
  3. Redo via the transaction log to restore all committed transactions.

(2) Point-in-Time Recovery (PIR)

• Point-in-time recovery is used when there is a need to restore the database state to a specific point in time. Using backups and log files you can restore the database to a point in time before the failure occurred.
• Recovery Steps:
  1. Restore the last full backup.
  2. Restore the associated incremental or differential backup.
  3. Use the transaction log to roll back to a specified point in time.

(3) Fast Recovery

• Use redundant data from the database, such as replicas and snapshots, to quickly restore services. Typically used in scenarios with high system availability requirements to reduce database downtime by quickly switching to a standby system or restoring snapshots.

(4) Disaster Recovery

• Disaster recovery is achieved using redundant data, remote backups, and distributed replication mechanisms in the event of a serious failure (e.g., hardware damage, natural disaster, etc.).
• Failure recovery time can be minimized through master-slave replication or backups across data centers.

---

summarize
Database recovery mechanisms rely on a variety of technical means, such as redundant data storage, logging and data backup. By reasonably designing these mechanisms, it can be ensured that the database can be quickly and reliably restored to a consistent state in the event of system failure, hardware damage, data loss, and so on. Achieving high availability and disaster recovery is the key to guaranteeing the long-term stable operation of the database system.

4. Concurrency control

In the database management system.Concurrent execution of transactionsIt is a situation where multiple transactions are executed at the same time. Since multiple transactions may access and modify the same data at the same time, this concurrent operation can lead to a number of concurrency problems. In order to solve these problems, database systems use a variety ofConcurrent control technologye.g.locking mechanismcap (a poem)Optimistic control(MVCC), etc.

4.1. Possible problems with concurrency

The following three common problems can occur when executing transactions concurrently:
(1) Lost Update

• define: A lost modification is when two or more transactions are executed concurrently and the modifications of one transaction are overwritten by the modifications of another, resulting in the loss of certain data updates.
• typical example：
  • Transaction T1 Read dataX = 100and then update it toX = 150。
  • Transaction T2 also reads dataX = 100and then update it toX = 120。
  • proveX = 120and the update of T1 is lost.

(2) Non-repeatable Read

• define: An unrepeatable read is when a piece of data is read multiple times in a transaction and the result of the reads changes. This usually occurs when a transaction reads data and then another transaction modifies or deletes that data.
• typical example：
  • Transaction T1 Read dataX = 100。
  • Transaction T2 has been updated.Xand replace it with the followingX = 150。
  • When T1 reads againX when gettingX = 150, which is different from the first reading.

(3) Dirty Read

• define: Reading "dirty" data is when a transaction reads data that has not yet been committed by another transaction. If the transaction is subsequently rolled back, the data read becomes invalid or "dirty".
• typical example：
  • Transaction T1 updated the dataXThe first step in the process is to remove it from theX = 100 change intoX = 150, but has not yet been submitted.
  • Transaction T2 readsX = 150Even if T1 doesn't commit, if T1 then rolls back, T2 reads theX = 150 It's just dirty data.

4.2. Key techniques for concurrency control

In order to solve the problems caused by concurrency, database systems usually use the following two concurrency control techniques:Locking mechanism cap (a poem)Optimization Control Method / Multi-Version Concurrency Control (MVCC)。

(1) Locking

A blocking mechanism ensures that only one transaction can access a particular piece of data at the same moment by placing a lock on the transaction's access to the data. There are several common types of locks:

• Shared Lock (S-Lock): Transactions can read data, but cannot modify it. If a transaction puts a shared lock on data, other transactions can put a shared lock on that data, but cannot modify it.
• Exclusive Lock (X-Lock): Transactions can not only read data but also modify it. If a transaction puts an exclusive lock on data, other transactions can neither read nor modify the data.

Advantages of the embargo：

• Concurrency problems such as lost modifications, non-repeatable reads, and reading dirty data are avoided by strictly controlling access to the data.

Disadvantages of the embargo：

• Deadlock: If two or more transactions hold locks needed by each other, resulting in the transaction being unable to continue execution, a deadlock is formed.
• performance loss: Holding locks for long periods of time affects concurrency and leads to degradation of database performance.

Common blocking protocols are:

• Two-Phase Locking (2PL) Protocol: Transactions add and then release locks during execution, and lock release can only occur at the final stage of the transaction (commit or rollback). This protocol avoids lost modifications, unrepeatable reads and dirty reads.

(2) Optimization Control Method / Multi-Version Concurrency Control (MVCC)

Multi-Version Concurrency Control (MVCC) is an optimistic lock-based concurrency control mechanism that allows transactions to create multiple versions of data without the need for locking. Each transaction can see a different version of the data, thus avoiding concurrency problems caused by locking.

4.3How MVCC works：

• Data versioning: The database system maintains multiple versions of each data item, and each transaction gets a timestamp or transaction ID at the beginning that identifies the version of the data it sees.
• Version checking on transaction commit: At transaction commit time, the system checks to see if the data operated on by the transaction has been modified by another transaction. If there is a conflict (for example, the other transaction has already committed the update), the current transaction is rolled back.
• concurrent read: Because each transaction sees the version of the database it started with, transactions can read data concurrently without waiting for other transactions to finish.

Advantages of MVCC：

• lock-free concurrency: MVCC allows multiple transactions to read different versions of data in parallel, avoiding lock contention and therefore improving concurrency performance.
• Avoids deadlocks: Since no locking is required, MVCC avoids deadlock problems.

Disadvantages of MVCC：

• space overhead: Maintaining multiple versions of each data item requires additional storage space.
• Versioning Complexity: Maintaining multiple versions of data requires additional mechanisms to ensure the validity of versions and to clean up outdated versions.

MVCC Implementation Mechanism

MVCC implements multi-version data storage by storing a version number (usually a transaction ID) for each data item. Whenever a transaction modifies data, the system generates a new version of the data and gives it a new timestamp or transaction ID. read operations select the appropriate version based on the start timestamp of the transaction.

---

Summary:

• Loss of modifications, non-repeatable reads and dirty readsis a concurrency problem that can occur when executing transactions concurrently.
• To address these issues, database systems typically useClosure mechanismcap (a poem)MVCC (Multi-Version Concurrency Control)Two concurrency control techniques.
• Closure mechanismConflicts between concurrent transactions are prevented by adding locks, but can lead to performance problems such as deadlocks.
• MVCCInstead, concurrency is improved by multi-version data storage and optimistic control to avoid lock contention, but requires additional space and versioning.

Depending on the application scenario, database systems may use different concurrency control techniques to balance performance and data consistency requirements.