2014年巴西世界杯_田径世界杯 - dutugo.com

排他锁 (Exclusive Lock / X Lock) ​📋 概述 ​排他锁（Exclusive Lock），又称写锁（Write Lock）或 X 锁，是一种独占式访问控制机制。当一个事务获得某资源的排他锁后，其他事务不能获得该资源的任何类型的锁（包括共享锁和排他锁）。

核心特点 ​✅ 优点:

数据一致性: 确保修改期间数据不被其他事务访问隔离性强: 完全隔离并发事务防止脏写: 避免多个事务同时修改同一数据简单直观: 易于理解和实现❌ 缺点:

并发度低: 阻塞所有其他事务的访问可能死锁: 多个事务互相等待对方释放锁性能影响: 长事务持有会严重影响并发资源浪费: 即使只修改一个字段也锁定整行适用场景 ​数据修改: UPDATE、DELETE 操作数据插入: INSERT 操作一致性更新: 读取后立即修改的场景临界区保护: 需要独占访问的代码段事务隔离: 确保事务串行执行🔧 工作原理 ​锁兼容性矩阵 ​排他锁是最严格的锁模式：

| 无锁 | S锁 | X锁

--------|-------|-------|------

X锁 | ✅ | ❌ | ❌123解读:

X 锁与任何锁都不兼容（除了无锁状态）一旦获得 X 锁，其他事务必须等待示例:

事务A: SELECT ... FOR UPDATE; -- 获得 X 锁

事务B: SELECT ... FOR UPDATE; -- ❌ 必须等待

事务C: SELECT ... LOCK IN SHARE MODE; -- ❌ 必须等待

事务D: UPDATE ...; -- ❌ 必须等待1234自动获取排他锁 ​DML 操作会自动获取排他锁：

操作锁行为说明INSERTX 锁插入新行时加排他锁UPDATEX 锁更新行时加排他锁DELETEX 锁删除行时加排他锁sql-- 这些操作都会自动加排他锁

UPDATE users SET name = 'test' WHERE id = 1; -- 自动加 X 锁

DELETE FROM users WHERE id = 1; -- 自动加 X 锁

INSERT INTO users VALUES (1, 'john'); -- 自动加 X 锁1234显式获取排他锁 ​sql-- MySQL

SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- PostgreSQL

SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- SQL Server

SELECT * FROM users WITH (UPDLOCK, ROWLOCK) WHERE id = 1;

-- Oracle

SELECT * FROM users WHERE id = 1 FOR UPDATE;1234567891011💻 各数据库实现 ​MySQL ​基本用法 ​sql-- 显式加排他锁

BEGIN;

SELECT * FROM users WHERE id = 1 FOR UPDATE;

UPDATE users SET balance = balance - 100 WHERE id = 1;

COMMIT;

-- 多行排他锁

SELECT * FROM users WHERE age > 18 FOR UPDATE;

-- 不等待立即返回

SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;

-- 跳过被锁定的行

SELECT * FROM users WHERE id IN (1,2,3) FOR UPDATE SKIP LOCKED;1234567891011121314锁的查看 ​sql-- 查看排他锁

SELECT

lock_id,

lock_trx_id,

lock_mode,

lock_type,

lock_table,

lock_index,

lock_data

FROM performance_schema.data_locks

WHERE lock_mode = 'X'; -- X 表示排他锁

-- 查看锁等待

SELECT * FROM performance_schema.data_lock_waits;

-- InnoDB 状态

SHOW ENGINE INNODB STATUS\G1234567891011121314151617参数配置 ​ini# my.cnf

# 排他锁等待超时时间（秒）

innodb_lock_wait_timeout = 50

# 死锁检测

innodb_deadlock_detect = ON123456PostgreSQL ​基本用法 ​sql-- 显式加排他锁

BEGIN;

SELECT * FROM users WHERE id = 1 FOR UPDATE;

UPDATE users SET balance = balance - 100 WHERE id = 1;

COMMIT;

-- 更强的锁模式

SELECT * FROM users WHERE id = 1 FOR NO KEY UPDATE; -- 不影响外键

SELECT * FROM users WHERE id = 1 FOR SHARE; -- 共享锁

SELECT * FROM users WHERE id = 1 FOR KEY SHARE; -- 最弱

-- 不等待

SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;

-- 跳过被锁定的行

SELECT * FROM users WHERE id IN (1,2,3) FOR UPDATE SKIP LOCKED;12345678910111213141516MVCC 与排他锁 ​PostgreSQL 使用 MVCC，但写操作仍需加排他锁：

sql-- 事务 A

BEGIN;

UPDATE users SET name = 'alice' WHERE id = 1;

