全面解析oracle中的锁机制3

5、TX事务锁和 TM(DML enqueue)锁：

TX锁：数据块的前面有一个开销空间(ITL)，这里会存放一个块的事务列表，对于每一个锁定了块的事务，都会在这个事务列表中有一个条目。这个结构的大小有创建对象时的两个参数决定：

INITRANS:初始分配的可容纳事务的大小，一般初始为2，可以说是事务槽。

MAXTRANS:这个结构可以扩缩到的最大大小。它默认为255，在实际中，最小值为2。在Oracle10g 中，这个设置已经废弃了，所以不再使用。这个版本中的MAXTRANS 总是255。

 

oracle 不需要传统的锁管理器，事务只是找到数据，发现我（事务）要找的数据还没有被锁定，则锁定找到的数据，这个事务有一个ID号，他存储在找到数据对应的数据块的头部，被锁住的数据行指向这个事务ID（也就是说，被锁住的数据行与这个事务建立了一个连接）当你处理完了数据，并且将锁释放，这时，对这个数据行处理过的事务（即刚才建立连接的那个事务的ID不会被删除，他还在数据块的头部，这个ID里面有：回滚段号，槽（盛放事务的一个结构，初始值为2，就是前面介绍的那个事务列表）和序列号）的ID不会被删除。正因如此，当另一个事务来读数据行时，根据那里的锁的id，找到事务ID，查看这个持有这个锁的事务还活动吗。

如果还活动，那还要看这个新来的事务是读还是写或删除，读的话，就根据事务ID里的回滚段号，读出回滚段内事先存储好的这行数据的副本，这样就实现高度并发，避免了阻塞；

如果是写或者删除操作，那就进入enqueue队列等待。

 

TM锁：用于确定修改表的内容时，表的结构不会改变，如果你已经修改了一个表，那你就会得到这个表的一个TM锁，这可以防止另一个用户在该表上执行drop或者alter操作。

有一个事要告诉你啊,TM锁的ID1其实就是DML操作锁定对象的ID，TM锁ID2一般都是零。这样很容易的就能根据锁的ID1找到这个锁在那个对象上面。

 

6、 DDL锁：

在DDL 操作中会自动为对象加DDL 锁（DDL Lock），从而保护这些对象不会被其他会话所修改。例如，如果我执行一个DDL 操作ALTER  TABLE T，表T 上就会加一个排他DDL 锁，以防止其他会话得到这个表的DDL锁和TM 锁。在DDL 语句执行期间会一直持有DDL 锁，一旦操作执行就立即释放DDL 锁。实际上，通常会把DDL 语句包装在隐式提交（或提交/回滚对）中来执行这些工作。由于这个原因，在Oracle 中DDL 一定会提交。

当我们发出DDL命令时，会自动在被处理的对象上添加DDL锁定，从而防止对象被其他用户所修改。当DDL命令结束以后，则释放DDL锁定。我们不能显式地请求一个DDL锁定，只有当对象结构被修改或者被引用时，才会在对象上添加DDL锁定。比如创建或者编译存储过程时会对引用的对象添加DDL锁定。在创建视图时，也会对引用的表添加DDL锁定等。

在执行DDL命令之前，Oracle会自动添加一个隐式提交命令，然后执行具体的DDL命令，在DDL命令执行结束之后，还会自动添加一个隐式提交命令。

每条CREATE、ALTER 等语句实际上都如下执行（这里用伪代码来展示）：

Begin

         Commit;

         DDL-STATEMENT

Commit;

Exception

         Whenothers then rollback;

End;

　实际上，Oracle在执行DDL命令时，都会将其转换为对数据字典表的DML操作。比如我们发出创建表的DDL命令时，Oracle会将表的名称插入数据字典表tab$里，同时将表里的列名以及列的类型插入col$表里等。因此，在DDL命令中需要添加隐式的提交命令，从而提交那些对数据字典表的DML操作。即使DDL命令失败，它也会发出提交命令。

　　我们来看下面的例子，启动两个session，其中一个叫做sess #1，另一个叫做sess #2。在sess #1里发出如下的SQL语句：

      SQL> insert into t values(1);
　　1 row created.

　　然后在sess #2里查询表T里的数据：

      SQL> select * from t; 
　　no rows selected

　　显然，由于sess #1还没有提交，因此sess #2里不能检索出sess #1所插入的记录。接下来，我们在sess #1里执行下面的语句：

      SQL> create table t(c1 number);  -————此处包含一个隐式提交命令，把之前那个insert给提交了。
