一、基本概念
1、丢失更新
在没有锁的情况下，两个人在基本同一时间对数据库的某行做修改。后来修改的会话会覆盖掉之前修改的会话的结果。

2、阻塞
一个会话保持另一个会话正在请求的资源上的锁定时，就发生阻塞。阻塞分为插入阻塞、更新和删除阻塞。
避免发生阻塞的方法是使用序列或者DBMS_LOCK包提供的方法手工锁定避免此情况。
更新阻塞可采用select  from upadate nowait来验证没有更新的行。

3、死锁
A会话：Update test Set test.col1 = 36 不提交
B会话：Update test1 set test1.col1=48 不提交

B会话：update test set test.col1=48 不提交
A会话：Update test1 Set test1.col1 = 96

然后在B会话会提示：
ORA-00060：等待资源时检测到死锁

检查没有索引的外键：
column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped
    
 select 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
         )
/

在plsql中执行该脚本报错。提示ORA-00979：不是group by表达式，尽管报错了，但是测试继续。

二、锁定类型
1  DML锁定
1.1 TX锁定
TX用于保证在一段时间只有一个用户进行修改，并且别人不可一删除正在使用的表。
测试如下：

在会话A执行
Update test Set col1 = 118 Where col1 = 18
不提交

在当前会话执行
Update test Set col1 = 128 Where col1 = 18

提示0行被更新

在B会话执行
Update test Set col1 = 128 Where col1 = 18

该执行挂起，等待A会话的执行确认或者回滚之后才能正常执行。


在A会话执行：
alter table TEST drop column COL1;
提示：ORA-00054: 资源正忙，要求指定 NOWAIT

执行：
select username,v$lock.TYPE,
        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
/
v$session展示登陆的会话
v$lock包含所有被持有的锁的入口，也包含正在等待锁定的会话的入口

结果如下：

SQL> select username,v$lock.TYPE,
  2          v$lock.sid,
  3          trunc(id1/power(2,16)) rbs,
  4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
  5          id2 seq,
  6          lmode,
  7          request
  8   from v$lock, v$session
  9   where v$lock.type = 'TX'
 10      and v$lock.sid = v$session.sid
 11     and v$session.username = USER
 12  /
 
 
USERNAME                       TYPE        SID        RBS       SLOT        SEQ      LMODE    REQUEST
------------------------------ ---- ---------- ---------- ---------- ---------- ---------- ----------
TEST                           TX           13          7         31        250          0          6
TEST                           TX           14          7         31        250          6          0


执行脚本：
select XIDUSN, XIDSLOT, XIDSQN
    from v$transaction
/

v$transaction展示了每个活动事物的入口

结果如下：

select XIDUSN, XIDSLOT, XIDSQN
  2      from v$transaction
  3  /
 
 
    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         7         31        250


继续测试，回滚A会话的修改，回滚B会话的修改，关闭B会话

执行：
select username,v$lock.TYPE,
        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
/

SQL> select username,v$lock.TYPE,
  2          v$lock.sid,
  3          trunc(id1/power(2,16)) rbs,
  4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
  5          id2 seq,
  6          lmode,
  7          request
  8   from v$lock, v$session
  9   where v$lock.type = 'TX'
 10      and v$lock.sid = v$session.sid
 11     and v$session.username = USER
 12  /
 
USERNAME                       TYPE        SID        RBS       SLOT        SEQ      LMODE    REQUEST
------------------------------ ---- ---------- ---------- ---------- ---------- ---------- ----------
 
SQL>

此时锁定消失。


在会话A执行
Update test Set col1 = 118 Where col1 = 18
不提交

执行：
select username,v$lock.TYPE,
        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
/

结果：

SQL> select username,v$lock.TYPE,
  2          v$lock.sid,
  3          trunc(id1/power(2,16)) rbs,
  4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
  5          id2 seq,
  6          lmode,
  7          request
  8   from v$lock, v$session
  9   where v$lock.type = 'TX'
 10      and v$lock.sid = v$session.sid
 11     and v$session.username = USER
 12  /
 
USERNAME                       TYPE        SID        RBS       SLOT        SEQ      LMODE    REQUEST
------------------------------ ---- ---------- ---------- ---------- ---------- ---------- ----------
TEST                           TX           14          3         17        329          6          0
 
结论：oracle给每个会话开启一个锁定

1.2 TM锁定

这些锁定用来保证在更改表的内容时，表的结构不会被更改。

分别执行下面的语句，
Insert Into test Values(1,2)

Insert Into test1 Values (2,3)
不提交

执行：

select username,
       v$lock.sid,
            id1, id2,
        lmode,
           request, block, v$lock.type
 from v$lock, v$session
  where v$lock.sid = v$session.sid
    and v$session.username = USER