-- 持有排他锁，但未提交

-- 事务 B

SELECT * FROM users WHERE id = 1;

-- ✅ 不被阻塞，读到旧版本（MVCC）

UPDATE users SET name = 'bob' WHERE id = 1;

-- ❌ 被阻塞，等待事务 A 提交或回滚1234567891011查看排他锁 ​sql-- 查看所有排他锁

SELECT

l.locktype,

l.relation::regclass AS table_name,

l.mode,

l.granted,

a.pid,

a.usename,

a.query

FROM pg_locks l

JOIN pg_stat_activity a ON l.pid = a.pid

WHERE l.mode IN ('ExclusiveLock', 'RowExclusiveLock')

ORDER BY l.relation;12345678910111213SQL Server ​基本用法 ​sql-- 使用排他锁提示

BEGIN TRANSACTION;

SELECT * FROM users WITH (UPDLOCK, ROWLOCK) WHERE id = 1;

UPDATE users SET balance = balance - 100 WHERE id = 1;

COMMIT TRANSACTION;

-- 强制使用排他锁

UPDATE users WITH (ROWLOCK) SET name = 'test' WHERE id = 1;

-- 表级排他锁

SELECT * FROM users WITH (TABLOCKX);1234567891011查看排他锁 ​sql-- 查看当前的排他锁

SELECT

tl.request_session_id,

OBJECT_NAME(p.object_id) AS table_name,

tl.resource_type,

tl.request_mode,

tl.request_status

FROM sys.dm_tran_locks tl

JOIN sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id

WHERE tl.request_mode IN ('X', 'IX'); -- X=排他锁, IX=意向排他锁12345678910Oracle ​基本用法 ​sql-- 显式加排他锁

BEGIN

SELECT * INTO user_record FROM users WHERE id = 1 FOR UPDATE;

UPDATE users SET balance = balance - 100 WHERE id = 1;

COMMIT;

END;

-- 不等待

SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;

-- 指定等待时间

SELECT * FROM users WHERE id = 1 FOR UPDATE WAIT 10;

-- 跳过已锁定的行

SELECT * FROM users WHERE id IN (1,2,3) FOR UPDATE SKIP LOCKED;123456789101112131415查看排他锁 ​sql-- 查看行级排他锁

SELECT

s.sid,

s.serial#,

s.username,

l.type,

l.lmode,

l.request,

o.object_name

FROM v$session s

JOIN v$lock l ON s.sid = l.sid

JOIN dba_objects o ON l.id1 = o.object_id

WHERE l.type = 'TX' -- TX = 事务锁（行级排他锁）

ORDER BY o.object_name;1234567891011121314📊 性能影响分析 ​并发度评估 ​场景并发度说明不同行的操作⭐⭐⭐⭐⭐完全并行同一行的操作⭐串行执行范围查询⭐⭐锁定多行全表更新⭐几乎串行性能测试示例 ​sql-- 测试场景：100个并发事务更新同一行

-- 使用排他锁

-- 100个事务串行执行，总耗时：~50秒

BEGIN;

SELECT * FROM users WHERE id = 1 FOR UPDATE;

UPDATE users SET balance = balance - 100 WHERE id = 1;

COMMIT;

-- 更新不同行

-- 100个事务并行执行，总耗时：~1秒

BEGIN;

SELECT * FROM users WHERE id = ? FOR UPDATE; -- 不同的 id

UPDATE users SET balance = balance - 100 WHERE id = ?;

COMMIT;123456789101112131415🎯 最佳实践 ​✅ 推荐做法 ​1. 缩短事务时间 ​sql-- ❌ 不推荐：长时间持有排他锁

BEGIN;

SELECT * FROM orders WHERE id = 1 FOR UPDATE;

-- 调用外部 API（耗时操作）

CALL process_order(1);

UPDATE orders SET status = 'processed' WHERE id = 1;

COMMIT;

-- ✅ 推荐：快速完成

BEGIN;

SELECT * FROM orders WHERE id = 1 FOR UPDATE;

UPDATE orders SET status = 'processed' WHERE id = 1;

COMMIT;

-- 之后再调用外部 API

CALL process_order(1);1234567891011121314152. 固定加锁顺序避免死锁 ​sql-- ❌ 可能导致死锁

-- 事务 A

SELECT * FROM users WHERE id = 1 FOR UPDATE;

SELECT * FROM users WHERE id = 2 FOR UPDATE;

-- 事务 B

SELECT * FROM users WHERE id = 2 FOR UPDATE;

SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- ✅ 固定顺序

SELECT * FROM users WHERE id IN (1,2) ORDER BY id FOR UPDATE;12345678910113. 批量操作优化 ​sql-- ❌ 逐行处理