　　create table t(c1 number) 
　　* 
　　ERROR at line 1: 
　　ORA-00955: name is already used by an existing object

　　由于表T已经存在，因此创建表T的命令失败。这时我们再回到sess #2里查询表T：

      SQL> select * from t; 
　　ID 
　　---------- 
　　1

　　很明显，我们并没有在sess #1里发出commit命令，但这时sess #1里所作的插入操作已经被提交了(上面的隐式提交)。这个commit就是通过create table这个DDL命令隐式发出的，尽管create table命令已经失败了。

有3 种类型的DDL 锁：

排他DDL 锁（Exclusive DDL lock）：DDL操作期间你可以查询一个表，但是无法以任何方式修改这个表。　大部分的DDL操作都会在被操作的对象上添加排他的DDL锁定，从而防止在DDL命令执行期间，对象被其他用户所修改。当对象上添加了排他的DDL锁定以后，该对象上不能再添加任何其他的DDL锁定（不能再加任何排他或者共享DDL锁）。如果是对表进行DDL命令，则其他进程也不能修改表里的数据。

共享DDL 锁（Share DDL lock）：这些锁会保护所引用对象的结构，使之不会被其他会话修改，但是允许修改数据。用来保护被DDL的对象不被其他用户进程所更新，但是允许其他进程在对象上添加共享的DDL锁定。如果是对表进行DDL命令，则其他进程可以同时修改表里的数据。比如我们发出create view命令创建视图时，在视图的所引用的表(这种表也叫基表)上添加的就是共享的DDL命令。也就是说，在创建视图时，其他用户不能修改基表的结构，但是可以更新基表里的数据。

可中断解析锁（Breakable parse locks）：在shared pool里缓存的SQL游标或者PL/SQL程序代码都会获得引用对象上的解析锁定。如果我们发出DDL命令修改了某个对象的结构时，该对象相关的、位于shared pool里的解析锁定就被打破，从而导致引用了该对象的SQL游标或者PL/SQL程序代码全都失效。下次再次执行相同的SQL语句时，需要重新解析，这也就是所谓的SQL语句的reload了。可打破的解析锁定不会阻止其他的DDL锁定，如果发生与解析锁定相冲突的DDL锁定，则解析锁定也会被打破。这些锁允许一个对象（如共享池中缓存的一个查询计划）向另外某个对象注册其依赖性。如果在被依赖的对象上执行DDL，Oracle会查看已经对该对象注册了依赖性的对象列表，并使这些对象无效。因此，这些锁是“可中断的”，它们不能防止DDL 出现。（黄色部分晦涩难懂）。

　大多数的DDL操作都会有一个DDL排他锁，如下列命令：

alter table t add  new_colmun data;

在执行上面这句DDL操作的时候，对于表 t  其他的DDL操作都不能再施加，但是可以使用select 查询表中的内容。

以下内容直接没看懂他妈的：画上下划线记者一天看一遍，知道懂了为止：

在Oracle 中，现在有些DDL 操作没有DDL 锁也可以发生。例如，可以发出以下语句：

create index t_idx on t(x) ONLINE;

ONLINE 关键字会改变具体建立索引的方法。Oracle 并不是加一个排他DDL 锁来防止数据修改，而只会试图得到表上的一个低级（mode 2）TM 锁。这会有效地防止其他DDL 发生，同时还允许DML 正常进行。Oracle 执行这一“壮举”的做法是，为DDL 语句执行期间对表所做的修改维护一个记录，执行CREATE 时再把这些修改应用至新的索引。这样能大大增加数据的可用性。

　　

有一个意义非凡的视图可用于查看这个信息，即DBA_DDL_LOCKS 视图。

对此没有相应的V$视图。DBA_DDL_LOCKS 视图建立在更神秘的X$表基础上，而且默认情况下，你的数据库上不会安装这个视图。可以运行[ORACLE_HOME]/rdbms/admin 目录下的catblock.sql 脚本来安装这个视图以及其他锁视图。必须作为用户SYS 来执行这个脚本才能成功。

　　我们主要通过dba_ddl_locks视图来监控DDL锁定，没有与DDL锁定相关的V$视图。如果没有发现dba_ddl_locks视图，则执行脚本$ORACLE_HOME/rdbms/admin/catblock.sql来创建该视图，执行脚本时应该以用户sys的身份登录数据库。

