6.2 Transaction control statements

Transaction control statements are crucial for managing database operations. They allow you to finalize changes, undo mistakes, and create savepoints for partial rollbacks. These tools give you precise control over how and when your data modifications take effect.

Understanding transaction control is key to maintaining data integrity. By mastering COMMIT, ROLLBACK, and SAVEPOINT, you can ensure your database stays consistent and reliable, even when dealing with complex operations or unexpected errors.

Transaction Statements

Finalizing and Reverting Transactions

Top images from around the web for Finalizing and Reverting Transactions

• 

  Why It Matters: Recording Business Transactions | Financial Accounting View original

  Is this image relevant?

• 

  Transactions in Processes | docs.camunda.org View original

  Is this image relevant?

• 

  Why It Matters: Recording Business Transactions | Financial Accounting View original

  Is this image relevant?

• 

  Transactions in Processes | docs.camunda.org View original

  Is this image relevant?

1 of 2

Top images from around the web for Finalizing and Reverting Transactions

• 

  Why It Matters: Recording Business Transactions | Financial Accounting View original

  Is this image relevant?

• 

  Transactions in Processes | docs.camunda.org View original

  Is this image relevant?

• 

  Why It Matters: Recording Business Transactions | Financial Accounting View original

  Is this image relevant?

• 

  Transactions in Processes | docs.camunda.org View original

  Is this image relevant?

1 of 2

• COMMIT

  statement finalizes a transaction by permanently saving all changes made during the transaction to the database
  • Once a transaction is committed, its effects are visible to other transactions and users
  • Committed changes cannot be undone, ensuring data integrity and consistency

• ROLLBACK

  statement reverts all changes made during a transaction, effectively canceling the transaction
  • Useful when an error occurs or the transaction needs to be aborted
  • Rolls back the database to its state before the transaction began, leaving no trace of the uncommitted changes

Savepoints for Partial Rollbacks

• SAVEPOINT

  statement creates a named point within a transaction to which you can later roll back
  • Allows for partial rollbacks, giving more control over transaction management
  • Multiple savepoints can be created within a single transaction
  • Savepoints are useful for complex transactions where only a portion of the changes may need to be reverted

• [ROLLBACK TO SAVEPOINT](https://www.fiveableKeyTerm:rollback_to_savepoint)

  statement rolls back the transaction to a previously defined savepoint
  • Changes made after the savepoint are undone, while changes made before the savepoint remain intact
  • Provides a way to selectively undo parts of a transaction without completely aborting it

Explicit Transaction Initiation

• [BEGIN TRANSACTION](https://www.fiveableKeyTerm:begin_transaction)

  statement explicitly starts a new transaction
  • Marks the beginning of a transaction block, allowing multiple statements to be grouped together
  • Explicit transactions provide more control over transaction boundaries compared to implicit transactions
  • Useful when a series of statements need to be executed as a single atomic unit

Transaction Properties and Control

ACID Properties

• Atomicity ensures that a transaction is treated as a single, indivisible unit of work
  • Either all changes within a transaction are applied successfully, or none of them are applied
  • Prevents partial updates and maintains data integrity in case of failures or errors
• Consistency guarantees that a transaction brings the database from one valid state to another
  • Transactions must adhere to defined constraints, rules, and integrity conditions
  • Ensures that the database remains in a consistent state before and after the transaction
• Isolation ensures that concurrent transactions do not interfere with each other
  • Each transaction should execute as if it were the only transaction running on the system
  • Prevents issues like dirty reads, non-repeatable reads, and phantom reads
• Durability guarantees that once a transaction is committed, its changes persist permanently
  • Even in the event of system failures or crashes, committed changes are not lost
  • Achieved through techniques like write-ahead logging and transaction logging

Concurrency Control Mechanisms

• Concurrency control manages simultaneous access to the database by multiple transactions
  • Ensures that transactions do not interfere with each other and maintain data integrity
  • Prevents issues like lost updates, dirty reads, and inconsistent data
• Techniques for concurrency control include:
  • Locking: Transactions acquire locks on data items to prevent conflicting access
  • Optimistic concurrency control: Transactions proceed without locks, but are validated before committing
  • Multiversion concurrency control (MVCC): Maintains multiple versions of data to allow concurrent access

Locking Strategies

• Locking is a common concurrency control mechanism used to manage access to shared resources
  • Transactions request locks on data items they need to access or modify
  • Locks can be shared (read locks) or exclusive (write locks)
• Two-phase locking (2PL) is a protocol that ensures serializability of transactions
  • Transactions acquire locks in a growing phase and release locks in a shrinking phase
  • Prevents deadlocks and ensures that transactions are executed in a serializable order
• Granularity of locks determines the level at which locks are applied
  • Fine-grained locks (row-level locks) allow for higher concurrency but incur more overhead
  • Coarse-grained locks (table-level locks) are simpler to manage but may limit concurrency
• Deadlock detection and resolution mechanisms are used to handle situations where transactions are waiting for each other's locks
  • Deadlock detection algorithms identify cycles in the wait-for graph
  • Deadlock resolution involves aborting one or more transactions to break the deadlock cycle