MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Introduction
	Why Are Locks Needed?
	Lock Levels
	Locks and Transactions
	Examples
		Prerequisites for the concurrency_book.generate Module
		Installing the concurrency_book.generate Module
		Getting Information
		Loading Test Data
		Executing a Workload
	Test Data: The world Schema
		Schema
		Installation
	Test Data: The sakila Schema
		Schema
		Installation
	Test Data: The employees Schema
		Schema
		Installation
	Summary
Chapter 2: Monitoring Locks and Mutexes
	The Performance Schema
		Metadata and Table Locks
		Data Locks
		Synchronization Waits
		Statement and Error Tables
	The sys Schema
	Status Counters and InnoDB Metrics
		Querying the Data
		Configuring the InnoDB Metrics
	InnoDB Lock Monitor and Deadlock Logging
	InnoDB Mutexes and Semaphores
	Summary
Chapter 3: Monitoring InnoDB Transactions
	Information Schema INNODB_TRX
	InnoDB Monitor
	INNODB_METRICS and sys.metrics
	Summary
Chapter 4: Transactions in the Performance Schema
	Transaction Events and Their Statements
	Transaction Summary Tables
	Summary
Chapter 5: Lock Access Levels
	Shared Locks
	Exclusive Locks
	Intention Locks
	Lock Compatibility
	Summary
Chapter 6: High-Level Lock Types
	User-Level Locks
	Flush Locks
	Metadata Locks
	Explicit Table Locks
	Implicit Table Locks
	Backup Locks
	Log Locks
	Summary
Chapter 7: InnoDB Locks
	Record Locks and Next-Key Locks
	Gap Locks
	Predicate and Page Locks
	Insert Intention Locks
	Auto-Increment Locks
	Mutexes and RW-Lock Semaphores
	Summary
Chapter 8: Working with Lock Conflicts
	Contention-Aware Transaction Scheduling (CATS)
	InnoDB Data Lock Compatibility
	Metadata and Backup Lock Wait Timeouts
	InnoDB Lock Wait Timeouts
	Deadlocks
	InnoDB Mutex and Semaphore Waits
	Summary
Chapter 9: Reducing Locking Issues
	Transaction Size and Age
	Indexes
	Record Access order
	Transaction Isolation Levels
	Configuration
		Resource Partitioning
		Disabling the InnoDB Adaptive Hash Index
		Reducing Priority of Metadata Write Locks
	Preemptive Locking
	Summary
Chapter 10: Indexes and Foreign Keys
	Indexes
		Primary vs. Secondary Indexes
		Ascending vs. Descending Indexes
		Unique Indexes
	Foreign Keys
		DML Statement
		DDL Statement
	Summary
Chapter 11: Transactions
	Transactions and ACID
		Atomicity
		Consistency
		Isolation
		Durability
	Impact of Transactions
		Locks
		Undo Logs
	Group Commit
	Summary
Chapter 12: Transaction Isolation Levels
	Serializable
	Repeatable Read
	Read Committed
	Read Uncommitted
	Summary
Chapter 13: Case Study: Flush Locks
	The Symptoms
	The Cause
	The Setup
	The Investigation
	The Solution
	The Prevention
	Summary
Chapter 14: Case Study: Metadata and Schema Locks
	The Symptoms
	The Cause
	The Setup
	The Investigation
	The Solution
	The Prevention
	Summary
Chapter 15: Case Study: Record-Level Locks
	The Symptoms
	The Cause
	The Setup
	The Investigation
	The Solution
	The Prevention
	Summary
Chapter 16: Case Study: Deadlocks
	The Symptoms
	The Cause
	The Setup
	The Investigation
	The Solution
	The Prevention
	Summary
Chapter 17: Case Study: Foreign Keys
	The Setup
	The Discussion
		Errors and High-Level Monitoring
		Lock Metrics
		Metadata Lock Contention
		InnoDB Lock Contention
	The Solution and Prevention
	Summary
Chapter 18: Case Study: Semaphores
	The Symptoms
	The Cause
	The Setup
	The Investigation
		The InnoDB RW-Lock Metrics
		InnoDB Monitor and Mutex Monitor
		Determining the Workload
	The Solution and Prevention
		Disabling the Adaptive Hash Index
		Increase the Number of Hash Index Parts
		Other Solutions
	Summary
Appendix A:  References
	Tables and Views
		Lock Information
		Metadata Object Types
		Metadata Lock Types
		Transaction Information
		Statement Information
		Wait Information
		Table I/O Information
		File I/O Information
		Error Information
		Status Variables and InnoDB Metrics
	InnoDB Monitor Sections
Appendix B: MySQL Shell Module
	Prerequisites
	Installation
	The help() and show() Methods
	Loading Test Data
	Executing a Workload
	Module Structure
		Library Files
			innodb_buffer_pool.py
			innodb_monitor.py
			innodb_mutex.py
			load.py
			log.py
			metrics.py
			query.py
			util.py
			workloads.py
		Workloads Directory
	Defining Workloads
		Global Keys
		Queries and Completions
		Investigations
	Summary
Index