　　我们来做个试验，并从dba_ddl_locks视图里查看有关DDL锁定的情况。在该试验中，我们创建一个存储过程，如下所示：

      SQL> create or replace procedure p_test is
　　2 ln_id number;
　　3 begin
　　4 dbms_lock.sleep(600);
　　5 end;
　　6 /

　　在该存储过程中，我们调用dbms_lock.sleep，dbms_lock.sleep能够让系统挂起，挂起的时间长度由传入参数决定，传入参数的单位是秒。在本例中，也就是让系统挂起600秒。

　　然后，我们启动三个session，并检索每个session的SID号，如下所示：

      SQL> select sid from v$mystat where rownum=1;
　　SID
　　----------
　　149

　　SQL> select sid from v$mystat where rownum=1;
　　SID
　　----------
　　151

　　SQL> select sid from v$mystat where rownum=1;
　　SID
　　----------
　　159

　　在149号session里我们执行存储过程p_test：

      SQL> exec p_test;

　　这时149号session被挂起，挂起持续的时间为600秒。然后我们到151号session里执行下面的SQL语句，对p_test进行编译：

 　SQL> alter procedure p_test compile;

　　我们会发现151号session也被挂起了，因为这时149号session正在执行p_test，因此151号的编译p_test命令必须等待。

　　接下来，我们到159号session里执行下面的语句，删除p_test：

     SQL> drop procedure p_test;

　　显然，由于151号session正在编译p_test，我们也无法删除p_test，因此159号session也被挂起了。

　　我们查询dba_ddl_locks视图，来了解这时DDL锁定的情况：

      SQL> select session_id,type,mode_held,mode_requested from dba_ddl_locks
　　2 where session_id in(149,151,159) and wner='HR' and name='P_TEST';

　　SESSION_ID         TYPE                       MODE_HELD       MODE_REQUESTED
　　----------              ---------------------         ---------                           --------------
　　159             Table/Procedure/Type     Null                                  None
　　159             Table/Procedure/Type    None                               Exclusive
　　151             Table/Procedure/Type     Exclusive                         None
　　149            Table/Procedure/Type       Null                                  None

　　从MODE_HELD列上可以看到，151号session尝试编译p_test，因此它获得了p_test上的排他的DDL锁定。而从MODE_REQUESTED列上可以看到，159号session尝试删除p_test，因此也需要在p_test上添加排他的DDL锁定。但是这时p_test上已经存在DDL锁定了，于是159号session只好等待。

1、行级锁 

insert ,update ,delete，自动在行上加一个行级锁。通过commit,rollback解锁。 
查看行级锁阻塞情况： 
select sid , blocking_session,username,event from v$session where blocking_session_status='VALID'; 
查看session加锁情况，不含锁阻塞的情况： 
select * from v$locked_objects; 
oracle 加锁是依次执行的，假设有3个用户，同时修改某行数据。第一个用户先获得行级锁，其他两个用户处于等待状态，也就是阻塞了其他两个用户。当这个用户 commit操作，解放自身的行级锁。此时，第二个用户将获得行级锁，再阻塞第三个用户。直到第二个用户释放行级锁，第三个用户才有机会获得锁，否则将一直处于等待状态。 

2、表级锁 
1) lock table tab_name in share mode(共享锁) 在表级别上加上共享锁，在该表上用户只能够select操作，其他操作都被阻塞，要一直等到该表的锁释放。同时还允许其他用户在该表上也加上share锁。Type为4。 

解锁：用户在该表上执行commit或rollback后，将解除该锁。 

2) lock table tab_name in exclusive mode(排他锁) 在表级别上加上排他锁，在该表上用户只能select操作，其他操作都被阻塞，通过还不允许用户再在该表上加上其他类型的锁。Type为6（加锁级别最高）。 

解锁：用户在该表上执行commit或rollback后，将解除该锁。 

3) Lock table tab_name in share row exclusive mode（）：在表级别上加上排他锁，在该表上用户只能select操作，其他操作将被阻塞，不能加共享锁、共享行排他锁和排他锁。Type为5。 
解锁：用户在该表上执行commit或rollback后，将解除该锁。 

3、数据库级锁 
1) alter system enable restricted session; 
受限方式打开数据库，对已经连接上数据库的用户仍然能够操作数据库；要连接数据库，用户必须具有restrictive session的权限。 其目的主要是希望在数据库打开期间，且在没有用户会话干扰（如建立连接和执行任务）的情况下完成数据库的维护操作（如数据库的导入、导出）。 
Alter system disable restricted session;解除数据库受限模式。 
2) 以只读方式打开数据库 
Shutdown immediate 
Startup mount 
Alter database open read only; 

