当前位置:Gxlcms > mysql > Oracle锁定策略的总结

Oracle锁定策略的总结

时间:2021-07-01 10:21:17 帮助过:53人阅读

以下是对Oracle锁定策略的总结: · Oracle只在修改时才对数据加行级锁。正常情况下不会升级到块级锁或表级锁。 · 如果只是读数据,Oracle 绝不会对数据锁定。不会因为简单的读操作在数据行上锁定。 · 写入器(writer)不会阻塞读取器(reader)。换种说法

以下是对Oracle锁定策略的总结:
· Oracle只在修改时才对数据加行级锁。正常情况下不会升级到块级锁或表级锁。
· 如果只是读数据,Oracle 绝不会对数据锁定。不会因为简单的读操作在数据行上锁定。
· 写入器(writer)不会阻塞读取器(reader)。换种说法:读(read)不会被写(write)阻塞。这一点几乎与其他所有数据库都不一样。在其他数据库中,读往往会被写阻塞。
· 写入器想写某行数据,但另一个写入器已经锁定了这行数据,此时该写入器才会被阻塞。读取器绝对不会阻塞写入器。

需要了解以下内容:
· 事务是每个数据库的核心。
· 必要时才提交事务。事务的大小只应该根据业务逻辑来定。
· 只要需要,就应该尽可能长时间地保持对数据所加的锁。锁不是稀有资源。
· 在Oracle中,行级锁没有相关的开销,对1 000 000行锁定所需的资源数与对1行锁定所需的资源数完全相同,这是一个固定的常量。
· 不要以为锁升级“对系统更好”(例如,使用表锁而不是行锁)。在Oracle中,锁升级(lock escalate)对系统没有任何好处,不会节省任何资源。
· 可以同时得到并发性和一致性。

Oracle中主要有3类锁,具体是:
· DML锁(DML lock):DML代表数据操纵语言(Data Manipulation Language)。如SELECT、INSERT、UPDATE、MERGE和DELETE语句。DML锁机制允许并发执行数据修改。
· DDL锁(DDL lock):DDL代表数据定义语言(Data Definition Language),如CREATE和ALTER语句等。DDL锁可以保护对象结构定义。
· 内部锁和闩:Oracle 使用这些锁来保护其内部数据结构。
具体介绍-〉http://www.linuxidc.com/Linux/2012-01/51267.htm


Oracle中的锁定一行的过程如下:
(1) 找到想锁定的那一行的地址。
(2) 到达那一行。
(3) 锁定这一行(如果这一行已经锁定,则等待锁住它的事务结束,除非使用了NOWAIT选项)。

仅此而已。由于闩为数据的一个属性,Oracle不需要传统的锁管理器。事务只是找到数据,如果数据还没有被锁定,则对其锁定。
需要注意,找到数据时,它可能看上去被锁住了,但实际上并非如此。在Oracle中对数据行锁定时,行指向事务ID的一个副本,事务ID存储在包含数据的块中,释放锁时,事务ID却会保留下来。这个事务ID是事务所独有的,表示了回滚段号、槽和序列号。事务ID留在包含数据行的块上,可以告诉其他会话:你“拥有”这个数据(并非块上的所有数据都是你的,只是你修改的那一行“归你所有”)。
另一个会话到来时,它会看到锁ID,由于锁ID表示一个事务,所以可以很快地查看持有这个锁的事务是否还是活动的。如果锁不活动,则允许会话访问这个数据。如果锁还是活动的,会话就会要求一旦释放锁就得到通知。因此,这就有了一个排队机制:请求锁的会话会排队,等待目前拥有锁的事务执行,然后得到数据。

