Oracle锁机制和锁争用问题[转]

最新推荐文章于 2021-04-16 06:35:12 发布

Lissen_Gan

最新推荐文章于 2021-04-16 06:35:12 发布

阅读量550

点赞数

分类专栏： Oracle 文章标签： oracle

原文链接：http://blog.itpub.net/28974745/viewspace-2148441/

版权

Oracle 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

一、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 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 $l o c k 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 $l o c k 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 $l o c k 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 $l o c k 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 $l o c k 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 $l o c k 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

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

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

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

select * from dba_blockers;

HOLDING_SESSION

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 $l o c k 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;

计算机生成了可选文字:
ROWW刘汀OBJ幸
一1
766()7
OBJECTNAME
RWIpTRACKINGT
RWIpTRACKINGT
OBJECTTYpE
TABLE
下ABL〔
OWNER
RMES
RMES
05USERNAME
Or刁Cle
Dr己Cle
SESSIONID
132
1O
SERIA以｝AuDSID}USERNAME
46480728CMES
28480735RMES
LOGON刀ME
2016八0月19:41:24
加16八O/319:44:51
MACHINE
Or刁ClelinUx
orac!e!inux
pROGRAM
sqlplus@oraclelinux任NS竹一V3)
sqlplus@oraclelinux仃NS牡一妇）
STATUS
INACTWE
ACTWE

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

alter system kill session ‘133, 354’;

八、死锁实验

当Oracle检测到死锁时，中断并回滚执行死锁的相关语句，报ORA-00060的死锁错误并记录在警告日志文件alert_.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 $l o c k 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

Lissen_Gan

关注

0
点赞
踩
3

收藏

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

一、Oracle数据库的锁类型根据保护对象的不同，Oracle数据库锁可以分为以下几大类：1、DML锁数据锁，用于保护并发情况下的数据完整性。insert、update、delete操作时锁定表中数据，可以是表级别的或行级别的，是隐含的（implicit）、DML操作时Oracle自动添加的。2、DDL锁字典锁，用于保护数据库对象的结构。创建、修改、删除表时在表上施加的锁，始终是表级...
复制链接

扫一扫