数据库锁优化

锁的基础概念

1. 锁的类型与作用

共享锁(S锁)

• 允许多个事务同时读取数据
• 阻止其他事务获取排他锁
• 适用于读取操作

排他锁(X锁)

• 独占访问,阻止其他事务读写
• 用于数据修改操作
• 确保数据一致性

意向锁(IS/IX锁)

• 表级锁,用于标记意图
• IS:表中某行有共享锁
• IX:表中某行有排他锁

2. 锁的粒度级别

SQL Server

-- 表级锁
SELECT * FROM users WITH (TABLOCKX);

-- 行级锁
SELECT * FROM users WITH (ROWLOCK)
WHERE id = 1;

-- 页级锁
SELECT * FROM users WITH (PAGLOCK)
WHERE dept_id = 100;

MySQL

-- 表级锁示例
LOCK TABLES users READ;
SELECT * FROM users;
UNLOCK TABLES;

-- 行级锁示例 
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 间隙锁示例
SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE;

达梦

-- 表级锁
LOCK TABLE users IN EXCLUSIVE MODE;

-- 行级锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

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

锁的使用策略

1. 乐观锁与悲观锁

SQL Server

-- 悲观锁
SELECT * FROM orders WITH (XLOCK)
WHERE id = 1;

-- 乐观锁
UPDATE orders 
SET status = 'completed',
    version = version + 1
WHERE id = 1 
AND version = @old_version;

-- 使用时间戳实现乐观锁
UPDATE orders 
SET status = 'completed'
WHERE id = 1 
AND timestamp = @old_timestamp;

MySQL

-- 悲观锁
SELECT * FROM orders 
WHERE id = 1 FOR UPDATE;

-- 乐观锁(版本号)
UPDATE orders 
SET status = 'completed',
    version = version + 1
WHERE id = 1 
AND version = @old_version;

-- 使用CAS实现乐观锁
UPDATE orders 
SET amount = @new_amount
WHERE id = 1 
AND amount = @old_amount;

达梦

-- 悲观锁
SELECT * FROM orders 
WHERE id = 1 FOR UPDATE;

-- 乐观锁
UPDATE orders 
SET status = 'completed',
    version = version + 1
WHERE id = 1 
AND version = @old_version;

-- 使用SCN实现乐观锁
UPDATE orders 
SET status = 'completed'
WHERE id = 1 
AND ORA_ROWSCN = @old_scn;

2. 锁超时与等待处理

SQL Server

-- 设置锁超时(毫秒)
SET LOCK_TIMEOUT 10000;

-- 使用READPAST跳过锁定行
SELECT * FROM orders WITH (READPAST)
WHERE status = 'pending';

-- 使用NOWAIT选项
SELECT * FROM orders WITH (UPDLOCK, NOWAIT)
WHERE id = 1;

MySQL

-- 设置锁等待超时(秒)
SET innodb_lock_wait_timeout = 10;

-- 使用NOWAIT
SELECT * FROM orders 
WHERE id = 1 FOR UPDATE NOWAIT;

-- 使用SKIP LOCKED
SELECT * FROM orders 
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED;

达梦

-- 设置锁等待超时
SET LOCK_TIMEOUT 10000;

-- 使用NOWAIT
SELECT * FROM orders 
WHERE id = 1 FOR UPDATE NOWAIT;

-- 使用WAIT选项
SELECT * FROM orders 
WHERE id = 1 FOR UPDATE WAIT 5;

死锁处理

1. 死锁检测与预防

SQL Server

-- 查看死锁信息
SELECT * FROM sys.dm_tran_locks;

-- 启用追踪标记以记录死锁
DBCC TRACEON(1222, -1);

-- 查看死锁图
SELECT * FROM sys.dm_tran_locks l
JOIN sys.dm_os_waiting_tasks w
ON l.lock_owner_address = w.resource_address;

MySQL

-- 开启死锁检测
SET innodb_deadlock_detect = ON;

-- 查看死锁信息
SHOW ENGINE INNODB STATUS;

-- 设置死锁自动回滚等待时间
SET innodb_lock_wait_timeout = 50;

达梦

-- 查看当前锁等待
SELECT * FROM V$LOCK 
WHERE block > 0;

-- 查看死锁详情
SELECT * FROM V$DEADLOCK;

-- 终止死锁会话
ALTER SYSTEM KILL SESSION 'sid,serial#';

2. 死锁避免最佳实践

2.1 事务设计原则

• 保持事务简短且快速
• 按固定顺序访问表和行
• 避免事务中的用户交互
• 在事务中减少锁的持有时间

2.2 锁升级控制

• 适当设置锁升级阈值
• 避免过度使用表锁
• 合理使用索引减少锁范围

2.3 并发访问优化

• 使用合适的隔离级别
• 实现错误重试机制
• 合理设置锁超时时间

锁监控与诊断

1. 锁等待分析

SQL Server

-- 查看锁等待信息
SELECT * FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'LCK%';

