首页 > 代码库 > 06 Locking and Latching
06 Locking and Latching
本章提要
---------------------------------------------------------------
6,7,8,9,10,11 这 6 章要细看, 从本章开始
how Oracle locks both data and shared data structures(比如在SGA中的)
锁的机制 和 如何实现
---------------------------------------------------------------
1. 锁的概念和 oracle锁的特点
锁 用于管理对共享资源的并发访问.
oracle 中的锁
1) 事务是数据库的核心, 是"好东西"
2) 尽量保持事务, 在必要时才提交,(对比sql server)
3) 只要需要, 尽可能时间长的保持锁, 不要担心浪费资源
4) 行级锁没有开销, 锁定 100000 行 与 1 行 所占用的资源是一样的
5) 不要以为升级为 table 级锁是对系统有好处, 表锁是为了方便, 比如有时你做批处理任务, 这时候想
修改table, 可以对这个table加表锁, 防止其他回话锁定表中的行.
6) 可以同时得到并发性和一致性.
2. 锁定相关 问题
update lost
1) session1 中的一个事务获取一行数据, 并放入本地内存(比如应用程度的变量中), 并显示该行内容给user1
2) session2 中另一个书屋也获取了该行, 并存入本地内存, 并显示该行内容给user2
3) user1修改了这行数据(注意是修改了除了主键以外的所有值, 并提交了, 此时, user2看到的还是"旧数据"
4) user2也修改了这行数据, 根据本地内存中的内容, 这样就将"部分旧数据"又恢复到了以前状态.
这个过程称为"丢失更新", 因为第3步所做的所有修改丢会丢失. 问题的原因为以下两方面:
1) 编写应用程序没有考虑到锁, 只是简单地 select, update 命令.
2) 这个流程的根本问题是, 应该只修改想要的数据, 而不是一整行全部数据(主键除外)
3. 锁的策略(悲观锁定, 乐观锁定)
悲观锁:
用户在修改值之前, 就要上锁, 直到提交.用户一旦有意对他选择的某个特定行执行update, 就上行锁
悲观锁有一个限制, 就是必须保持与数据库的长连接, 至少在事务的整个生命周期里, 要一直保持着对数据库的
连接, 但是, 实际上现在的应用程序, 一般都不是长连接. 而是请求, 然后就断开了, 等你干好活以后, 再连接
锁是不能跨连接的
比如 select for update nowait
-- user1, execute 1 -----------------select * from DEPTwhere deptno = 10;-- user2, execute 2 ------------------- user2 第一次查询select * from DEPTwhere deptno = 10; -- get result, deptno = 10, dname=‘bbb‘, loc=‘aaa‘variable :deptno number;variable :dname varchar2(20);variable :loc varchar2(20);-- 模拟应用程序的本地变量存储exec :deptno := 10; :dname := ‘bbb‘; :loc := ‘aaa‘;-- user1, execute 3 -----------------update deptset dname = ‘ccc‘where deptno = 10;commit;-- 此时, user2 本地变量中保存的数据已经过时-- user2, execute 4 ------------------- user2 第2次查询, 加锁, 准备修改select deptno, dname, loc from dept where deptno = :deptno and decode(dname, :dname, 1) = 1 and decode(loc, :loc, 1) = 1 for update nowait;-- result, 0 rows
上面代码, 模拟解决办法
1) 如果底层数据没有改变, 那么会再次得到这行, 并加了行锁
2) 如果有另一个用户正在更新这一行, 我们就会得到 resource busy错误, 必须等待另一个用户的执行
3) 这一点最关键, 即当别的用户修改并提交了数据发生在你第一次获取该行和第2次想获取该行之间, 那么
通过以上的例子, 我们会得到 0 行结果再第2次查询的时候, 因为这个时候你内存中获取的第一次的数据已经
是旧数据了, 所以必须重新查询得到新数据, 再申请加行锁, 实现的办法是通过查询条件, 对整行的数据都
进行了比较, 如果有不一样的, 那么查询条件就为 false, 所以查询返回的结果是 0 行.
上边代码有个小技巧是 为什么不直接用 and ename = :ename, 因为 ename 有可以出现 null, 而是用decode
可以有效的避免这种判断, 是用decode 等价于
(column = :bind_variable or (column is null and :bind_variable is null))
第2次获取行之后, 一旦获取成功, 就可以修改该行并提交, 非常安全.
乐观锁:
把所有锁都延迟到即将执行更新之前才做. 换句话说, 我们修改信息而不上锁, 我们很乐观, 认为数据不会被
其他用户修改, 如果所有应用(会话)都使用乐观锁定, 那么直接使用update没什么问题, 因为执行更新并提交
时, 行会被锁定很短的事件, 但是, 如果某些应用使用了悲观锁, 它会再一段相对较长的事件内持有行上的锁,
或如果有应用(如批量处理进程) 可能锁定行很长时间, 那么可能会考虑使用 select for update nowait,
以此来验证行是否未被修改, 并在即将update之前锁定来避免被另一个会话阻塞.
实现 乐观锁
1) 增加 1 个特殊列, 当该行被修改时, 必须修改这列的值, 通过这列来判断当前本地内存中的行是否为最
新行, 可以被修改.
create table dept( deptno number(2), dname varchar2(14), loc varchar2(13), last_mod timestamp with time zone default systimestamp not null, constraint dept_pk primary key(deptno))insert into dept(deptno, dname, loc)select deptno, dname, locfrom scott.dept;commit;-- timestamp 是oracle的最高精度, 两个用户在这样精度下的同一时刻修改数据,-- 概率太小了.-- 维护这个值可以使用 trigger/procedure, 不过我们最好是让用户维护这个值,-- 不是必须使用触发器的情况, 尽量少用触发器.variable deptno numbervariable dname varchar2(14)variable loc varchar2(13)variable last_mod varchar2(50)-- start testbegin :deptno := 10; select dname, loc, to_char(last_mod, ‘DD-MON-YYYY HH>MI.SSXFF AM TZR‘) into :dname, :loc, :last_mod from dept where deptno = :deptno;end;/-- 没有加锁, 直接修改update deptset dname = initcap(:dname), last_mod = systimestampwhere deptno = :deptno and last_mod = to_timestamp_tz(:last_mod, ‘DD-MON-YYYY HH>MI.SSXFF AM TZR‘);-- 注意这里的条件, 包括了 timestamp, 所以如果在之前有人修改过这个table, 那么本地保存的-- 数据就是"旧数据", 而此时如果你保存的是旧数据的话, 那么更新将不能进行, 因为条件不满足
也不能让应用程序每次修改时, 都判断这个值, 万一某个developer忘记了, 所以, 也可以将这个判断封装在
procedure中, 把上面的更新过程封装在procedure中, 而如果此procedure返回了 0 行, 就知道失败了.
根据上面代码, 如果返回 0 行, 表示这行在修改之前已经被修改过, 那么我们要怎么办?
告诉用户重新查询? 返回一个错误结果 ?
选择悲观锁 还是 乐观锁 ? 如果是能保持长连接, 使用悲观锁好, 否则就要使用乐观锁.
4. 阻塞
如果一个会话持有某个资源的锁, 而另一个会话在请求这个资源, 就会出现阻塞. 请求资源的session会被"挂起",
直至持有锁的会话放弃锁定资源. 几乎所有情况阻塞都是可以避免的, 而阻塞的真正原因是你的应用逻辑有问题, 即
update lost.
5条常见DML阻塞: insert, update, delete, merge, select for update
select for update 发生阻塞, 只要改成 select for update nowait, 这样, 应用就会报告, 这一行已经被锁定.
insert: 不常见, 比如一般插入时两行主键一样, 一般解决办法是, 使用 sequence 来充当主键. 不考虑吧, 一般不
会出现.
merge, update, delete: 如果发生, 说明你的程序存在 update lost, 按照之前的方法进行加锁, 就可以防止阻塞
5. 死锁
如果两个session, 每个session都持有另一个会话想要的资源, 此时就出现死锁.
例如: 两个表都分别只有1条数据, sessionA 修改了表A, sessionB 修改了表B, 此时, sessionB又想修改表A, 此时就
会阻塞(这不是死锁), 然后session又想修改表B, 这时就出现了死锁,
演示死锁
-- session 1-- initialize create table t1( xa number(2));create table t2( xb number(2));insert into t1 values(1);insert into t2 values(1);commit;-- start demonstrate --------------------------- session 1, execute 1-- 注意没有commit, 如果commit就没戏了update t1set xa = 2;-- session 2, execute 2update t2set xb = 2;-- 下面语句执行, 会发生阻塞update t1set xa = 3;-- 下面语句执行, 会发生死锁-- session 1, execute 3update t2set xb = 3;
要在这两个会话中选择一个作为"牺牲品"让它的语句"回滚"
我个人的PC测试时, 两个session都 hang 到那了, 不能动, 也不能回滚.
死锁一般很少产生, 根据大师的经验, 死锁的产生的主要原因是 外键未加索引(第2个原因是位图索引遭到并发更新)
以下3种情况, oracle在修改父表后会对子表加一个全表锁
1) 如果更新了父表的主键(倘若遵循rdbms原则, 主键应当是不可改变的, 这种改变主键的情况很少见), 由于外键没有
索引, 所以子表会被锁住.
2) 如果删除了父表中的一行, 整个子表也会被锁住(由于外键上没有索引)
演示
-- session 1create table p(x int primary key);create table c(x references p);insert into p values(1);insert into p values(2);commit;insert into c values(2);insert into c values(1);-- session 2-- 以下这条语句会被阻塞, 因为以下操作会为表c 加一个全表锁, 而此时的表c中的-- 某一行已经被session1加锁, 所以加全表锁不成功, 需要等待, 更新主键也会发生delete from p where x = 1;
预先测试, 子表中的外键索引:
column columns format a30 word_wrappedcolumn tablename format a15 word_wrappedcolumn constraint_name format a15 word_wrappedselect table_name, constraint_name, cname1 || nvl2(cname2, ‘,‘ ||cname2, null) || nvl2(cname3, ‘,‘ || cname3, null) || nvl2(cname4, ‘,‘||cname4, null) || nvl2(cname5, ‘,‘ || cname5, null) || nvl2(cname6, ‘,‘||cname6, null) || nvl2(cname7, ‘,‘ || cname7, null) || nvl2(cname8, ‘,‘||cname8, null) || columns from ( select b.table_name, b.constraint_name, max(decode( position, 1, column_name, null)) cname1, max(decode( position, 2, column_name, null)) cname2, max(decode( position, 3, column_name, null)) cname3, max(decode( position, 4, column_name, null)) cname4, max(decode( position, 5, column_name, null)) cname5, max(decode( position, 6, column_name, null)) cname6, max(decode( position, 7, column_name, null)) cname7, max(decode( position, 8, column_name, null)) cname8, count(*) col_cnt from ( select substr(table_name, 1, 30) table_name, substr(constraint_name, 1, 30) constraint_name, substr(column_name, 1, 30) column_name, position from user_cons_columns) a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = ‘R‘ group by b.table_name, b.constraint_name ) cons where col_cnt > all ( select count(*) from user_ind_columns i where i.table_name = cons.table_name and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8 ) and i.column_position <= cons.col_cnt group by i.index_name )/
所以, 一定要对子表的外键增加索引. (当然, 如果具备以下条件, 可以不给子表外键加索引)
1) 从没有父表删除行
2) 从没有更新父表的主键.
3) 从没有父表联结子表(如dept和emp)
以上情况, 一般不会得到满足, 所以还是给子表外键加索引把.
6. 锁类型( 主要有 3 种)
DML 锁: select for update, update, delete, insert, merge
用于确保一次只有一个人能修改某一行.
TX锁, 事务发起第一个修改时会得到TX锁(事务锁), 而且会一直持有这个锁, 直至事务执行提交(commit)或回滚(rollback)
oracle锁定的过程:
1) 找到想锁定的那一行地址.
2) 到达那一行
3) 锁定这一行(如果这一行已经锁定, 则等待锁住它的事务结束, 除非使用了 nowait 选项)
说明: 闩是数据的一个属性, 事务找到数据, 如果还没有被锁定, 则对其锁定, 在oracle对数据进行锁定时, 行指向事务ID的
一个副本, 事务ID存储在包含数据的块中, 释放锁时, 事务ID会保留下来, 这个事务ID是事务所独有的, 表示了撤销段号, 槽
和序列号, 事务ID是说明"这个事务ID 你"拥有这个数据(并非数据块上所有的数据都是你的, 只是你修改的那一行"归你"),
另一个session来到时, 它会看到锁ID, 由于锁ID表示一个事务, 所以可以很快的查看持有这个锁的事务是否还是活动的, 如果
锁不活动, 则准许会话访问这个数据. 如果锁还是活动的, 会话就会要求一旦释放锁就得到通知.
举例说明这个过程:
要用到3个v$表:
1) v$transaction, 对应每个活动事务都包含了一个条目(1行)
2) v$session, 显示已登录的会话
3) v$lock, 持有所有 enqueue 队列锁以及正在等待锁的会话, 都分别包含一个条目, 如果一个会话锁定了EMP表中的百万行,
v$lock 视图中对应这个会话还是只有一行, 这个视图显示了各个会话有哪些队列锁.
-- initialize ------------------create table deptas select * from scott.dept;create table empas select * from scott.emp;alter table deptadd constraint dept_pkprimary key(deptno);alter table empadd constraint emp_pkprimary key(empno);alter table empadd constraint emp_fk_deptforeign key (deptno)references dept(deptno);create index emp_deptno_idxon emp(deptno);-- start one transactionupdate deptset dname = initCap(dname);-- 查看系统状态-- 以下返回1条数据, 虽然锁定了4行, 但是只有1行select username, v$lock.sid, trunc(id1/power(2, 16))rbs, bitand(id1, to_number(‘ffff‘, ‘xxxx‘))+0 slot, id2 seq, lmode, request from v$lock, v$session where v$lock.type = ‘TX‘ and v$lock.sid = v$session.sid and v$session.username = USER;select XIDUSN, XIDSLOT, XIDSQN from v$transaction;
这样, 这个在数据块中的事务ID, 就对应到了创建table时, 有一个INITRANS 和 MAXTRANS 参数的用处
INITRANS: 这个结构初始的预分配大小, 对于索引和表, 这个大小默认是 2.(事务槽)
MAXTRANS: 这个结构可以扩展到的最大大小, oracle10g以后, 这个参数已经被废弃.
以下程序演示事务槽被填满(事务槽会动态增长, 除非这个块本身空间剩下不多, 才会被填满, 所以一般使用默认值就可以了)
create table t( x int primary key, y varchar2(4000)); insert into t(x,y) select rownum, rpad(‘*‘, 148, ‘*‘) from dual connect by level <= 46; select length(y), dbms_rowid.rowid_block_number(rowid) blk, count(*), min(x), max(x) from t group by length(y), dbms_rowid.rowid_block_number(rowid);-- 以下存储过程使用了递归调用, 直到我们的块填满create or replace procedure do_update( p_n in number ) as pragma autonomous_transaction; l_rec t%rowtype; resource_busy exception; pragma exception_init( resource_busy, -54 ); begin select * into l_rec from t where x = p_n for update NOWAIT; do_update( p_n+1 ); commit; exception when resource_busy then dbms_output.put_line( ‘locked out trying to select row ‘ || p_n ); commit; when no_data_found then dbms_output.put_line( ‘we finished - no problems‘ ); commit; end; /exec do_update(1);-- locked out trying to select row 38-- PL/SQL procedure successfully completed.-- 发现执行到, 第38行, 块满了, 无法再接受新的事务在这行-- 重新测试, 这次块内还有很大空间, 那么存储过程可以顺利执行truncate table t;insert into t (x,y)select rownum, rpad(‘*‘,147,‘*‘)from dualconnect by level <= 46;exec do_update(1);
TM锁(DML Enqueue)
用于确保表在修改时, 表的结构不会改变, 比如, 如果已经更新了一个表, 会得到这个表的TM锁, 这样, 另一个用户在该表上执行DROP或
alter命令, 就会得到错误提示.
DDL 锁: 可以保护对象结构定义
在 DDL 操作中会自动为对象加 DDL 锁(DDL Lock), 从而保护这些对象不会被其他会话所修改.
DDL 我们知道, 它会自动提交, 如果不成功, 会自动回滚
内部锁和闩: 保护内部数据结构.
闩就是一种锁, 设计为只保持极短的一段时间(例如, 修改一个内存中数据结构所需的时间), 闩用于保护某些内存结构, 等待闩的会话不会
排队, 只是一大堆会话在不断地重试.
闩"自旋"
等待闩可能是一个代价很高的操作, 如果闩不是立即可用的, 我们就得等待, 在一台多CPU机器上, 我们的会话就会自旋(spin), 也就是说,
在循环中反复的尝试来得到闩. 因为被"踢出"CPU,然后又必须调度回CPU需要很大开销,如果进程不能立即得到闩,我们就会一直呆在CPU上,
并立即再次请求, 而不是先睡眠, 放弃CPU, 等到必须调度回CPU时才再次尝试. 之所以呆在CPU上, 是因为我们指望闩的持有者正在另一个
CPU上忙于处理(由于闩设计为只保持很短的时间, 所以一般是这样), 而且很快放弃闩. 如果出现自旋并不断地尝试想得到闩, 但是之后还是
得不到闩, 此时我们的进程才会睡眠, 或者让开CPU (尝试一定的次数, 得到闩), 醒来时, 整个过程会再重复一遍.
测试 闩 消耗资源情况(很库)
建立测试环境, 代码如下:
-- 建立测试环境connect scott/scottbegin for i in 1..10 loop for x in (select * from user_tables where table_name = ‘T‘ || i) loop execute immediate ‘drop table‘ || x.table_name; end loop; execute immediate ‘create table t‘ || i || ‘(x int)‘; end loop;end;/
测试步骤:
1) statspack.snap
2) 立即启动 N 个 java例程, 表示并发用户
3) 等待所有 N 个 例程完成
4) 运行 statspack.snap
5) 为最后两个 statspack ID 生成 statspack 报告.
测试1: 不使用绑定变量.
package com.leon.testjava;import java.sql.*;public class instest { public static void main(String[] args) throws Exception { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:ora11gr2", "scott","soctt"); conn.setAutoCommit( false ); Statement stmt = conn.createStatement(); for( int i = 0; i < 25000; i++ ) { stmt.execute ("insert into "+ args[0] + " (x) values(" + i + ")" ); } conn.commit(); conn.close(); }}
测试2: 使用绑定变量.
import java.sql.*;public class instest{static public void main(String args[]) throws Exception{System.out.println( "start" );DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());Connectionconn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora11gr2","scott","scott");conn.setAutoCommit( false );PreparedStatement pstmt =conn.prepareStatement("insert into "+ args[0] + " (x) values(?)" );for( int i = 0; i < 25000; i++ ){ pstmt.setInt( 1, i ); pstmt.executeUpdate();} conn.commit(); conn.close(); System.out.println( "done" );}}