MySQL中行级锁和表级锁的区别小结

目录

• 一、行级锁 vs 表级锁
• 1. 行级锁（Row-level Locks）
• 2. 表级锁（Table-level Locks）
• 二、InnoDB 中的行级锁
• 1. 共享锁（S-lock）
• 2. 排它锁（X-lock）
• 三、MyISAM 中的表级锁
• 1. 读锁（共享锁）
• 2. 写锁（排它锁）
• 四、使用示例
• 1. 行级锁示例
• 2. 表级锁示例
• 五、意向锁
• 六、总结

mysqlphp 中的行级锁和表级锁是两种不同的锁机制，它们在并发控制和锁粒度方面有显著的区别。了解这两种锁的区别及其使用场景，有助于优化数据库性能并确保数据一致性。以下是对行级锁和表级锁的详细解析，并结合代码示例来帮助理解。

一、行级锁 vs 表级锁

1. 行级锁（Row-level Locks）

• 粒度：行级锁锁定的是表中的单个行。
• 并发性：行级锁具有较高的并发性能，因为不同事务可以并发地修改不同的行。
• 开销：管理行级锁的开销较高，因为需要跟踪每一行的锁状态。
• 使用场景：适用于需要高并发读写操作的场景。

2. 表级锁（Table-level Locks）

• 粒度：表级锁锁定的是整个表。
• 并发性：表级锁的并发性能较低，因为一个事务锁定整个表后，其他事务不能同时对该表进行任何读写操作。
• 开销：管理表级锁的开销较低，相对于行级锁而言更简单。
• 使用场景：适用于读多写少的场景，如报表查询等。

二、InnoDB 中的行级锁

InnoDB 存储引擎支持行级锁，这使得它适用于高并发的事务处理。

1. 共享锁（S-lock）

多个事务可以同时读取同一行，但不能修改。

ST编程ART TRANSACTION;

-- 获取共享锁
SELECT * FROM employees WHERE id = 1 LOCK IN SHARE MODE;

-- 完成事务
COMMIT;

2. 排它锁（X-lock）

一个事务获取排它锁后，其他事务不能读取或修改该行。

START TRANSACTION;

-- 获取排它锁
SELECT * FROM employees WHERE id = 1 FOR UPDATE;

-- 完成事务
COMMIT;

三、MyISAM 中的表级锁

MyISAM 存储引擎只支持表级锁。

1. 读锁（共享锁）

多个客户端可以同时读取表，但不能写入。

LOCK TABLES employees READ;

-- 在锁定的表上执行读操作
SELECT * FROM employees;

-- 释放锁
UNLOCK TABLES;

2. 写锁（排它锁）

一个客户端获取写锁后，其他客户端不能读取或写入该表。

LOCK TABLES employees WRITE;

-- 在锁定的表上执行写操作
INSERT INTO employees (name, department_id) VALUES ('Alice', 1);

-- 释放锁
UNLOCK TABLES;

四、使用示例

1. 行级锁示例

假设有一个 employees 表，我们使用 InnoDB 存储引擎，并展示行级锁的使用。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO employees (name, department_id) VALUES ('Alice', 1), ('Bob', 2);

两会话并发更新不同的行：

• 会话 1

START TRANSACTION;
UPDATE employees SET name = 'Charlie' WHERE id = 1;
-- 保持android事务未提交

• 会话 2

START TRANSACTION;
UPDATE employees SET name = 'Dave' WHERE id = 2;
-- 保持事务未提交

这种情况下，两会话可以并发执行，因为它们修改的是不同的行。

2. 表级锁示例

假设有一个 employees 表，我们使用 MyISAM 存储引擎，并展示表级锁的使用。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM;

INSERT INTO employees (name, department_id) VALUES ('Alice', 1), ('Bob', 2);

两会话并发更新表：

• 会话 1

LOCK TABLES employees WRITE;
UPDATE employees SET name = 'Charlie' WHERE id = 1;
-- 保持锁未释放

• 会话 2

-- 会被阻塞，直到会话 1 释放锁
LOCK TABLES employees WRITE;
UPDATE employees SET name = 'Dave' WHERE id = android2;

这种情况下，会话 2 会被阻塞，直到会话 1 释放锁，因为 MyISAM 使用的是表级锁。

五、意向锁

InnoDB 还支持意向锁（Intent Locks），这是表级锁和行级锁之间的一种协调机制。意向锁分为意向共享锁（IS）和意向排它锁（IX）。

• 意向共享锁（IS-lock）：事务打算在某些行上加共享锁时，先在表级加意向共享锁。
• 意向排它锁（IX-lock）：事务打算在某些行上加排它锁时，先在表级加意向排它锁。

意向锁是由 InnoDB 自动管理的，不需要显式加锁。

六、总结

行python级锁和表级锁是 MySQL 中两种重要的锁机制，它们在锁粒度和并发控制方面有显著的区别：

• 行级锁：粒度较小，适合高并发读写操作，但管理开销较高。InnoDB 存储引擎支持行级锁。
• 表级锁：粒度较大，适合读多写少的场景，管理开销较低。MyISAM 存储引擎使用表级锁。

通过合理选择和使用锁机制，可以有效提高数据库的并发性能和数据一致性。理解和掌握这些锁机制的细节，有助于在设计和优化数据库时做出更好的决策。

到此这篇关于MySQL中行级锁和表级锁的区别小结的文章就介绍到这了,更多相关MySQL 行级锁和表级锁内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！