Database Locking Concepts

Locks are mechanisms that coordinate concurrent acess to data by multiple processses or threads. In database systems, data represents a shared resource alongside traditional computing resources like CPU, memory, and I/O. Ensuring data consistency and validity during concurrent access is fundamental for all databases, while lock conflicts significantly impact concurrent performance.

MySQL Lock Classification by Granularity

Based on locking scope, MySQL implements three primary lock types:

• Global Lock: Locks all tables in the database
• Table-level Lock: Locks entire tables during operations
• Row-level Lock: Locks specific rows during data manipulation

Global Lock Implementation

Global locking restricts the entire database instance to read-only mode. When applied, it blocks Data Manipulation Language (DML) write operations, Data Definition Language (DDL) statements, and transaction commit operations involving updates.

Database Operation Types

• DML (Data Manipulation Language): Modifies table data through insert, update, and delete operations
• DDL (Data Definition Language): Alters database structure using CREATE, ALTER, DROP statements
• DQL (Data Query Language): Queries database table records
• DCL (Data Control Language): Manages database users and access permissions

Global Lock Use Cases

The primary application for global locks is performing logical database backups. By locking all tables, administrators can obtain consistent snapshots while ensuring data integrity throughout the backup process.

Backup Consistency Requirements

Without global locking, backup operations risk data inconsistency. Consider a scenario where inventory tables backup first, followed by order and log tables. During this interval, business operations might process new orders, reducing inventory and creating order records. When the system finally backs up order and log tables, the inventory data no longer matches the order and log information, creating inconsistency.

Global locking resolves this by blocking DDL and DML operations during backup. While the database remains in read-only mode, backup operations can safely execute data queries, ensuring consistent data throughout the process.

Global Lock Implementation Syntax

Apply global lock:

FLUSH TABLES WITH READ LOCK;

Execute data base backup:

mysqldump -h192.168.70.130 -uroot -p1234 inventory_db > backup_file.sql