数据库事务处理优化：悲观锁vs乐观锁策略效果分析
最近在处理一个高并发的订单系统时，遇到了严重的锁竞争问题。通过对比测试，发现悲观锁和乐观锁在不同场景下的表现差异显著。
测试环境
- MySQL 8.0 + InnoDB引擎
- 100个并发线程同时访问
- 每个线程执行1000次更新操作
- 测试表结构：
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
amount DECIMAL(10,2),
version INT DEFAULT 0,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
悲观锁测试代码（Python）：
import threading
import time
import mysql.connector
def pessimistic_lock_test():
conn = mysql.connector.connect(...)
cursor = conn.cursor()
start_time = time.time()
for i in range(1000):
cursor.execute("SELECT amount FROM orders WHERE id = %s FOR UPDATE", (i,))
current_amount = cursor.fetchone()[0]
new_amount = current_amount + 10
cursor.execute("UPDATE orders SET amount = %s WHERE id = %s", (new_amount, i))
conn.commit()
return time.time() - start_time
乐观锁测试代码：
import threading
import time
import mysql.connector
def optimistic_lock_test():
conn = mysql.connector.connect(...)
cursor = conn.cursor()
start_time = time.time()
for i in range(1000):
cursor.execute("SELECT amount, version FROM orders WHERE id = %s", (i,))
current_amount, version = cursor.fetchone()
new_amount = current_amount + 10
affected_rows = cursor.execute(
"UPDATE orders SET amount = %s, version = version + 1 WHERE id = %s AND version = %s",
(new_amount, i, version)
)
if not affected_rows:
# 重试逻辑
time.sleep(0.01)
cursor.execute("UPDATE orders SET amount = %s, version = version + 1 WHERE id = %s AND version = %s", (new_amount, i, version))
conn.commit()
return time.time() - start_time
测试结果（平均时间）：
- 悲观锁：平均耗时 24.8秒
- 乐观锁：平均耗时 18.3秒
- 性能提升：26%（在高并发场景下）
结论
悲观锁适用于更新冲突频繁的场景，但会带来明显的性能瓶颈；乐观锁在低冲突情况下表现更佳，且可以有效减少锁等待时间。建议根据实际业务场景选择合适的锁策略。
讨论