锁表

📄字数 1.7K

👁️阅读量 加载中...

描述

LOCK命令用于显式地对一张或多张表加锁，以控制多个事务间对表的并发访问，从而确保数据一致性与事务隔离。在默认的事务机制（如自动加锁）无法满足更严格控制需求的场景中，用户可以手动使用LOCK来避免死锁等问题。

LOCK语法格式

参数说明

• table_name：加锁的表名称。

• opt_lock：可选锁模式，用于指定表锁的级别。

  锁模式	含义说明	示例
  SHARE MODE	共享锁（S锁），允许多个事务同时读取同一资源，但不允许写（共享只读）	LOCK TABLE a IN SHARE MODE;
  EXCLUSIVE MODE	排它锁（X锁），只能由一个事务持有，阻止其他事务对该资源的任何操作（读或写）	LOCK TABLE a IN EXCLUSIVE MODE;
  ROW SHARE MODE	意向共享锁（IS锁），该锁通常加在更高层级，表示将在其子资源上加S锁。允许多个意向共享锁共存，不阻塞彼此	LOCK TABLE a IN ROW SHARE MODE;
  ROW EXCLUSIVE MODE	意向排它锁（IX锁），表示将在子资源上加X锁。允许多个事务并发持有锁，但要求这些事务操作的子集不同。通常用于并发DML操作	LOCK TABLE a IN ROW EXCLUSIVE MODE;
  -	锁模式为空时，默认X锁	LOCK TABLE a;

• opt_wait：可选等待策略，目标表被其他事务锁定时的行为。

  等待策略	说明	示例
  NOWAIT	如果不能立即获得锁，则立即报错，不等待	LOCK TABLE a IN SHARE MODE NOWAIT;
  WAIT	等待直到锁可用（同默认行为）	LOCK TABLE a WAIT;
  WAIT ICONST	最多等待指定毫秒数（ICONST为常量，取值范围：大于等于10ms）	LOCK TABLE a IN EXCLUSIVE MODE WAIT 5;
  -	为空时，默认行为为无限等待，直到锁可用	LOCK TABLE a;

使用要求及注意事项

• 事务内使用：LOCK通常应在事务块BEGIN ... COMMIT中使用，锁直到事务提交或回滚才释放
• 锁粒度为表级：锁住的是整张表，非单行
• 避免长时间持锁：长事务持锁时间越长，越可能导致其他事务阻塞甚至死锁，应尽快完成操作并提交事务
• 锁模式应谨慎选择：过强的锁可能会阻止其他事务访问。其中X锁与任何锁不兼容，S锁与S/IS兼容，但与写相关锁（X、IX、SIX）冲突，IS与IX之间兼容。锁兼容性表如下图所示（行是已有锁，列是尝试加锁）：

	S	X	IS	IX
S	true	false	true	false
X	false	false	false	false
IS	true	false	true	true
IX	false	false	true	true

示例

• 锁表并更新数据

  -- 会话1：创建表并插入数据
  SQL> CREATE TABLE accounts (
           id int,
           name varchar,
           balance NUMERIC
       );
  
  SQL> INSERT INTO accounts VALUES
       (1, 'Alice', 1000),
       (2, 'Bob', 800);
  
  -- 关闭自动提交
  SQL> set auto_commit to off;
  
  -- 显式锁表，更新数据（此时不提交事务，保持锁状态）
  SQL> BEGIN;
  SQL> LOCK TABLE accounts IN EXCLUSIVE MODE;
  SQL> UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
  
  -- 另启会话2：尝试读表，此时将会卡住（由于会话1占用表锁，因此被阻塞）
  SQL> select * from ACCOUNTS;
  
  -- 会话1提交事务后，会话2才会继续执行
  SQL> COMMIT;

使用场景

• 批处理任务前锁表，用于防止其他事务在数据修改过程中读取或写入，确保数据一致性
• 避免死锁。当多个事务需依次访问多张表时，显式锁表能确保锁顺序，降低死锁风险
• 结构变更保护，对于执行ALTER TABLE、DROP TABLE等DDL前确保无其他访问操作