这时候数据库只能够执行select操作，其他操作就要抛出错。

与游标相关：

 1、游标中用for update

   当你定义一个在current of子句中被参照的游标，你必须用for update子句去请求一个行级排他锁，例如：

DECLARE
   CURSOR c1 IS SELECT employee_id, salary FROM employees
      WHERE job_id = 'SA_REP' AND commission_pct > .10
      FOR UPDATE NOWAIT;  --这个就是说明这些查询出来的行都是将被更新，或者删除的，在每一行中都会被锁定，这样是非常有用的，比如当你打算更新一个已经存在的行的某个值或某一行，你必须先确定这行是没有被别的用户更新过的。NOWAIT是个可选的关键字，他告诉这个操作，如果你要查询的行已经被倍的用户锁定了，那你就不要在这等待了。迅速返回到程序中，这样就可以去做别的工作了，等待会在过去看看那个用户解锁了没，若果你不加这个NOWAIT关键字，这个查询会一直等着那个行被别的用户释放。

  当你打开这个游标的时候，所有的行就被锁定了，当你提交，或者回滚这个事务的时候，行就会解锁，所以我们无法从一个已经提交了的 fof update 游标中fetch内容。

 2、for update of 

  当查询多个表的时候，可以只锁定一个表中得行，而不锁定另一个表中的行，下面这个例子只锁定了employees表但是没有锁定departments表：

DECLARE

   CURSOR c1 IS SELECT last_name, department_name FROM employees, departments

      WHERE employees.department_id = departments.department_id

            AND job_id = 'SA_MAN'

        FOR UPDATE OF salary;

3、用current of 去更新读取游标最近的一行

DECLARE

    my_emp_id NUMBER(6);

    my_job_id VARCHAR2(10);

    my_sal NUMBER(8,2);

  CURSOR c1 IS SELECT employee_id, job_id, salary FROM employees FOR UPDATE;

 BEGIN

   OPEN c1;

   LOOP

     FETCH c1 INTO my_emp_id, my_job_id, my_sal;

       IF my_job_id = 'SA_REP' THEN

           UPDATE employees SET salary = salary * 1.02 WHERE CURRENT OF c1;  --这里去参照了c1中得查询。

        END IF;

       EXIT WHEN c1%NOTFOUND;

     END LOOP;

END;

4、看下面一个例子：

DECLARE

-- if "FOR UPDATE OF salary" is included on following line, an error is raised，如果FOR UPDATE OF用在下面，就会出错

         CURSOR c1 IS SELECT * FROM employees;

        emp_rec employees%ROWTYPE;

BEGIN

OPEN c1;

     LOOP

  FETCH c1 INTO emp_rec; -- FETCH fails on the second iteration with FOR UPDATE 如果上面有FOR UPDATE ,，第二                                                                -- 次循环时，由于已经commit了，所以就会报错。

 EXIT WHEN c1%NOTFOUND;

   IF emp_rec.employee_id = 105 THEN

        UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 105;

 END IF;

 COMMIT; -- releases locks

     END LOOP;

END;

如果你真的想读取和提交交叉进行，那也不是没有可能，那就是用rowid 伪列咯，用伪列来模拟current of 子句，查出每一行的rowid ，放入一个UROWID的变量类型的（至于UROWID是个啥，哥现在确实不知道。。）这样你就可以用rowid在后来的更新和删除操作中去标记当前的所有行。

declare

  cursor c1 is select  last_name,job_id , rowid  from  employees;

  my_lastname  employees.last_name%type;

  my_rowid   UROWID;

begin

  open c1

  loop

   fetch c1 into my_lastname,  my_jobid , my_rowid;

  exit when c1%notfound;

  update employees set salary =salary*1.3 where worid =my_rowid; --这句话就是模拟了where current of c1；

  commit;

 end loop;

end;

下面这个用了%rowtype。

DECLARE

CURSOR c1 IS SELECT employee_id, last_name, salary, rowid FROM employees;

emp_rec c1%ROWTYPE;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO emp_rec;

EXIT WHEN c1%NOTFOUND;

IF emp_rec.salary = 0 THEN

DELETE FROM employees WHERE rowid = emp_rec.rowid;

END IF;

END LOOP;

CLOSE c1;

END;