数据库锁

锁的最主要作用: 保证事务一致性,完整性,避免数据讹误;

数据库锁,基本上分为2大类

• DML 锁, 针对数据变更,如 delete / update / insert ;
• DDL 锁, 针对表结构变更,如字段的增加/变更/删除,表删除,索引建立等;
• 内存锁, 从磁盘读入内存中,或内存中数据版本的变更等;

Oracle 的锁

事务锁 TX (通常即数据行锁);

* 针对具体的数据行,锁住单行后,其他事务必须等待;
* commit / rollback / 会话被kill , 会自动释放;
* 会排队,表现为SQL一直在执行,没有返回;

表锁 TM

* 快速争抢,不排队;
* DDL 数据对象变更结束即释放,不需要commit/rollback;

Oracle 的锁实现

事务锁 TX (通常即数据行锁)

数据行在操作系统上的实现

操作系统文件到数据块示意图:

数据行在操作系统上的实现:
数据块示意图 , 每个数据块上都有ITL槽用于处理事务锁.

数据行在操作系统上的实现
数据块 ITL槽意图 , 每个数据块上可容纳255个itl槽,即可锁定255行.

1. 行锁产生的SQL场景:

update 表名 set … where …
delete from 表名 where …
merge into 表名 … where …

insert 不会锁历史数据,故不会产生行锁冲突

1. 锁的等待和冲突

行锁等待 / 冲突: 针对同一行数据,

1. 思考题, 如下,comp 在 00:00:05 的结果是?

答案为2

对象锁 TM (表锁)

表锁为对象锁,是保证SQL正常一致执行的基础.
有其他会话持有表的行锁,则新会话无法获得表锁;
表锁申请当时判断,不能获得即抛出失败,不会排队等待;

常见表锁场景:

alter table —更新数据字典,通常瞬间完成;
truncate table —更新数据字典和segment头,通常秒级完成;
drop table —更新数据字典和segment头,通常秒级完成;
如上动作,都是更新数据字典,获得锁后会很快执行完成.

create index on table
需要建完索引才释放,锁表时间较长, 建索引时间较短(特别是paralle时).

create index on table … online
在线建立索引,锁表时间非常短,但索引时间比较长(要二次处理新数据)

Oracle 减少锁冲突

减少行锁冲突

• 大批量数据更新,避免影响用户,在用户最少使用的时段操作;
• 快速提交,避免长时间空等待;
  更新完数据,及时提交,避免无效时间流逝;
• 符合业务需要的情况下,缩小事务;
  例如为10万行用户提升等级,各用户间是独立的,没有依赖关系就可以拆成100~1000 个为1批更新,减少等待其他会话释放锁或其他会话等待本会话释放锁;
• 合并更新,对同一行的多个update,合并成1个;
  例如,同一事务对某用户余额加10次,原本要10次update, 就可以将10的值合并,执行1次update,避免SQL多次执行的间隙时间,产生无效的等待;
• 高频变更数据的变通实现;
  对于非高实时性要求的数据,如是高频变更,可以采用变通的方法,
  1. 更新前,用select … from 表 for update no wait ,请求立即获得锁,不能立即获得即放弃;
  2. 如立即获得,即更新; 如不能立即获得,将变更值insert到中间表; 立即提交;
  3. 查询该行值时,将表中的值 + 中间表变更值即可;
  4. 另设守护进程,将中间表值合并update到 表, 并从中间表删除中间数据.

减少表锁冲突

• 尽量非业务高峰操作;
• alter 表, 增加字段时,如有 default 值, 符合业务需求时,使用not null ;

  1. alter table   表  add (字段名  类型  default  默认值)  ;   
       ---这种写法会将所有的表行update一遍.
  2. alter table   表  add (字段名  类型  default  默认值  not null )  ;
       ---仅更新数据字典,查询时判断旧数据,从数据字典中取得值.
  

• 建立索引时,使用paralle, 索引建立后改为noparallel.