如何用数据本身来管理锁定和事务信息?这是块开销的一部分。数据库块的最前面有一个“开销”空间(overhead),这里会存放该块的一个事务表,对于锁定了该块中某些数据的各个“实际”事务,在这个事务表中都有一个相应的条目。这个结构的大小由创建对象时CREATE语句上的两个物理属性参数决定:
· INITRANS:这个结构初始的预分配大小。对于索引和表,这个大小默认为2
· MAXTRANS:这个结构可以扩大到的最大大小。Oracle 10之后中,这个设置已经废弃了,MAXTRANS 总是255。
默认情况下,每个块最开始都有两个事务槽。一个块上同时的活动事务数受MAXTRANS值的约束,另外也受块上空间可用性的限制。
如果没有足够的空间来扩大这个结构,块上就无法得到255个并发事务,这时候就会等待,产生阻塞。
因此在频繁修改的表上就可能要增加INITRANS 设置,或者更常见的是,对于频繁修改的索引也可能需要这么做,因为索引块中的行一般比表中的行多。你可能需要增加PCTFREE或INITRANS,从而在块上提前预留足够的空间以应付可能的并发事务数。尤其是,如果你预料到块开始时几乎是满的(这说明块上没有空间来动态扩缩事务结构),则更需要增加PCTFREE或INITRANS。

可以做一个测试:
创建1个表,里面包含很多行,而且都存入一个块,使这个块一开始就比较满,只留下很少的空间,会限制事务表的增长。

[sql]

  1. create table t
  2. ( x int primary key,
  3. y varchar2(4000)
  4. );
  5. insert into t (x,y)
  6. select rownum, rpad('*',148,'*')
  7. from dual
  8. connect by level <= 46;

可以通过以下语句,可以看到表中有46行,都在同一个块上。之所以选择148个字符,是因为再多一个字符,就需要2个块才能放下这46行。
如果你的Oracle当前的块大小是8K,就能得到同下面的测试相同的结果。

[sql]

  1. select length(y),
  2. dbms_rowid.rowid_block_number(rowid) blk,
  3. count(*), min(x), max(x)
  4. from t
  5. group by length(y), dbms_rowid.rowid_block_number(rowid);
  6. LENGTH(Y) BLK COUNT(*) MIN(X) MAX(X)
  7. ---------- ---------- ---------- ---------- ----------
  8. 148 291 46 1 46


下面通过让多个事务通过select ... for update nowait语句同时锁定这个块上的数据,第一个事务锁定第一行,第二个事务锁定第二行,以此类推。
如果需要等待,则会产生1个ORA-54 resource busy的错误,这说明已经用完了这个块上的事务表。
通过使用自治事务(AUTONOMOUS_TRANSACTION),可以使用一个会话就完成这个测试,不需要运行大量SQL*PLUS会话。

[sql]

  1. create or replace procedure do_update( p_n in number )
  2. as
  3. pragma autonomous_transaction;
  4. l_rec t%rowtype;
  5. resource_busy exception;
  6. pragma exception_init( resource_busy, -54 );
  7. begin
  8. select *
  9. into l_rec
  10. from t
  11. where x = p_n
  12. for update NOWAIT;
  13. do_update( p_n+1 );
  14. commit;
  15. exception
  16. when resource_busy
  17. then
  18. dbms_output.put_line( 'locked out trying to select row ' || p_n );
  19. commit;
  20. when no_data_found
  21. then
  22. dbms_output.put_line( 'we finished - no problems' );
  23. commit;
  24. end;
  25. /

执行这个过程,可以得到如下结果:

[sql]

  1. tony@ORA11GR2> exec do_update(1);
  2. locked out trying to select row 38
  3. PL/SQL 过程已成功完成。

阻塞
有5条常见的DML语句会产生阻塞::INSERT、UPDATE、DELETE、MERGE 和SELECT FOR UPDATE。
对于一个阻塞的SELECT FOR UPDATE,只需增加NOWAIT 子句,它就不会阻塞,会报告一个ORA-00054:Resource Busy的错误。

1)阻塞的Insert
最常见的情况是,有一个带主键的表,或者表上有惟一的约束,但有两个会话试图用同样的值插入一行。如果是这样,其中一个会话就会阻塞。
此外,如果使用外键,对子表的插入可能会阻塞,因为它所依赖的父表正在创建或删除。

