Oracle锁机制和锁争用问题

最新推荐文章于 2024-06-16 11:47:28 发布

cldh1492

最新推荐文章于 2024-06-16 11:47:28 发布

阅读量561

点赞数

文章标签：数据库数据结构与算法

一、Oracle数据库的锁类型

根据保护对象的不同，Oracle数据库锁可以分为以下几大类：

1、DML锁

数据锁，用于保护并发情况下的数据完整性。

insert、update、delete操作时锁定表中数据，可以是表级别的或行级别的，是隐含的（implicit）、DML操作时Oracle自动添加的。

2、DDL锁

字典锁，用于保护数据库对象的结构。

创建、修改、删除表时在表上施加的锁，始终是表级别的，是隐含的（implicit）、DDL操作时Oracle自动添加的。

3、锁存器（latch）

用以保护数据库实例的内存结构。

Oracle数据库中有两种基本的锁模式：

1、独占锁（exclusive locks，或称排它锁，即X锁）

其它事务不能读取和修改。

2、共享锁（share locks，即S锁）

可被其它事务读取，但不能修改。

Oracle还提供了两种显示的锁（explicit），即可以人工施加的锁控制：

1、select ... for update [nowait] | [wait n]语句

在选中行上施加行独占锁，在未选中行上施加行共享锁。

2、lock table ... in <lockmode> mode [nowait]

以指定的模式锁定一个或多个表、表分区等。

nowait：如果不能立即获得申请的锁，则系统不会等待，而是立刻返回一个错误提示信息。

lockmode的几种锁模式取值：

1）row share

允许对锁定表的并发访问，但禁止其他用户以排它方式锁定整个表。

2）row exclusive

与row share相似，不同的是它禁止其它共享模式的锁。当其他用户执行DML操作时，也能同时获得该模式的锁。

3）share

允许并发查询，但禁止对锁定表的数据更新。

4）exclusive

仅允许在锁定表上执行查询操作，禁止执行其它操作。

5）share row exclusive

相当于同时施加share模式和row exclusive模式，此时禁止对锁定表的数据更新。

二、有关锁的初始化参数

ddl_wait_for_locks

10g动态参数，用于控制DDL语句是否需要等待锁，默认为false，即为nowait方式不等待锁，为true时表示会等待锁，11g不再用此参数。

ddl_lock_timeout

11g动态参数，该值若为0，则当DDL语句访问的对象正在执行DML语句时，会立即报错ORA-00054：资源正忙，但指定以NOWAIT方式获取资源。当该值为N时，DDL等待DML N秒。

dml_locks

