开发多用户、数据库驱动的应用时，最大的难点之一是：一方面要力争取得最大限度的并发访问，与此同时还要确保每个用户能在保证一致性的前提下读取和修改数据，为此就有了锁机制。
锁用于管理对共享资源的并发访问，与此同时还要保证数据完整性和一致性。
v$transaction 视图
第一个视图是 v$transaction，就是 Oracle 数据库所有活动的事务数，每一个活动的事务在这里都有一行记录。
- v$transaction 视图结构
- XIDUSN 当前事务使用的回滚段的编号
- XIDSLOT 该事务在回滚段头部的事务表中对应的记录编号
- XIDSQN 序列号
- STATUS 该事务是否为活动的
在 SCOTT 用户下执行如下语句（注意，未提交）：
SQL> drop table t purge; SQL> create table t as select * from emp; SQL> delete from t where rownum = 1;
上述 SQL 语句执行了 DELETE 操作，相当于开始了一个事务，可以通过 v$transaction 视图去查看该事务（注意，如果有多个事务，该视图会显示多条记录）：
SQL> select xidusn, xidslot, xidsqn, status from v$transaction;
XIDUSN XIDSLOT XIDSQN STATUS
---------- ---------- ---------- ----------------
2 19 10372 ACTIVE
通过关联 v$session 视图，我们可以得到该事务对应的会话信息：
SQL> select
a.sid,
a.username,
b.xidusn,
b.used_urec,
b.used_ublk
from v$session a, v$transaction b
where a.saddr = b.ses_addr;
SID USERNAME XIDUSN USED_UREC USED_UBLK
---------- ------------------------------ ---------- ---------- ----------
7 SCOTT 2 1 1
通过关联 v$sql 视图， 可以得到该事务对应的 SQL 语句：
SQL> select sql_text from v$sql, v$transaction where last_active_time = start_date and xidusn = 2 and xidslot = 19 and xidsqn = 10372 and module = 'SQL*Plus'; SQL_TEXT -------------------------------------------- delete from t where rownum = 1
XIDUSN、XIDSLOT 和 XIDSQN 这三个字段唯一标示一个事务的编号。
v$lock 视图
v$lock 视图记录了会话已经获得的锁定以及正在请求的锁定的信息，就是每个会话，它已经获取的锁和正在申请的锁都会列出来。
| 序号 | 字段名 | 描述 |
| 1 | SID | 会话的 ID |
| 2 | TYPE | 锁的类型，主要关注 TX 和 TM 锁 |
| 3 | LMODE | 已经获得的锁的模式，以数字编码表示 |
| 4 | REQUEST | 正在请求的锁的模式，以数字编码表示 |
| 5 | BLOCK | 是否阻止了其他用户获得锁定，大于 0 说明是，等于 0 说明否 |
| 6 | ID1 | 对于 TM 锁 ID1 表示被锁定表的 object_id；对于 TX 锁 ID1 以十进制数值表示该事务所占用的回滚段号和事务槽号 |
| 7 | ID2 | 对于 TM 锁 ID2 值为 0；对于 TX 锁 ID2 以十进制数值表示环绕 wrap 的次数，即事务槽被重用的次数 |
v$enqueue_lock 视图
v$enqueue_lock 该视图中包含的字段以及字段含义与 v$lock 中的字段一模一样。只不过该视图中只显示那些申请锁定，但是无法获得锁定的 session 信息。其中的记录按照申请锁定的时间先后顺序排列，先申请锁定的 session 排在前面，排在前面的 session 将会先获得锁定。
v$locked_object 视图
v$locked_object 视图列出当前系统中哪些对象正被锁定，其主要字段说明如下：
| 序号 | 字段名 | 描述 |
| 1 | XIDUSN | 回滚段号 |
| 2 | XIDSLOT | 槽号 |
| 3 | XIDSQN | 序列号 |
| 4 | OBJECT_ID | 被锁对象标识 |
| 5 | SESSION_ID | 持有锁的会话标识 |
| 6 | ORACLE_USERNAME | 持有该锁的用户的 Oracle 用户名 |
| 7 | OS_USER_NAME | 持有该锁的用户的操作系统用户名 |
| 8 | PROCESS | 操作系统的进程号 |
| 9 | LOCKED_MODE | 锁模式 |
两个事务的锁争用
打开一个会话，记做 #A，并执行 UPDATE 语句， 开始一个事务：
SQL> drop table t purge; SQL> create table t as select * from emp; SQL> update t set t.ename = t.ename || 'a' where deptno = 10;
因为刚开始一个事务，它是 active 的没有提交，且它在 v$transaction 视图中一定会出现：
SQL> select xidusn, xidslot, xidsqn, status from v$transaction;
XIDUSN XIDSLOT XIDSQN STATUS
---------- ---------- ---------- ----------------
5 5 10400 ACTIVE
获取该会话 #A 的 SID：
SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
7
通过会话 #A 的 SID，查看产生了哪些锁：
SQL> select
sid, type, id1, id2,
decode(lmode, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') lock_mode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') request_mode,
block
from v$lock
where sid = 7 and type in ('TX', 'TM');
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
7 TM 90098 0 Row Exclusive None 0
7 TX 327685 10400 Exclusive None 0
从结果集可以看出，产生了一个 TM 锁和一个 TX 锁。
TM 锁 的 ID2 总是 0，ID1 是代表着操作所在的表，TX 锁的 ID1 和 ID2 通过语句可以找到这个事务，从 LOCK_MODE 看出他们都持有锁， REQUEST_MODE 看出都没有请求锁，从 BLOCK 都是 0 看出持有的锁都没有阻塞别人。
再打开一个会话，记做 #B，先获取会话 SID 再执行同样的 SQL 语句
SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
131
SQL> update t set t.ename = t.ename || 'a' where deptno = 10;
这时会发现，该语句被挂起，也就是请求的资源已经被锁住了。
通过会话 #A 和 会话 #B 的SID，我们在会话 #A 中执行如下语句，查看此时锁的情况：
SQL> select
sid, type, id1, id2,
decode(lmode, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') lock_mode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') request_mode,
block
from v$lock
where sid in (7, 131) and type in ('TX', 'TM');
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
131 TX 65555 10253 None Exclusive 0
131 TM 90098 0 Row Exclusive None 0
7 TM 90098 0 Row Exclusive None 0
7 TX 65555 10253 Exclusive None 1
从结果集可以看出，会话 #A 对应的 SID=7 的 TM 锁和 TX 锁没有变化，SID=7 是先执行的，持有 TM 锁和 TX 锁，后执行的 SID=131 的 TM 锁 LOCK_MODE 为 Row Exclusive。
持有 SID=7 和 SID=131 的 TM 锁的 ID1 列相同，也就是说 SID=7 和 SID=131 都在这个 90098 对象上加了 RX 锁；但是 SID=131 的 TX 锁行的 REQUEST_MODE 的值是 Exclusive，也就是说 SID=131 被 SID=7 这个事务锁住了。
然后看 SID=7 的 TX 这行，BLOCK 列的值是 1 说明阻塞了别人，即阻塞了 SID=131，而 SID=131 TX 的 REQUEST_MODE 列是 Exclusive，标识它正在请求 Exclusive 锁，也就是被锁住了。
当 SID=7 会话一旦提交以后，SID=131 马上就能获取到这个锁了。
三个事务的锁争用
新建一个会话，记做 #A，并获取会话 SID：
SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
7
在会话 #A 执行如下 SQL 语句：
SQL> update t set t.ename = t.ename || 'a' where deptno = 10;
新建一个会话，记做 #B，并获取会话 SID：
SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
131
在会话 #B 执行如下 SQL 语句：
SQL> update t set t.ename = t.ename || 'a' where deptno = 10;
新建一个会话，记做 #C，并获取会话 SID：
SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
72
在会话 #C 执行如下 SQL 语句：
SQL> update t set t.ename = t.ename || 'a' where deptno = 10;
通过会话 #A、会话 #B 和会话 #C 的SID，我们在会话 #A 中执行如下语句，查看此时锁的情况：
SQL> select
sid, type, id1, id2,
decode(lmode, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') lock_mode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') request_mode,
block
from v$lock
where sid in (7, 131, 72) and type in ('TX', 'TM')
order by 1;
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
7 TX 655364 10428 Exclusive None 1
7 TM 90098 0 Row Exclusive None 0
72 TM 90098 0 Row Exclusive None 0
72 TX 655364 10428 None Exclusive 0
131 TX 655364 10428 None Exclusive 0
131 TM 90098 0 Row Exclusive None 0
一个事务多个 TM 锁
一个事务修改多行，产生一个 TX 锁，可以在多个表上产生多个 TM 锁，但一个事务只产生一个 TX 锁。
新建一个会话，记做 #A，并获取会话 SID：
SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
199
执行如下 SQL 语句：
SQL> drop table t1 purege; SQL> drop table t2 purge; SQL> create table t1 as select * from emp; SQL> create table t2 as select * from dept;
在一个事务里面执行多条 UPDATE 语句：
SQL> update t1 set ename = ename || 'a' where deptno = 10; SQL> update t2 set loc = loc || 'b' where deptno = 20;
通过会话 #A 的 SID，查看产生了哪些锁：
SQL> select
sid, type, id1, id2,
decode(lmode, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') lock_mode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') request_mode,
block
from v$lock
where sid = 199 and type in ('TX', 'TM');
SID TY ID1 ID2 LOCK_MODE REQUEST_MODE BLOCK
---------- -- ---------- ---------- ------------------- ------------------- ----------
199 TM 90118 0 Row Exclusive None 0
199 TM 90119 0 Row Exclusive None 0
199 TX 196618 10466 Exclusive None 0
transactions 和 dml_locks 参数
-
transactions 参数表示 Oracle 一个实例最多可有的事务数
-
dml_locks 参数表示一个 Oracle 实例中最多可产生的 TM 锁（表级锁）的数量
SQL> select name, value from v$parameter where name in('transactions', 'dml_locks');
NAME VALUE
-------------------- ------------------------------
dml_locks 1088
transactions 272
SQL> select
resource_name as "R_N",
current_utilization as "C_U",
max_utilization as "M_U",
initial_allocation as "I_U"
from v$resource_limit
where resource_name in('transactions','dml_locks');
R_N C_U M_U I_U
------------------------------ ---------- ---------- ----------
dml_locks 0 28 1088
transactions 0 7 272
R_N 列是资源名字，如 dml_locks 是资源名，C_U 是 current_utilization 表示当前已经使用的数目（当前锁定了 0 个表），M_U 是 max_utilization 最大同时使用的数目（最大锁过 28个），I_U 是 initial_allocation 初始可分配的数量（最大可分配的数量为 1088）。
锁的分类
DML 锁
DML 一般是指 SELECT 、INSERT 、UPDATE 、MERGE 和 DELETE 语句，DML 锁机制允许并发执行数据修改
DML 锁用于确保一次只有一个人能够修改某一行，而且这时别人不能删除这个表
- TX 锁（行级锁）
| 锁模式 | 锁描述 | 解释 | 相关 SQL |
| 6 | Exclusive Table（X） | 表级排它锁 | insert 、update 、delete |
- TM 锁（表级锁）
TM 锁 用于确保在修改表的内容时，表的结构不会改变，例如，如果你已经更新了一个表中的行，那同时也会得到这个表上的一个 TM 锁
TM 锁类型
| 锁模式 | 锁描述 | 解释 | 锁定表的 SQL |
| 0 | None | 不存在锁 | |
| 1 | Null | 空锁，不与其它任何 | |
| 2 | Row Share（RS） | 行级共享锁（其它事务可以对锁定的表进行任何DML操作，还可以与其它会话锁并存） | lock table t in row share mode; |
| 3 | Row Exclusive Table Lock（RX） | 行级排它锁（允许其它事务对锁定的表进行select和DML操作，多个事务同时锁定一张表） | lock table t in row exclusive mode; |
| 4 | Share Table Lock（S） | 表级共享锁（其它事务可以查询锁定的表但不能修改，只允许当前事务修改） | lock table t in share mode; |
| 5 | Share Row Exclusive Table Lock（SRX） | 共享行级排它锁（同一时间只允许一个事务持有和修改锁定的表，其它事务可以查询但不能修改） | lock table t in share row exclusive mode; |
| 6 | Exclusive Table Lock（X） | 表级排它锁（禁止其它事务执行任何类型的DML语句或者锁表，一个表只能有一个6号锁） | lock table t in exclusive mode; |
TM 锁 与 TM 锁 之间的互斥关系：
| 锁模式 | 锁名称 | 允许级别 | 互斥级别 |
| 2 | 行级共享锁 | 2 3 4 5 6 | 6 |
| 3 | 行级排他锁 | 2 3 | 4 5 6 |
| 4 | 表级共享锁 | 2 4 | 3 5 6 |
| 5 | 共享行级排他锁 | 2 | 3 4 5 6 |
| 6 | 排他锁 | 2 3 4 5 6 |
DML 锁总结：
DDL 锁
DDL 一般是指 CREATE 和 ALTER 语句等，DDL 锁可以保护对象结构定义（如表 、索引的结构定义）；
在 DDL 操作中会自动为对象加 DDL 锁，从而保护这些对象不会被其它会话所修改；
在 DDL 语句执行期间会一直持有 DDL 锁，一旦操作执行完毕就立即释放 DDL 锁；
如果需要执行 DDL 语句，但是不想让它提交现有的事务，就可以使用一个自治事务。
- 排他 DDL 锁
当对一个表加了
DDL排他锁的时候，其它会话就不能再加DDL锁或DML锁了，此时可以查询这个表，但无法以任何方式修改这个表。如：alter table 、create table 、drop table 、truncate table等
# 创建测试表 SQL> drop table t purge; SQL> create table t as select * from all_objects; SQL> insert into t select * from t; SQL> commit;
session A：
# alter table 语句 SQL> alter table t drop column subobject_name;
session B：
# 查看 alter table 语句为 T 表所加的锁
SQL> select
(select username from v$session where sid = V$lock.SID) username,
sid,
id1,
id2,
lmode,
request,
block,
v$lock.TYPE
from v$lock where id1 = (select object_id from user_objects where object_name = 'T');
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TY
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- --
OPTIMIZER 22 1924204 0 6 0 0 TM
# alter table 语句新增字段
SQL> alter table t add c_data clob;
alter table t add c_data clob
*
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
# 对 T 表执行 DML 操作（阻塞）
SQL> delete from t where rownum = 1;
- 共享 DDL 锁 其它会话只能获得 DDL 共享锁，不能加 DML 锁，不能修改表结构，但是可以查询和修改表中的数据。如 create view，create index 等。
# 创建测试表 SQL> drop table t purge; SQL> create table t as select * from all_objects; SQL> insert into t select * from t; SQL> commit;
session A：
# create index 语句 SQL> create index idx_t on t(object_id, object_name);
session B：
# 查看 alter table 语句为 T 表所加的锁
SQL> select
(select username from v$session where sid = V$lock.SID) username,
sid,
id1,
id2,
lmode,
request,
block,
v$lock.TYPE
from v$lock where id1 = (select object_id from user_objects where object_name = 'T');
USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TY
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- --
OPTIMIZER 22 1924213 0 4 0 0 TM
# alter table 语句新增字段
SQL> alter table t add c_data clob;
alter table t add c_data clob
*
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
# 对 T 表执行 DML 操作（阻塞）
SQL> delete from t where rownum = 1;
死锁
如果有两个会话，每个会话都持有另一个会话想要的资源，此时就会出现死锁。
- 死锁的发生
如果数据库中有两个表 A 和 B，每个表中都只有一行记录：
SQL> create table A(id int primary key); SQL> create table B(id int primary key); SQL> insert into A(id) values(1); SQL> insert into B(id) values(1); SQL> commit;
- 在会话
#A中更新表 A
SQL> update a set id = 11;
- 在会话
#B中更新表 B
SQL> update b set id = 11;
- 在会话
#B中更新表 A（会话#B会被阻塞）
SQL> update a set id = 111;
- 在会话
#A中更新表 B（会话#B中出现死锁）
SQL> update b set id = 111;
update a set id = 111
*
第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁
- 死锁的信息
当死锁发生的时候，Oracle 会记录下死锁的信息。
查看日志文件位置：
SQL> show parameter user_dump_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string E:\app\allen\diag\rdbms\orcl\o
rcl\trace
找到对应的日志文件（orcl_ora_17260.trc），查看死锁信息：
*** 2019-05-17 13:37:52.297 DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock] The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-00080018-000028bb 25 72 X 31 199 X TX-00040002-000027f3 31 199 X 25 72 X session 72: DID 0001-0019-0000114A session 199: DID 0001-001F-000004B4 session 199: DID 0001-001F-000004B4 session 72: DID 0001-0019-0000114A
监控锁的相关视图
| 序号 | 视图名 | 描述 |
| 1 | v$session | 查询会话的信息和锁的信息 |
| 2 | v$session_wait | 查询等待的会话信息 |
| 3 | v$lock | 列出系统中的所有的锁 |
| 4 | v$locked_object | 只包含 DML 的锁信息，包括回滚段和会话信息 |
常用命令
- 查询数据库中的锁
SQL> select * from v$lock;
- 查询阻塞的会话
SQL> select * from v$lock where block = 1;
- 查询被锁的对象
SQL> select * from v$locked_object;
- 查询数据库正在等待锁的进程
SQL> select * from v$session where lockwait is not null;
- 查询会话之间锁等待的关系
SQL> select a.sid holdsid, b.sid waitsid, a.type, a.id1, a.id2, a.ctime from v$lock a, v$lock b where a.id1 = b.id1 and a.id2 = b.id2 and a.block = 1 and b.block=0;
- 查找锁住的表和解锁
SQL> select b.owner tableowner, b.object_name tablename, c.osuser lockby, c.username loginid, c.sid sid, c.serial# serial from v$locked_object a,dba_objects b, v$session c where b.object_id = a.object_id and a.session_id = c.sid;
- 通过
SID,SERIAL解锁
SQL> alter system kill session 'SID, SERIAL';
- 通过用户名和表名，查看表上的锁
SQL> select (select username from v$session where sid = v$lock.sid) username, sid, id1, id2, lmode, request, block, v$lock.type from v$lock where id1 = ( select object_id from dba_objects where owner = '&ownname' and object_name = '&table_name' );
原创文章，转载请注明出处：http://www.opcoder.cn/article/26/