2)阻塞的Update 和Delete
如果有未提交的事务在这一行上执行了Update或者Delete操作,那么就会产生阻塞。

3)阻塞的Merge
Merge只是Insert和UPDATE(10g之后还有Delete)的组合。因此结果同上。

死锁
如果两个会话,每个会话都持有另一个会话想要的资源,此时就会出现死锁(deadlock)。
根据经验,导致死锁的头号原因是外键未加索引(第二号原因是表上的位图索引遭到并发更新)。
在以下两种情况下,Oracle在修改父表后会对子表加一个全表锁:
· 如果更新了父表的主键,由于外键上没有索引,所以子表会被锁住。
· 如果删除了父表中的一行,由于外键上没有索引,整个子表也会被锁住。
在 Oracle9i 及以上版本中,这些全表锁都是短期的,这意味着它们仅在DML 操作期间存在,而不是在整个事务期间都存在。即便如此,这些全表锁还是会导致很严重的锁定问题。

除了全表锁外,在下面情况下,未加索引的外键还可能带来性能问题。
· 如果有ON DELETE CASCADE,而且没有对子表加索引
例如,EMP是DEPT的子表,DELETE DEPTNO = 10 应该CASCADE(级联)至EMP[4]。如果EMP中的DEPTNO没有索引,那么删除DEPT表中的每一行时都会对EMP做一个全表扫描。这个全表扫描可能是不必要的,而且如果从父表删除多行,父表中每删除一行就要扫描一次子表。
· 从父表查询子表
例如利用DEPTNO查询EMP表是相当常见的。如果频繁地运行以下查询,没有索引会使查询速度变慢:
select * from dept, emp where emp.deptno = dept.deptno and dept.deptno = :X;

一般来说,仅当满足以下条件时不需要给外键加索引:
· 没有从父表删除行。
· 没有更新父表的惟一键/主键值(当心工具有时会无意地更新主键!)。
· 没有从父表联结子表

可以利用下面的脚本来查找没有加索引的外键。

[sql]

  1. column columns format a30 word_wrapped
  2. column tablename format a15 word_wrapped
  3. column constraint_name format a15 word_wrapped
  4. select table_name, constraint_name,
  5. cname1 || nvl2(cname2,','||cname2,null) ||
  6. nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
  7. nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
  8. nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
  9. columns
  10. from ( select b.table_name,
  11. b.constraint_name,
  12. max(decode( position, 1, column_name, null )) cname1,
  13. max(decode( position, 2, column_name, null )) cname2,
  14. max(decode( position, 3, column_name, null )) cname3,
  15. max(decode( position, 4, column_name, null )) cname4,
  16. max(decode( position, 5, column_name, null )) cname5,
  17. max(decode( position, 6, column_name, null )) cname6,
  18. max(decode( position, 7, column_name, null )) cname7,
  19. max(decode( position, 8, column_name, null )) cname8,
  20. count(*) col_cnt
  21. from (select substr(table_name,1,30) table_name,
  22. substr(constraint_name,1,30) constraint_name,
  23. substr(column_name,1,30) column_name,
  24. position
  25. from user_cons_columns ) a,
  26. user_constraints b
  27. where a.constraint_name = b.constraint_name
  28. and b.constraint_type = 'R'
  29. group by b.table_name, b.constraint_name
  30. ) cons
  31. where col_cnt > ALL
  32. ( select count(*)
  33. from user_ind_columns i
  34. where i.table_name = cons.table_name
  35. and i.column_name in (cname1, cname2, cname3, cname4,
  36. cname5, cname6, cname7, cname8 )
  37. and i.column_position <= cons.col_cnt
  38. group by i.index_name
  39. )
  40. /

有一个办法可以快速定位导致子表被锁住的代码段。可以发出以下命令:
ALTER TABLE DISABLE TABLE LOCK;
现在,对父表的可能导致子表被锁住的任何UPDATE或DELETE都会接收到以下错误:
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for
这有助于跟踪到有问题的代码段,(比如,你认为并没有对父表的主键执行UPDATE或DELETE)。

人气教程排行