-- 查看阻塞情况
SELECT * FROM sys.dm_exec_requests
WHERE blocking_session_id IS NOT NULL;

-- 查看详细锁信息
SELECT * FROM sys.dm_tran_locks
WHERE request_status = 'WAIT';

MySQL

-- 查看当前锁等待
SELECT * FROM 
performance_schema.events_waits_current
WHERE EVENT_NAME LIKE '%lock%';

-- 查看锁等待历史
SELECT * FROM 
performance_schema.events_waits_history
WHERE EVENT_NAME LIKE '%lock%';

-- 查看锁等待统计
SELECT * FROM sys.innodb_lock_waits;

达梦

-- 查看当前锁等待
SELECT * FROM V$SESSION_WAIT
WHERE wait_class = 'Application';

-- 查看锁资源
SELECT * FROM V$LOCKED_OBJECT;

-- 查看阻塞会话
SELECT * FROM V$SESSION
WHERE blocking_session IS NOT NULL;

2. 性能优化建议

2.1 锁优化策略

• 使用合适的锁粒度

  SQL Server

  -- 使用行级锁
  SELECT * FROM users WITH (ROWLOCK) 
  WHERE id = 1;
  
  -- 使用表提示控制锁粒度
  SELECT * FROM users WITH (PAGLOCK)
  WHERE age > 20;

  MySQL

  -- 优先使用行级锁而不是表级锁
  SELECT * FROM users WHERE id = 1 FOR UPDATE;
  
  -- 使用间隙锁防止幻读
  SELECT * FROM users WHERE age > 20 FOR UPDATE;

  达梦

  -- 行级锁示例
  SELECT * FROM users WHERE id = 1 FOR UPDATE;
  
  -- 使用NOWAIT选项避免等待
  SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;

• 优化锁等待时间

  SQL Server

  -- 设置锁超时(毫秒)
  SET LOCK_TIMEOUT 5000;
  
  -- 使用READPAST跳过锁定的行
  SELECT * FROM users WITH (READPAST);

  MySQL

  -- 设置锁等待超时(秒)
  SET innodb_lock_wait_timeout = 50;
  
  -- 使用NOWAIT避免等待
  SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;

  达梦

  -- 设置会话锁等待时间
  SET SESSION LOCK_TIMEOUT = 5000;
  
  -- 使用SKIP LOCKED跳过锁定行
  SELECT * FROM users FOR UPDATE SKIP LOCKED;

2.2 应用层优化

• 合理设计并发访问模式
  SQL Server

  -- 使用ROWVERSION实现乐观并发
  UPDATE users 
  SET name = 'new_name'
  WHERE id = 1 AND timestamp = @oldTimestamp;
  
  -- 使用READ COMMITTED SNAPSHOT减少阻塞
  ALTER DATABASE MyDB
  SET READ_COMMITTED_SNAPSHOT ON;

  MySQL

  -- 乐观锁实现
  UPDATE users 
  SET name = 'new_name', version = version + 1
  WHERE id = 1 AND version = 1;
  
  -- 批量更新减少锁竞争
  UPDATE users 
  SET status = 'active'
  WHERE id IN (1,2,3,4,5);

  达梦

  -- 使用SCN实现乐观锁
  UPDATE users 
  SET name = 'new_name'
  WHERE id = 1 AND ORA_ROWSCN = @oldSCN;
  
  -- 批量处理优化
  FORALL i IN 1..5
  UPDATE users SET status = 'active'
  WHERE id = ids(i);

2.3 监控与告警

SQL Server

-- 设置锁超时告警
sp_configure 'lock timeout', 10000;
RECONFIGURE;

-- 查看锁等待
SELECT * FROM sys.dm_os_waiting_tasks
WHERE wait_duration_ms > 10000
AND wait_type LIKE 'LCK%';

-- 监控死锁
SELECT * FROM sys.event_log
WHERE event_type = 'deadlock';

-- 分析锁争用
SELECT * FROM sys.dm_db_index_operational_stats
(DB_ID(), NULL, NULL, NULL);

MySQL

-- 设置锁等待阈值告警
SET GLOBAL innodb_lock_wait_timeout = 10;

-- 查看当前锁等待情况
SELECT * FROM performance_schema.events_waits_current 
WHERE EVENT_NAME LIKE '%lock%'
AND TIMER_WAIT > 10000000000;

-- 监控死锁发生
SHOW ENGINE INNODB STATUS;

-- 分析锁争用
SELECT * FROM sys.innodb_lock_waits;

达梦

-- 设置会话锁等待超时
ALTER SESSION SET LOCK_TIMEOUT = 10;

-- 查看当前锁等待
SELECT * FROM V$SESSION_WAIT 
WHERE event LIKE '%lock%'
AND seconds_in_wait > 10;

-- 监控死锁
SELECT * FROM V$LOCK 
WHERE block > 0;

-- 分析锁争用
SELECT * FROM V$LOCK_ACTIVITY;