锁的类型：

 

1：表锁：锁表的结构  TM

2：行锁：锁表上的行  TX

 

v$mystat中的sid  跟 v$session视图中的sid相等

 

当修改一张表的时候既需要保护元数据（表），也需要保护数据（行）

 

DML statements automatically acquire both table-level locks and row-level locks。

 

If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

 

锁的类型

 

select * from v$lock_type  where type  in ('TM','TX') 

 

注意： Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE在日常中最常用的是TM、TX 这两个锁

          v$lock中的lmode 、request、和block

 

SQL> select * from t;

 

     ID NAME              AGE     SALARY

---------- ---------- ---------- ----------

   1273999 a                  20       1000

   1273999 b                  30       1000

 

SQL> update t set name='c' where age=20;

 

1 row updated.

 

SQL> select distinct sid from v$mystat;

 

       SID

----------

        36

 

查看视图

 

select * from v$lock where sid in (36)

 

找到 type 为 TM 的这行数据，查看该行的ID1=75541  ,  然后再 结合 dba_objects 这个视图的 object_id=75541 ，找到被锁的表名

select * from dba_objects  where object_id=75541

 

 

在视图 v$transaction 中记录了与 TX 有关的信息

 XIDUSN   XIDSLOT    XIDSQN

 

结论如下：

 

1：select 查询不需要锁

2：锁定数据只有一种排他锁 exclusive (6 row)  ---TX

3：锁定元数据 mentadata （3 table）---TM

4：要想锁住数据，必须先锁定元数据

 

http://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm 

 

介绍了表锁的类

 

表锁包含5中模式

2  RS： row share

3  RX： row exclusive

4  S ： share

5  SRX：share row exclusive

6  X ： exclusive

 

日常中 DML 操作，在表上都是加的 RX锁 （3）

 

A transaction acquires an exclusive row lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.

 

注意： v$session 中的 sql_id 等于 v$sql中的sql_id

 

可以通过 v$session 中的 ROW_WAIT_OBJ# 、 ROW_WAIT_FILE# 、ROW_WAIT_BLOCK# 和 ROW_WAIT_ROW# 构造出 阻塞会话具体操作的行

      

SQL> select dbms_rowid.rowid_create(1,11756,4,16,2) from dual;

 

DBMS_ROWID.ROWID_C

------------------

AAAC3sAAEAAAAAQAAC

 

SQL> select * from 表名 where rowid='AAAC3sAAEAAAAAQAAC';

 

表名可以通过  ROW_WAIT_OBJ#  关联 dba_objects 视图中的  data_object_id  得到

 

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

 Deadlocks

A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Figure 13-3 is a hypothetical illustration of two transactions in a deadlock.

In Figure 13-3, no problem exists at time point A, as each transaction has a row lock on the row it attempts to update. Each transaction proceeds without being terminated. However, each tries next to update the row currently held by the other transaction. Therefore, a deadlock results at time point B, because neither transaction can obtain the resource it needs to proceed or terminate. It is a deadlock because no matter how long each transaction waits, the conflicting locks are held.

Figure 13-3 Two Transactions in a Deadlock

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

Deadlock Detection

Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks. A corresponding message also is returned to the transaction that undergoes statement-level rollback. The statement rolled back is the one belonging to the transaction that detects the deadlock. Usually, the signalled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.