FOR i IN 1..1000 LOOP

SELECT * FROM users WHERE id = i FOR UPDATE;

UPDATE users SET ... WHERE id = i;

END LOOP;

-- ✅ 批量处理

SELECT * FROM users WHERE id BETWEEN 1 AND 1000 FOR UPDATE;

UPDATE users SET ... WHERE id BETWEEN 1 AND 1000;1234567894. 设置合理的超时 ​sql-- MySQL

SET innodb_lock_wait_timeout = 10;

-- PostgreSQL

SET lock_timeout = '5s';

-- SQL Server

SET LOCK_TIMEOUT 5000;12345678❌ 避免陷阱 ​1. 避免忽略死锁异常 ​python# ❌ 不处理死锁

cursor.execute("SELECT * FROM users WHERE id = 1 FOR UPDATE")

# ✅ 重试机制

import time

max_retries = 3

for attempt in range(max_retries):

try:

cursor.execute("SELECT * FROM users WHERE id = 1 FOR UPDATE")

break

except DeadlockException:

if attempt == max_retries - 1:

raise

time.sleep(0.1 * (2 ** attempt)) # 指数退避1234567891011121314152. 避免在循环中执行带锁查询 ​sql-- ❌ 性能差

FOR i IN 1..1000 LOOP

SELECT * FROM users WHERE id = i FOR UPDATE;

UPDATE users SET ... WHERE id = i;

END LOOP;

-- ✅ 批量处理

SELECT * FROM users WHERE id BETWEEN 1 AND 1000 FOR UPDATE;

UPDATE users SET ... WHERE id BETWEEN 1 AND 1000;1234567893. 避免大事务持有排他锁 ​sql-- ❌ 不推荐

BEGIN;

SELECT * FROM users WHERE id = 1 FOR UPDATE;

INSERT INTO logs ...; -- 大量日志插入

UPDATE users SET ... WHERE id = 1;

COMMIT;

-- ✅ 拆分事务

BEGIN;

SELECT * FROM users WHERE id = 1 FOR UPDATE;

UPDATE users SET ... WHERE id = 1;

COMMIT;

BEGIN;

INSERT INTO logs ...;

COMMIT;12345678910111213141516🔍 监控与诊断 ​MySQL 监控 ​sql-- 查看排他锁

SELECT

lock_id,

lock_trx_id,

lock_mode,

lock_type,

lock_table,

lock_data

FROM performance_schema.data_locks

WHERE lock_mode = 'X';

-- 查看锁等待

SELECT

requesting_thread_id,

blocking_thread_id,

wait_age

FROM performance_schema.data_lock_waits;

-- 查看死锁

SHOW ENGINE INNODB STATUS\G1234567891011121314151617181920PostgreSQL 监控 ​sql-- 查看排他锁

SELECT

l.relation::regclass AS table_name,

l.mode,

l.granted,

a.pid,

a.query

FROM pg_locks l

JOIN pg_stat_activity a ON l.pid = a.pid

WHERE l.mode IN ('ExclusiveLock', 'RowExclusiveLock');

-- 查看锁等待

SELECT * FROM pg_stat_activity

WHERE wait_event_type = 'Lock';1234567891011121314SQL Server 监控 ​sql-- 查看排他锁

SELECT

request_session_id,

OBJECT_NAME(resource_associated_entity_id) AS table_name,

request_mode,

request_status

FROM sys.dm_tran_locks

WHERE request_mode IN ('X', 'IX');

-- 查看阻塞

EXEC sp_who2;1234567891011🚨 常见问题 ​Q1: 排他锁和共享锁的区别？ ​答:

特性排他锁 (X)共享锁 (S)兼容性与任何锁都不兼容与其他 S 锁兼容用途写操作读操作并发度低高死锁风险有无（纯 S 锁）Q2: 如何避免排他锁导致的性能问题？ ​答:

缩短事务时间: 快速提交减少锁粒度: 使用行锁而非表锁批量操作: 减少锁获取次数合理索引: 精确定位需要锁定的行读写分离: 从库承担读负载Q3: 排他锁会导致死锁吗？如何预防？ ​答: 会。预防措施：

固定加锁顺序: 总是按相同顺序获取锁缩短事务时间: 快速提交设置超时: 避免无限等待重试机制: 捕获死锁异常并重试📚 相关资源 ​内部链接 ​共享锁 - 读锁模式意向锁 - 层级协调锁行级锁 - 锁粒度悲观锁 - 使用策略外部资源 ​MySQL Exclusive LocksPostgreSQL Explicit LockingSQL Server Locking最后更新: 2026-04-12维护状态: ✅ 完整