/

结果如下：

SQL> select username,
  2         v$lock.sid,
  3              id1, id2,
  4          lmode,
  5             request, block, v$lock.type
  6   from v$lock, v$session
  7    where v$lock.sid = v$session.sid
  8      and v$session.username = USER
  9  /
 
 
USERNAME                              SID        ID1        ID2      LMODE    REQUEST      BLOCK TYPE
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----
TEST                                   14      30914          0          3          0          0 TM
TEST                                   14      65558        265          6          0          0 TX
TEST                                   14      31267          0          3          0          0 TM
 
SQL>

结论：每个事务中只获取一个TX锁，但是可以获取与所修改数量一样多的TM锁定。此时ID1是DML锁定对象的对象ID。
TM缩的总数是可配置的DML_LOCKS，如果该参数配置为0，意味着不允许DDL。

2、DDL锁定

三种DDL锁定：
2.1 独占的DDL锁定，防止其他会话获得DDL锁定或TM锁定，意味着在此期间，查询可以进行，任何方式的修改不允许。
2.2 共享的DDL锁定，保护引用对象的结构，防止其他会话修改，但是允许数据修改
2.3 可以打破的分析锁定，不能防止DDL发生

多数的DDL使用独占的DDL锁定

下面的语句不使用DDL独占锁
Create Index index_test On test(col1) Online

执行该语句之前执行：
Create Table ddl_lock1 As Select *  From dba_ddl_locks
SQL> desc  ddl_lock1;
Name           Type         Nullable Default Comments
-------------- ------------ -------- ------- --------
SESSION_ID     NUMBER       Y                        
OWNER          VARCHAR2(60) Y                        
NAME           VARCHAR2(60) Y                        
TYPE           VARCHAR2(40) Y                        
MODE_HELD      VARCHAR2(9)  Y                        
MODE_REQUESTED VARCHAR2(9)  Y   
SQL> col session_id for a10;
SQL> col owner for a10;
SQL> col name for a30;
SQL> col type for a30;
SQL> select * from ddl_lock1
  2  /
 
SESSION_ID OWNER      NAME                           TYPE                           MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
         5 XDB        DBMS_XDBZ0                     Body                           Null      None
         5 XDB        DBMS_XDBZ                      Table/Procedure/Type           Null      None
         5 SYS        DBMS_CDC_PUBLISH               Body                           Null      None
         5 SYS        DICTIONARY_OBJ_OWNER           Table/Procedure/Type           Null      None
         5 SYS        DICTIONARY_OBJ_NAME            Table/Procedure/Type           Null      None
         5 XDB        DBMS_XDBZ0                     Table/Procedure/Type           Null      None
         5 SYS        DBMS_STANDARD                  Table/Procedure/Type           Null      None
         5 SYS        DBMS_CDC_PUBLISH               Table/Procedure/Type           Null      None
         5 XDB        DBMS_XDBZ                      Body                           Null      None
        13 XDB        DBMS_XDBZ0                     Body                           Null      None
        13 SYS        DICTIONARY_OBJ_NAME            Table/Procedure/Type           Null      None
        13 SYS        DATABASE                       18                             Null      None
        13 XDB        DBMS_XDBZ0                     Table/Procedure/Type           Null      None
        13 SYS        DBMS_STANDARD                  Table/Procedure/Type           Null      None
        13 TEST       TEST                           18                             Null      None
        13 SYS        DBMS_APPLICATION_INFO          Body                           Null      None
        13 SYS        DBMS_APPLICATION_INFO          Table/Procedure/Type           Null      None
        13 SYS        DICTIONARY_OBJ_OWNER           Table/Procedure/Type           Null      None
        13 SYS        DBMS_CDC_PUBLISH               Table/Procedure/Type           Null      None
        13 SYS        DBMS_CDC_PUBLISH               Body                           Null      None
 
SESSION_ID OWNER      NAME                           TYPE                           MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
        13 XDB        DBMS_XDBZ                      Body                           Null      None
        13 XDB        DBMS_XDBZ                      Table/Procedure/Type           Null      None
        17 SYS        DBMS_TRANSACTION               Table/Procedure/Type           Null      None
        17 SYS        DBMS_TRANSACTION               Body                           Null      None
        17 SYS        DBMS_APPLICATION_INFO          Table/Procedure/Type           Null      None
        17 SYS        DATABASE                       18                             Null      None
        17 TEST       TEST                           18                             Null      None
        17 SYS        DBMS_APPLICATION_INFO          Body                           Null      None
 
28 rows selected
 



执行该语句之后执行：
Create Table ddl_lock2 As Select *  From dba_ddl_locks

