Oracle锁定策略的总结

以你之姓@ 2021-06-24 13:59 539阅读 0赞

以下是对[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][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.    
6.  **insert** **into** t (x,y)  
7.  **select** rownum, rpad('\*',148,'\*')   
8.    **from** dual  
9.  **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.      
7.   LENGTH(Y)        BLK   COUNT(\*)     **MIN**(X)     **MAX**(X)  
8.  \---------- ---------- ---------- ---------- ----------   
9.         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.   
14.     do\_update( p\_n+1 );  
15.     **commit**;  
16. exception  
17. **when** resource\_busy   
18. **then**  
19.     dbms\_output.put\_line( 'locked out trying to select row ' || p\_n );  
20.     **commit**;  
21. **when** no\_data\_found  
22. **then**  
23.     dbms\_output.put\_line( 'we finished - no problems' );  
24.     **commit**;  
25. **end**;  
26. /

执行这个过程，可以得到如下结果：

**\[sql\]**

1.  tony@ORA11GR2> **exec** do\_update(1);  
2.  locked **out** trying **to** **select** row 38  
3.    
4.  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.    
5.  **select** table\_name, constraint\_name,  
6.       cname1 || nvl2(cname2,','||cname2,null) ||  
7.       nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||  
8.       nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||  
9.       nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)  
10.             columns  
11.   **from** ( **select** b.table\_name,  
12.                 b.constraint\_name,  
13.                 **max**(decode( position, 1, column\_name, null )) cname1,  
14.                 **max**(decode( position, 2, column\_name, null )) cname2,  
15.                 **max**(decode( position, 3, column\_name, null )) cname3,  
16.                 **max**(decode( position, 4, column\_name, null )) cname4,  
17.                 **max**(decode( position, 5, column\_name, null )) cname5,  
18.                 **max**(decode( position, 6, column\_name, null )) cname6,  
19.                 **max**(decode( position, 7, column\_name, null )) cname7,  
20.                 **max**(decode( position, 8, column\_name, null )) cname8,  
21.                 count(\*) col\_cnt  
22.            **from** (**select** substr(table\_name,1,30) table\_name,  
23.                         substr(constraint\_name,1,30) constraint\_name,  
24.                         substr(column\_name,1,30) column\_name,  
25.                         position  
26.                    **from** user\_cons\_columns ) a,  
27.                 user\_constraints b  
28.           **where** a.constraint\_name = b.constraint\_name  
29.             and b.constraint\_type = 'R'  
30.           **group** **by** b.table\_name, b.constraint\_name  
31.        ) cons  
32.  **where** col\_cnt > ALL  
33.          ( **select** count(\*)  
34.              **from** user\_ind\_columns i  
35.             **where** i.table\_name = cons.table\_name  
36.               and i.column\_name in (cname1, cname2, cname3, cname4,  
37.                                     cname5, cname6, cname7, cname8 )  
38.               and i.column\_position <= cons.col\_cnt  
39.             **group** **by** i.index\_name  
40.          )  
41. /

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

[Oracle]: http://www.linuxidc.com/topicnews.aspx?tid=12
[http_www.linuxidc.com_Linux_2012-01_51267.htm]: http://www.linuxidc.com/Linux/2012-01/51267.htm