静态参数，用于设定DML(insert、update、delete）锁的最大个数。取值范围为20到无限。默认值为transactions初始化参数（默认272）×4，即1088，

表示默认情况下一个事务平均涉及4个DML锁。

enqueue_resources

静态参数，从参数sessions派生而来，用于指定锁队列中可以并发锁定的资源数，10g以后不再有此参数。

三、有关锁的数据字典

1、v$session

记录会话及锁的信息

2、v$session_wait

记录会话的等待信息

3、v$lock

锁信息

4、v$locked_object

当前被锁对象的信息

5、dba_locks

对V$lock的格式化视图

6、dba_blockers

正在阻塞资源的会话

7、dba_waiters

正在等待锁资源的会话

8、v$latch

锁存器信息

9、v$latch_misses

latch争用的丢失统计

四、事务的隔离级别

1、read committed

读提交，oracle默认的事务隔离级别，事务只会读取其它会话已经提交的数据，不会脏读。

2、serializable

序列化或串行化，事务看起来象是一个接一个的顺序执行，仅能看见在本事务开始前由其它事务提交的更改和在本事务中所做的更改。如果在序列化事务期间有其它事务对该序列化事务要修改的东西进行了修改并提交，则会发生无法序列化访问的错误：ORA-08177: 无法连续访问此事务处理。

3、read-only

只读，仅能看见在本事务开始前由其它事务提交的更改，但不允许在本事务中进行DML操作。是serializable的子集。

设置事务的隔离级别

set transaction isolation level read committed;

set transaction isolation level serializable;

set transaction read only;

set transaction read write;

set transaction必须是事务处理的第一个语句，事务未提交或回滚前不能切换。

设置会话级的事务隔离级别，只有以下两种选择，也必须是事务处理的第一个语句

alter session set isolation_level = serializable;

alter session set isolation_level = read committed;

五、数据访问过程中的加锁

我们分别用会话session1、session2、session3登录来做测试：

1）数据库连接与会话信息

session1:

sqlplus cmes/cmes@mes

select sys_context('userenv','sessionid') from dual;

SYS_CONTEXT

-----------

330036

session2:

sqlplus rmes/rmes@mes

select sys_context('userenv','sessionid') from dual;

SYS_CONTEXT

-----------

330038

session3:

sqlplus / as sysdba

查询两个会话的基本信息

col username for a10

col machine for a30

col program for a20

col lockwait for a20

select sid, serial#, audsid, username, status, machine, program, logon_time, lockwait, row_wait_obj# from v$session where audsid in (330036, 330038);

SID SERIAL# AUDSID USERNAME STATUS MACHINE PROGRAM LOGON_TIME LOCKWAIT ROW_WAIT_OBJ#

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

133 8 330036 CMES INACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 14:16:38 13482

197 28 330038 RMES INACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 14:17:30 -1

2）共享锁与行独占锁

session1:

lock table rmes.r_wip_tracking_t in share mode;

session2:

update rmes.r_wip_tracking_t set outline_flag=1 where sn='Z51AA5CTF1090063C';

此更新被卡住，未能执行

session3:

查询锁阻塞和锁请求的基本信息（谁阻塞了谁）

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;

HOLDSID WAITSID TY ID1 ID2 CTIME

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

133 197 TM 97125 0 69

HOLDSID：锁阻塞会话的SID

WAITID：锁请求会话的SID

TYPE：锁类型，TM-DML锁，TX-DDL锁, UL-用户定义的锁类型

ID1：当锁类型为TM锁时，为被锁对象的object_id

ID2：当锁为TX锁时，ID1为usn+slot，而ID2为seq

CTIME：锁持续的时间

进一步查询锁阻塞和锁请求的详细信息

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') lmode,

decode(request, 0, 'none', 1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') request,

ctime, block from v$lock where type in ('TM', 'TX');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

133 TM 97125 0 share none 351 1

197 TM 97125 0 none row exclusive 335 0

lmode：锁占用类型

request：锁请求类型

block：阻塞者标志

此时再看两个会话的基本信息

col username for a10

col machine for a30

col program for a20

col lockwait for a20

select sid, serial#, audsid, username, status, machine, program, logon_time, lockwait, row_wait_obj# from v$session where sid in (133，197);

SID SERIAL# AUDSID USERNAME STATUS MACHINE PROGRAM LOGON_TIME LOCKWAIT ROW_WAIT_OBJ#

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

133 8 330036 CMES INACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 14:16:38 -1

197 28 330038 RMES ACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 14:17:30 000007FF47C507E8 97125

status：inactive，表示命令执行已完成正处于就绪状态，active，表示命令正在执行中

lockwait：锁等待的地址

row_wait_object#：出现行等待对象的ID

可以看到行等待的对象

select object_name from dba_objects where object_id=97125;

OBJECT_NAME

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

R_WIP_TRACKING_T

3）行共享模式与行独占锁

session1:（SID=68）

lock table rmes.r_wip_tracking_t in row share mode;

session2:（SID=12）

update rmes.r_wip_tracking_t set outline_flag=1 where sn='Z51AA5CTF1090063C';

已更新 1 行。

session3:

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;

未选定行

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') lmode,

decode(request, 0, 'none', 1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') request,

ctime, block from v$lock where type in ('TM', 'TX');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

12 TM 97125 0 row exclusive none 256 0

68 TM 97125 0 row share none 298 0

12 TX 196619 1498 exclusive none 256 0

第一个会话持有行共享锁，此时允许第二个会话对同一对象施加行独占锁，与此同时，Oracle自动施加了独占模式的TX锁。

4）行独占模式和行独占锁

session1:（SID=68）

lock table rmes.r_wip_tracking_t in row exclusive mode;

session2:（SID=12）

update rmes.r_wip_tracking_t set outline_flag=1 where sn='Z51AA5CTF1090063C';

已更新 1 行。

session3:

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;

未选定行

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') lmode,

decode(request, 0, 'none', 1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') request,

ctime, block from v$lock where type in ('TM', 'TX');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

12 TM 97125 0 row exclusive none 2815 0

68 TM 97125 0 row exclusive none 649 0

12 TX 196619 1498 exclusive none 2815 0

行独占模式下，允许第二个会话对同一对象施加行独占锁，与此同时，Oracle自动施加了独占模式的TX锁。

5）共享行独占模式和行独占锁

session1:（SID=68）

lock table rmes.r_wip_tracking_t in share row exclusive mode;

session2:（SID=133）

update rmes.r_wip_tracking_t set outline_flag=1 where sn='Z51AA5CTF1090063C';

此更新被卡住，未能执行

session3:

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;

HOLDSID WAITSID TY ID1 ID2 CTIME

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

68 133 TM 97125 0 122

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') lmode,

decode(request, 0, 'none', 1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') request,

ctime, block from v$lock where type in ('TM', 'TX');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

133 TM 97125 0 none row exclusive 184 0

68 TM 97125 0 share row exclusive none 220 1

此时再看两个会话的基本信息

col username for a10

col machine for a30

col program for a20

col lockwait for a20

select sid, serial#, audsid, username, status, machine, program, logon_time, lockwait, row_wait_obj# from v$session where sid in (68，133);

SID SERIAL# AUDSID USERNAME STATUS MACHINE PROGRAM LOGON_TIME LOCKWAIT ROW_WAIT_OBJ#

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

68 399 330306 CMES INACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 18:05:30 -1

133 288 330304 RMES ACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 18:05:02 000007FF47C365C8 97125

可以看到行等待的对象

select object_name from dba_objects where object_id=97125;

OBJECT_NAME

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

R_WIP_TRACKING_T

6）独占模式和行独占锁

session1:（SID=68）

lock table rmes.r_wip_tracking_t in exclusive mode;

session2:（SID=12）

update rmes.r_wip_tracking_t set outline_flag=1 where sn='Z51AA5CTF1090063C';

此更新被卡住，未能执行

session3:

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;

HOLDSID WAITSID TY ID1 ID2 CTIME

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

68 12 TM 97125 0 81

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') lmode,

decode(request, 0, 'none', 1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') request,

ctime, block from v$lock where type in ('TM', 'TX');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

12 TM 97125 0 none row exclusive 224 0

68 TM 97125 0 exclusive none 245 1

此时再看两个会话的基本信息

col username for a10

col machine for a30

col program for a20

col lockwait for a20

select sid, serial#, audsid, username, status, machine, program, logon_time, lockwait, row_wait_obj# from v$session where sid in (68，12);

SID SERIAL# AUDSID USERNAME STATUS MACHINE PROGRAM LOGON_TIME LOCKWAIT ROW_WAIT_OBJ#

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

12 241 330322 RMES ACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 18:19:31 000007FF47C24F08 97125

68 399 330306 CMES INACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 18:05:30 -1

可以看到行等待的对象

select object_name from dba_objects where object_id=97125;

OBJECT_NAME

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

R_WIP_TRACKING_T

7）for update和行独占锁

session1:（SID=133）

select * from rmes.r_wip_tracking_t where sn in ('Z51AA5CTF1090063C','Z51AA5CTF1090064D') for update;

session2:（SID=12）

update rmes.r_wip_tracking_t set outline_flag=1 where sn='Z51AA5CTF1090063C';

此更新被卡住，未能执行

session3:

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;

HOLDSID WAITSID TY ID1 ID2 CTIME

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

133 12 TX 131084 1386 82

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') lmode,

decode(request, 0, 'none', 1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') request,

ctime, block from v$lock where type in ('TM', 'TX');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

12 TX 131084 1386 none exclusive 154 0

12 TM 97125 0 row exclusive none 154 0

133 TM 97125 0 row exclusive none 203 0

133 TX 131084 1386 exclusive none 203 1

此时再看两个会话的基本信息

col username for a10

col machine for a30

col program for a20

col lockwait for a20

select sid, serial#, audsid, username, status, machine, program, logon_time, lockwait, row_wait_obj# from v$session where sid in (133，12);

SID SERIAL# AUDSID USERNAME STATUS MACHINE PROGRAM LOGON_TIME LOCKWAIT ROW_WAIT_OBJ#

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

12 243 330335 RMES ACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 18:28:37 000007FF4AC56980 97125

133 348 330343 CMES INACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 18:32:02 -1

可以看到行等待的对象

select object_name from dba_objects where object_id=97125;

OBJECT_NAME

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

R_WIP_TRACKING_T

六、量测锁争用

Oracle还提供了两个特别的视图，获取正在阻塞和等待锁资源的会话信息，它们不是Oracle数据字典的标配，如果没有，可以以sysdba身份运行脚本$ORACLE_HOME/rdbms/admin/catblock.sql来创建它们。

1、dba_blockers

Name Type Nullable Default Comments

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

HOLDING_SESSION NUMBER Y

2、dba_waiters

Name Type Nullable Default Comments

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

WAITING_SESSION NUMBER Y

HOLDING_SESSION NUMBER Y

LOCK_TYPE VARCHAR2(26) Y

MODE_HELD VARCHAR2(40) Y

MODE_REQUESTED VARCHAR2(40) Y

LOCK_ID1 NUMBER Y

LOCK_ID2 NUMBER Y

用法演示：

session1:（SID=133）

select * from rmes.r_wip_tracking_t where sn in ('Z51AA5CTF1090063C','Z51AA5CTF1090064D') for update;

session2:（SID=9）

update rmes.r_wip_tracking_t set outline_flag=1 where sn='Z51AA5CTF1090063C';

此更新被卡住，未能执行

session3:

select * from dba_blockers;

HOLDING_SESSION

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

133

此时显示有一个锁出现，进一步查

select waiting_session, holding_session, mode_held, mode_requested from dba_waiters;

WAITING_SESSION HOLDING_SESSION MODE_HELD MODE_REQUESTED

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

9 133 Exclusive Exclusive

表明会话133锁住了会话9，锁阻塞和锁请求都是exclusive的。

七、常见锁的处理

当系统中有多个会话对某个数据库表进行更新操作时，一个会话修改后长时间不提交，而另一个会话又要对其修改，则会出现会话被锁住的情况。

1、可通过下列三种手段快速判断是否存在会话阻塞

1）查询所有会话的块状态，确定是否有锁阻塞的会话

select sid from v$lock where block=1;

SID

----------

133

2）通过视图dba_blockers查看是否有锁阻塞的会话

select * from dba_blockers;

HOLDING_SESSION

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

133

3）通过视图dba_waiters查看等待锁资源的会话及会话的阻塞关系

select waiting_session, holding_session, mode_held, mode_requested from dba_waiters;

WAITING_SESSION HOLDING_SESSION MODE_HELD MODE_REQUESTED

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

200 133 Exclusive Exclusive

2、从被锁对象的角度入手来判断会话的阻塞关系

1）通过v$locked_object查询被锁住的对象及在该对象上建立的会话

select object_id, session_id, oracle_username, os_user_name, locked_mode from v$locked_object;

OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME LOCKED_MODE

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

97125 9 RMES ORACLE11G-2\Administrator 3

97125 133 CMES ORACLE11G-2\Administrator 3

2）查看被锁对象的信息

col object_name for a30

col owner for a10

select object_id, object_name, object_type, owner from dba_objects where object_id = 97125;

OBJECT_ID OBJECT_NAME OBJECT_TYPE OWNER

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

97125 R_WIP_TRACKING_T TABLE RMES

3）查看会话的阻塞关系是谁锁住了谁

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;

HOLDSID WAITSID TY ID1 ID2 CTIME

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

133 9 TX 262154 1227 1005

4）可进一步查看锁阻塞和锁请求的信息

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') lmode,

decode(request, 0, 'none', 1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') request,

ctime, block from v$lock where type in ('TM', 'TX');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

9 TX 262154 1227 none exclusive 1127 0

133 TM 97125 0 row exclusive none 1150 0

9 TM 97125 0 row exclusive none 1127 0

133 TX 262154 1227 exclusive none 1150 1

3、通过SID查看相关会话的信息

col username for a10

col machine for a30

col program for a20

col lockwait for a20

select sid, serial#, audsid, username, status, machine, program, logon_time, lockwait, row_wait_obj# from v$session where sid in (133，9);

-1

SID SERIAL# AUDSID USERNAME STATUS MACHINE PROGRAM LOGON_TIME LOCKWAIT ROW_WAIT_OBJ#

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

9 54 330462 RMES ACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 20:14:48 000007FF4AC55E08 97125

133 354 330460 CMES INACTIVE WORKGROUP\ORACLE11G-2 sqlplus.exe 2016-10-30 20:14:12 -1

4、也可以将几个视图联合查询，同时找出被锁对象及其会话信息

select t3.row_wait_obj#, t2.object_name, t2.object_type, t2.owner, t1.os_user_name, t1.session_id, t3.serial#, t3.audsid, t3.username, t3.logon_time, t3.machine, t3.program, t3.status

from v$locked_object t1 join dba_objects t2 on t1.object_id = t2.object_id join v$session t3 on t1.session_id = t3.sid;

5、提交持锁事务或杀掉会话

alter system kill session '133, 354';

八、死锁实验

当Oracle检测到死锁时，中断并回滚执行死锁的相关语句，报ORA-00060的死锁错误并记录在警告日志文件alert_<sid>.log中，同时会在user_dump_dest下产生一个跟踪文件，详细描述死锁的相关信息。

session1:

sqlplus scott/tiger@mes

session2:

sqlplus scott/tiger@mes

session1:

update emp set sal=1000 where empno=7369;

session2:

update emp set sal=1500 where empno=7499;

session1:

update emp set sal=1700 where empno=7499;

session2:

update emp set sal=900 where empno=7369;

session1提示：ORA-00060: 等待资源时检测到死锁

查询锁阻塞和锁请求信息

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;

HOLDSID WAITSID TY ID1 ID2 CTIME

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

200 9 TX 458773 1222 788

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') lmode,

decode(request, 0, 'none', 1, 'null', 2, 'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') request,

ctime, block from v$lock where type in ('TM', 'TX');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

9 TX 458773 1222 none exclusive 793 0

9 TM 73196 0 row exclusive none 842 0

200 TM 73196 0 row exclusive none 856 0

9 TX 655380 1237 exclusive none 842 0

200 TX 458773 1222 exclusive none 856 1

显示session2（SID=9）对表施加的TX exclusive模式的锁请求被session1（SID=200）的TX exclusive锁阻塞。

查看警告日志文件alert_mes.log，提示在跟踪文件中记录了有关死锁的详细信息：

ORA-00060: Deadlock detected. More info in file c:\oracle\diag\rdbms\mes\mes\trace\mes_ora_2252.trc.

session1提交会话：

SQL> commit;

此时查empno=7499的值保持原样，没有被更新，即第二个更改被回滚，而empno=7369的值被更新。

解除死锁的方法有四种：

1）收到死锁信息的session1提交事务

2）收到死锁信息的session2回滚事务

3）DBA通过alter system kill session命令强制中断引发死锁的session2

4）DBA通过alter system kill session命令强制中断阻塞session2操作的session1

比较上面的四种处理方法，最好的还是方法一，因为它导致的数据丢失量最小，最大限度的保护了数据。

九、锁存器机制（latch）

latch的作用

用来保护对Oracle内存结构的访问，是一种特殊类型的锁，采用低层次的序列化技术，用以保护SGA中的共享数据结构。Oracle通过锁存器机制保证没有任何两个进程可以同时访问同一块数据结构。

latch按请求方式分为两种类型：

等待型（willing-to-wait）：请求的进程通过数次短时间的等待后再次发出请求，直到获得latch控制权。

立即型（immediate）：请求的进程如果未能获得latch控制权，则不等待，继续处理其它指令。

DBA无法对latch直接控制，也无相关的初始化参数可以调配。

从性能优化的角度看，DBA需要注意锁存器的争用情况，如果latch存在竞争，表明SGA的一部分正在经历不正常的资源使用。

视图v$latch记录了等待型和立即型两类锁存器的活动。以下查询可以检查latch争用情况，锁存器是否存在等待

select name, gets, misses, wait_time from v$latch where misses <> 0 order by wait_time desc;

NAME GETS MISSES WAIT_TIME

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

qmn task queue latch 4451 91 15593

client/application info 14959 1 5912

row cache objects 2044367 2385 2128

shared pool 504868 1517 1753

cache buffers chains 4627785 286 908

call allocation 13928 88 639

In memory undo latch 54320 7 405

messages 294304 159 378

resmgr:free threads list 4023 45 118

space background task latch 22190 4 11

active service list 68540 55 0

OS process allocation 17729 2 0

mostly latch-free SCN 15681 4 0

lgwr LWN SCN 15671 1 0

active checkpoint queue latch 17869 1 0

session idle bit 15749 8 0

redo allocation 60408 19 0

dummy allocation 3781 3 0

sequence cache 2832 2 0

session allocation 9522 1 0

JS slv state obj latch 6440 21 0

process allocation 3849 1 0

channel handle pool latch 2865 2 0

resmgr group change latch 3914 1 0

FOB s.o list latch 3271 2 0

enqueues 513514 21 0

enqueue hash chains 587384 7 0

从以上wait_time（微秒）的结果可以看到系统主要存在的锁存器争用。

另外通过v$system_event检查系统事件latch free是否出现来判断是否存在锁存器争用

select event, total_waits, time_waited from v$system_event where event='latch free';

EVENT TOTAL_WAITS TIME_WAITED

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

latch free 85 2

以上显示自实例启动以来，经历了与锁存器有关的等待事件次数和消耗的等待时间（毫秒）。

以下统计latch争用导致的各类未命中：

select parent_name, sum(longhold_count) from v$latch_misses group by parent_name having sum(longhold_count) > 0 order by sum(longhold_count) desc;

PARENT_NAME SUM(LONGHOLD_COUNT)

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

In memory undo latch 3

cache buffers chains 3

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28974745/viewspace-2148441/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28974745/viewspace-2148441/

cldh1492

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle锁机制和锁争用问题

一、Oracle数据库的锁类型根据保护对象的不同，Oracle数据库锁可以分为以下几大类： 1、DML锁数据锁，...
复制链接

扫一扫