利用PL/SQL DEVELOPER工具进行DDL操作，删除col1字段，不提交
同时执行语句：
Create Table ddl_lock3 As Select *  From dba_ddl_locks


在另外一个会话利用PL/SQL DEVELOPER工具进行DDL操作，修改col1字段为col3字段，不提交

执行：

Create Table ddl_lock4 As Select *  From dba_ddl_locks


执行：
Select * From  ddl_lock2
Minus
Select * From  ddl_lock1

结果：

SQL> Select * From  ddl_lock2
  2  Minus
  3  Select * From  ddl_lock1
  4  /
 
SESSION_ID OWNER      NAME                           TYPE                           MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
        13 SYS        DATABASE                       18                             Null      None
        13 SYS        DBMS_APPLICATION_INFO          Body                           Null      None
        13 SYS        DBMS_APPLICATION_INFO          Table/Procedure/Type           Null      None
        13 SYS        DBMS_CDC_PUBLISH               Body                           Null      None
        13 SYS        DBMS_CDC_PUBLISH               Table/Procedure/Type           Null      None
        13 SYS        DBMS_OUTPUT                    Body                           Null      None
        13 SYS        DBMS_OUTPUT                    Table/Procedure/Type           Null      None
        13 SYS        DBMS_STANDARD                  Table/Procedure/Type           Null      None
        13 SYS        DBMS_TRANSACTION               Body                           Null      None
        13 SYS        DBMS_TRANSACTION               Table/Procedure/Type           Null      None
        13 SYS        DICTIONARY_OBJ_NAME            Table/Procedure/Type           Null      None
        13 SYS        DICTIONARY_OBJ_OWNER           Table/Procedure/Type           Null      None
        13 TEST       TEST                           18                             Null      None
        13 XDB        DBMS_XDBZ                      Body                           Null      None
        13 XDB        DBMS_XDBZ                      Table/Procedure/Type           Null      None
        13 XDB        DBMS_XDBZ0                     Body                           Null      None
        13 XDB        DBMS_XDBZ0                     Table/Procedure/Type           Null      None
        17 TEST       DDL_LOCK2                      Table/Procedure/Type           Exclusive None
 

执行：
Select * From  ddl_lock3
Minus
Select * From  ddl_lock2
结果：
SESSION_ID OWNER      NAME                           TYPE                           MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
        17 TEST       DDL_LOCK3                      Table/Procedure/Type           Exclusive None

执行：
Select * From  ddl_lock4
Minus
Select * From  ddl_lock3

结果：

SESSION_ID OWNER      NAME                           TYPE                           MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
        17 TEST       DDL_LOCK4                      Table/Procedure/Type           Exclusive None

最终的dba_ddl_locks内容如下：
SQL> col session_id for a10;
SQL> col owner for a10;
SQL> col name for a30;
SQL> col type for a30
SQL> Select * From  ddl_lock4
  2  /
 
SESSION_ID OWNER      NAME                           TYPE                           MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
         5 XDB        DBMS_XDBZ0                     Body                           Null      None
         5 XDB        DBMS_XDBZ                      Table/Procedure/Type           Null      None
         5 SYS        DBMS_CDC_PUBLISH               Body                           Null      None
         5 SYS        DICTIONARY_OBJ_OWNER           Table/Procedure/Type           Null      None
         5 SYS        DICTIONARY_OBJ_NAME            Table/Procedure/Type           Null      None
         5 SYS        DBMS_CDC_PUBLISH               Table/Procedure/Type           Null      None
         5 XDB        DBMS_XDBZ0                     Table/Procedure/Type           Null      None
         5 SYS        DBMS_STANDARD                  Table/Procedure/Type           Null      None
         5 XDB        DBMS_XDBZ                      Body                           Null      None
         7 SYS        DBMS_APPLICATION_INFO          Table/Procedure/Type           Null      None
         7 SYS        DATABASE                       18                             Null      None
         7 TEST       TEST                           18                             Null      None
         7 SYS        DBMS_APPLICATION_INFO          Body                           Null      None
        12 XDB        DBMS_XDBZ0                     Body                           Null      None
        12 SYS        DATABASE                       18                             Null      None
        12 XDB        DBMS_XDBZ0                     Table/Procedure/Type           Null      None
        12 SYS        DBMS_STANDARD                  Table/Procedure/Type           Null      None
        12 TEST       TEST                           18                             Null      None
        12 SYS        DBMS_APPLICATION_INFO          Body                           Null      None
        12 SYS        DICTIONARY_OBJ_NAME            Table/Procedure/Type           Null      None
 
