Oracle Database Transactions and Locking Revealed

Oracle Database Transactions and Locking Revealed

by Thomas Kyte, Darl Kuhn
Released November 2014
Publisher(s): Apress
ISBN: 9781484207604

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Oracle Database Transactions and Locking Revealed provides much-needed information for building scalable, high-concurrency applications and deploy them against the Oracle Database. Read this short, 150-page book that is adapted from Expert Oracle Database Architecture to gain a solid and accurate understanding of how locking and concurrency are dealt with by Oracle Database. Also learn how the Oracle Database architecture accommodates user transactions, and how you can write code to mesh with how Oracle Database is designed to operate.

Good transaction design is an important facet of highly-concurrent applications that are run by hundreds, even thousands of users who are all executing transactions at the same time. Transaction design in turn relies upon a good understanding of how the underlying database platform manages of the locking of resources so as to prevent access conflicts and data loss that might otherwise result from concurrent access to data in the database.

Oracle Database Transactions and Locking Revealed covers in detail the various lock types, and also different locking schemes such as pessimistic and optimistic locking. Then you'll learn about transaction isolation and multiversion concurrency, and how the various lock types support Oracle Databases transactional features. You'll learn some good tips for transaction design, as well as some bad practices and habits to avoid. Coverage is also given to redo and undo, and their role in concurrency. This is an important book that anyone developing highly-concurrent applications will want to have handy on their shelf.

Table of contents

  1. Cover
  2. Title
  3. Copyright
  4. Contents at a Glance
  5. Contents
  6. About the Authors
  7. Acknowledgments
  8. Introduction
  9. Chapter 1: Getting Started
    1. Background
      1. Locking
      2. Concurrency Control
      3. Multiversioning
      4. Transactions
      5. Redo and Undo
    2. Summary
  10. Chapter 2: Locking and Issues
    1. What Are Locks?
    2. Lost Updates
    3. Pessimistic Locking
    4. Optimistic Locking
      1. Optimistic Locking Using a Version Column
      2. Optimistic Locking Using a Checksum
    5. Optimistic or Pessimistic Locking?
    6. Blocking
      1. Blocked Inserts
      2. Blocked Merges, Updates, and Deletes
    7. Deadlocks
    8. Lock Escalation
    9. Summary
  11. Chapter 3: Lock Types
    1. DML Locks
      1. TX (Transaction) Locks
      2. TM (DML Enqueue) Locks
    2. DDL Locks
    3. Latches
      1. Latch “Spinning”
      2. Measuring the Cost of Latching a Shared Resource
    4. Mutexes
    5. Manual Locking and User-Defined Locks
      1. Manual Locking
      2. Creating Your Own Locks
    6. Summary
  12. Chapter 4: Concurrency and Multiversioning
    1. What Are Concurrency Controls?
    2. Transaction Isolation Levels
      1. READ UNCOMMITTED
      2. READ COMMITTED
      3. REPEATABLE READ
      4. SERIALIZABLE
      5. READ ONLY
    3. Implications of Multiversion Read Consistency
      1. A Common Data Warehousing Technique That Fails
      2. An Explanation for Higher Than Expected I/O on Hot Tables
    4. Write Consistency
      1. Consistent Reads and Current Reads
      2. Seeing a Restart
      3. Why Is a Restart Important to Us?
    5. Summary
  13. Chapter 5: Transactions
    1. Transaction Control Statements
    2. Atomicity
      1. Statement-Level Atomicity
      2. Procedure-Level Atomicity
      3. Transaction-Level Atomicity
      4. DDL and Atomicity
    3. Durability
      1. WRITE Extensions to COMMIT
      2. COMMITS in a Nondistributed PL/SQL Block
    4. Integrity Constraints and Transactions
      1. IMMEDIATE Constraints
      2. DEFERRABLE Constraints and Cascading Updates
    5. Bad Transaction Habits
      1. Committing in a Loop
      2. Using Autocommit
    6. Distributed Transactions
    7. Autonomous Transactions
      1. How Autonomous Transactions Work
      2. When to Use Autonomous Transactions
    8. Summary
  14. Chapter 6: Redo and Undo
    1. What Is Redo?
    2. What Is Undo?
    3. How Redo and Undo Work Together
      1. Example INSERT-UPDATE-DELETE-COMMIT Scenario
    4. Commit and Rollback Processing
      1. What Does a COMMIT Do?
      2. What Does a ROLLBACK Do?
    5. Summary
  15. Chapter 7: Investigating Redo
    1. Measuring Redo
    2. Can I Turn Off Redo Log Generation?
      1. Setting NOLOGGING in SQL
      2. Setting NOLOGGING on an Index
      3. NOLOGGING Wrap-up
    3. Why Can’t I Allocate a New Log?
    4. Block Cleanout
    5. Log Contention
    6. Temporary Tables and Redo/Undo
      1. Prior to 12c
      2. Starting with 12c
    7. Summary
  16. Chapter 8: Investigating Undo
    1. What Generates the Most and Least Undo?
    2. ORA-01555: Snapshot Too Old Error
      1. Undo Segments Are in Fact Too Small
      2. Delayed Block Cleanout
    3. Summary
  17. Index

Product information

  • Title: Oracle Database Transactions and Locking Revealed
  • Author(s): Thomas Kyte, Darl Kuhn
  • Release date: November 2014
  • Publisher(s): Apress
  • ISBN: 9781484207604