SESSION_ID OWNER      NAME                           TYPE                           MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
        12 SYS        DICTIONARY_OBJ_OWNER           Table/Procedure/Type           Null      None
        12 SYS        DBMS_CDC_PUBLISH               Table/Procedure/Type           Null      None
        12 SYS        DBMS_APPLICATION_INFO          Table/Procedure/Type           Null      None
        12 SYS        DBMS_CDC_PUBLISH               Body                           Null      None
        12 XDB        DBMS_XDBZ                      Body                           Null      None
        12 XDB        DBMS_XDBZ                      Table/Procedure/Type           Null      None
        13 XDB        DBMS_XDBZ0                     Body                           Null      None
        13 SYS        DATABASE                       18                             Null      None
        13 XDB        DBMS_XDBZ0                     Table/Procedure/Type           Null      None
        13 SYS        DBMS_STANDARD                  Table/Procedure/Type           Null      None
        13 TEST       TEST                           18                             Null      None
        13 SYS        DBMS_APPLICATION_INFO          Body                           Null      None
        13 SYS        DBMS_APPLICATION_INFO          Table/Procedure/Type           Null      None
        13 SYS        DBMS_OUTPUT                    Body                           Null      None
        13 SYS        DBMS_OUTPUT                    Table/Procedure/Type           Null      None
        13 SYS        DBMS_TRANSACTION               Body                           Null      None
        13 SYS        DBMS_TRANSACTION               Table/Procedure/Type           Null      None
        13 SYS        DICTIONARY_OBJ_NAME            Table/Procedure/Type           Null      None
        13 SYS        DICTIONARY_OBJ_OWNER           Table/Procedure/Type           Null      None
        13 SYS        DBMS_CDC_PUBLISH               Table/Procedure/Type           Null      None
        13 SYS        DBMS_CDC_PUBLISH               Body                           Null      None
 
SESSION_ID OWNER      NAME                           TYPE                           MODE_HELD MODE_REQUESTED
---------- ---------- ------------------------------ ------------------------------ --------- --------------
        13 XDB        DBMS_XDBZ                      Body                           Null      None
        13 XDB        DBMS_XDBZ                      Table/Procedure/Type           Null      None
        17 TEST       DDL_LOCK4                      Table/Procedure/Type           Exclusive None
        17 XDB        DBMS_XDBZ0                     Table/Procedure/Type           Null      None
        17 SYS        DBMS_STANDARD                  Table/Procedure/Type           Null      None
        17 TEST       TEST                           18                             Null      None
        17 SYS        DBMS_APPLICATION_INFO          Body                           Null      None
        17 SYS        DBMS_APPLICATION_INFO          Table/Procedure/Type           Null      None
        17 SYS        DBMS_OUTPUT                    Body                           Null      None
        17 SYS        DBMS_OUTPUT                    Table/Procedure/Type           Null      None
        17 SYS        DBMS_TRANSACTION               Body                           Null      None
        17 SYS        DBMS_TRANSACTION               Table/Procedure/Type           Null      None
        17 SYS        DATABASE                       18                             Null      None
        17 SYS        DICTIONARY_OBJ_NAME            Table/Procedure/Type           Null      None
        17 SYS        DICTIONARY_OBJ_OWNER           Table/Procedure/Type           Null      None
        17 SYS        DBMS_CDC_PUBLISH               Table/Procedure/Type           Null      None
        17 SYS        DBMS_CDC_PUBLISH               Body                           Null      None
        17 XDB        DBMS_XDBZ                      Body                           Null      None
        17 XDB        DBMS_XDBZ0                     Body                           Null      None
        17 XDB        DBMS_XDBZ                      Table/Procedure/Type           Null      None
 
61 rows selected

SQL>
其中的含义不是很明白，只了解，在每次进行过程中，就加了一个DDL锁定。
在模拟DDL锁之前dba_ddl_locks的内容都是什么含义？
模拟DDL锁定之后，TEST用户中的每个锁定意义是什么？
DDL锁释放之后，什么时候更新dba_ddl_locks表中的内容，还是说不会更新？

3 锁存器和内部锁定(入队)(latches and internal locks(enqueues))
锁存器是在极短时间内，例如用于修改内存里数据结构的时间内保持的锁定，他们用来保护某些内存结构。
入队是另外一个复杂的串行化设备，例如在更新数据库表中的行时使用，它们与锁存器的区别在于他们允许请求者排队等待资源，使用锁存器请求，请求者立刻被告知他们是否得到了锁存器，使用入队，请求者将被阻塞，直到实际地获得它为止。它速度不如锁存器，但是提供的功能在锁存器功能之上。入队可以在不同的等级获得。

上面是概念，但是还是不能完全理解latch 和enquenue，不知道有没有实践的例子可以展示。或者论坛上已经有的很细的帖子，给个连接也可以。