oracle并发管理,并发管理-Focus on Oracle-51CTO博客

一、Oracle的锁

锁是Oracle管理共享数据库资源并发访问并防止并发数据库事务之间“相互干涉”的核心机制。

1、锁的类型

(1)DML锁：允许并发执行数据修改，特定数据行上的锁或表中的所有行的锁。

(2)DDL锁：保护对象结构定义。

(3)Latch锁：这是Oracle内部锁，用来协调对期共享数据结构的访问。

2、LOCK锁的模式

(1)空锁(NULL)

(2)排它锁(X)

(3)共享锁(S)

二、DML锁

1、TX锁：修改数据的事务在执行期间会获得这种锁。

TX锁的模式：排它锁(X)

TX锁和行级锁:TX锁不是行锁，一个事务不管修改了多少行，都只会有一个TX锁。TＸ锁算是行锁的代表，行锁上发生了等待，会表　　　　现为TX锁的等待。行锁是属于事务的，事务开始，行锁产生，事务结束，行锁也被释放。

两个会话时DML操作同一行数据，会产生阻塞，操作如下：

会话125号，更新id=1这行数据。

gyj@OCM> select distinct sid from v$mystat;

SID

----------

125

gyj@OCM> update t5 set name='aaaaaa' where id=1;

1 row updated.

事务未提交！！

会话145，同时也更新id=1这行数据。

gyj@OCM> select distinct sid from v$mystat;

SID

----------

145

gyj@OCM> update t5 set name='bbbbb' where id=1;

这里被阻塞了！

查v$lock视图

sys@OCM>select TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from v$lock where sidin(125,145);

TY        ID1        ID2     LMODE    REQUEST      CTIME     BLOCK

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

AE        100          0          4          0       1647          0

AE        100          0          4          0        682          0

TX      65539       2978          0          6         77         0

TO      65908          1          3          0        676          0

TM      74709          0          3          0         77          0

TM      74709          0          3          0        206          0

TX      65539       2978          6          0       206          1

TX锁的标识：TX-ID1-ID2 --ID1表示事务使用的回滚段编号以及在事务表中对应的记录编号，ID2表示该记录编号被重用的次数(wrap)

将ID1拆解:ID1是由4个字节组成的，高位2个字节和低位2个字节分别表示v$trasaction中的xidusn,xidslot

selecttrunc(id1/power(2,16)) as undo_blk#,bitand(id1,to_number('ffff','xxxx')) + 0 asslot# from dual;

另一计算方法：selectto_char(id1,'xxxxxxx') from dual;

查询持有锁和请求锁的信息:v$lock

gyj@OCM> desc v$lock

名称是否为空?类型

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

ADDR              RAW(4)：锁状态对象的地址V$transaction.addr  V$session.taddr这个其实是事务地址

KADDR   RAW(4)：锁的地址。此列是真正的锁地址。V$session.lockwait

SID          NUMBER：持有或获得锁的会话

TYPE                VARCHAR2(2)：用户或系统锁的类型

ID1                   NUMBER：锁的标识

ID2                   NUMBER：锁的标识

LMODE           NUMBER：会话持有的锁的模式，0 - none、1 - null(NULL)、2 - row-S (SS)、3 - row-X (SX)、4 - share (S)、5 - S/Row-X (SSX)、6 - exclusive (X)

REQUEST        NUMBER：进程正在请求锁的模式，模式类型同上。

CTIME             NUMBER：当前模式持续的时间

BLOCK             NUMBER：当前锁是否正在阻塞其他的锁(1：是，0：不是)

系统进程也可能持有锁，系统锁被持有很短的时间，系统锁有如下类型： System Type Description System Type Description AT Lock held for theALTER TABLEstatement NA..NZ Library cache pin instance (A..Z= namespace)

BL Buffer hash table instance PF Password File

CF Control file schema global enqueue PI, PS Parallel operation

CI Cross-instance function invocation  instance PR Process startup

CU Cursor bind QA..QZ Row cache instance (A..Z= cache)

DF datafile instance RT Redo thread global enqueue

DL Direct loader parallel index create SC System change number instance

DM Mount/startup db primary/secondary  instance SM SMON

DR Distributed recovery process SN Sequence number instance

DX Distributed transaction entry SQ Sequence number enqueue

FS File set SS Sort segment

HW Space management operations on a  specific segment ST Space transaction enqueue

IN Instance number SV Sequence number value

IR Instance recovery serialization global  enqueue TA Generic enqueue

IS Instance state TS Temporary segment enqueue (ID2=0)

IV Library cache invalidation instance TS New block allocation enqueue (ID2=1)

JQ Job queue TT Temporary table enqueue

KK Thread kick UN User name

LA .. LP Library cache lock instance lock (A..P =  namespace) US Undo segment DDL

MM Mount definition global enqueue WL Being-written redo log instance

MR Media recovery

2、TM锁：表级锁

TM锁的主要作用，在DML期间，防止对表的DDL操作，可以确保对象的结构不被修改，例如，如果你已经更新了一个表，会得到这个表的一个TM锁。这会防止另一个用户在该表上执行DROP或ALTER命令。如果你有表的一个TM锁，而另一个用户试图在这个表上执行DDL，操作如下，他不会得到以下错误消息：

会话一

gyj@OCM> select distinct sid from v$mystat;

SID

----------

125

gyj@OCM> delete from t5 where id=1;

1 row deleted.

事务没提交！

会话二：

gyj@OCM> drop table t5;

drop table t5

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified ortimeout expired

查v$lock视图

gyj@OCM> select TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK fromv$lock where sid=125;

TY        ID1        ID2     LMODE    REQUEST      CTIME     BLOCK

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

AE        100          0          4          0       1171          0

TM      74709          0          3          0        241          0

TX     196641       3996          6          0        241          0

锁的标识：TM-ID1-ID2   --ID1表示被锁定的对象的对象ID，ID2始终为0

TM锁的模式： 代码 锁模式 表 行

0-None 没有锁 － －

1-Null 空锁 － －

2-SS(RS) 行级共享锁。Row Share － 共享

3-SX(RX) 行级排它锁。Row Exclusive － 排他

4-S 表共享锁Share 共享 －

5-SSX(SRX) 表共享行排它锁。Share Row Exclusive 共享 排他

6-X 表排它锁。Exclusive 排他 －

通过lock tablet10 in row share mode;命令添加RS锁

通过lock tablet10 in row exclusive mode;命令对表添加RX锁定;

通过select …from forupdate命令添加RX锁

通过lock tablet10 in share mode;命令添加该S锁(wait for ITL release)

通过lock tablet10 in share row exclusive mode;命令添加SRX锁

通过lock tablet10 in exclusive mode命令添加X锁

语法：lock table  in [row share][rowexclusive][share][share row exclusive][exclusive] mode;

各种TM锁的兼容性： 持有/得到 1-Null 2-SS(RS) 3-SX(RX) 4-S 5-SSX(SRX) 6-X

1-Null √ √ √ √ √ √

2-SS(RS) √ √ √ √ √

3-SX(RX) √ √ √

4-S √ √ √

5-SSX(SRX) √ √

6-X √

二、DDL锁

(省，单独讲)

三、Latch锁

(省，单独讲)

四、相关查询锁的语句

1、查询持有锁和请求锁的信息:v$lock

sys@OCM> selectsid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'RowExclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')lock_mode,decode(request,0,'None',1,'Null',2,'Row share',3,'RowExclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,blockfrom v$lock where sid in(125,145);

SID TY        ID1        ID2 LOCK_MODE           REQUEST_MODE             BLOCK

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

125 AE        100          0 Share               None                         0

145 AE        100          0 Share               None                         0

145 TX      65539       2978 None                Exclusive                    0

145 TO      65908         1 Row Exclusive       None                         0

145 TM      74709          0 Row Exclusive       None                         0

125 TM      74709          0 Row Exclusive       None                         0

125 TX      65539       2978 Exclusive           None

2、查询请求锁的信息:v$enqueue_lock

sys@OCM> select sid,type,decode(request,0,'None',1,'Null',2,'Row share',3,'RowExclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode fromv$enqueue_lock where sid in(125,145);

SID TY REQUEST_MODE

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

125 AE None

145 AE None

145 TX Exclusive

145 TO None

3、查询请求锁时间过长的信息

sys@OCM> select a.sidblocker_sid,a.serial#,a.username asblocker_username,b.type,decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'RowExclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,b.ctimeas time_held,c.sid as waiter_sid,decode(c.request,0,'None',1,'Null',2,'Rowshare',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')request_mode,c.ctime time_waited from  v$lock b, v$enqueue_lock c, v$session a

2  where  a.sid = b.sid and    b.id1= c.id1(+) and b.id2 = c.id2(+) andc.type(+) = 'TX' and  b.type = 'TX'and  b.block   = 1

3  order by time_held,time_waited;

BLOCKER_SID    SERIAL# BLOCKER_USERNAME               TY LOCK_MODE            TIME_HELD WAITER_SID REQUEST_MODE        TIME_WAITED

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

125          9 GYJ                            TX Exclusive                  605        145 Exclusive            476

把125号会话给KILL掉，操作命令如下：

alter system kill session '125,9';

五、死锁

1、两个session(以A和B来表示)，如果A持有B正在申请的锁定，同时B也持有A正在申请的锁定时，这时发生死锁现象。

操作如下：

session A

update t1 set name='A1' where id=1;

session B

update t1 set name='B1' where id=2;

session A

update t1 set name='B2' where id=2;

session B

update t1 set name='A2' where id=1;

alter_.log

2、实际举个产生死锁的例子

步骤一：通过主外键创建班级代码表和学生基本信息表。

gyj@OCM>  create table bjdmb(bjdm number(8) primary key,bjmc varchar2(20));

Table created.

gyj@OCM> create table xsjbxxb(xh number(10),xm varchar2(10),bjdm number(8),

2  foreign key(bjdm) references bjdmb(bjdm) on delete cascade,primary key(xh));

Table created.

步骤二：加载数据

gyj@OCM> insert into bjdmb values(01,'bj01');

1 row created.

gyj@OCM> insert into bjdmb values(02,'bj02');

1 row created.

gyj@OCM> insert into bjdmb values(03,'bj03');

1 row created.

gyj@OCM> insert into bjdmb values(04,'bj04');

1 row created.

gyj@OCM> commit;

Commit complete.

gyj@OCM> insert into xsjbxxb values(001,'tom',01);

1 row created.

gyj@OCM> insert into xsjbxxb values(002,'joe',02);

1 row created.

gyj@OCM> commit;

Commit complete.

步骤三：产生死锁

会话A，插入学生一条信息记录：

gyj@OCM>insert into xsjbxxb values(00,'joe',03);

1 row created.

会话B：删除4班信息

gyj@OCM> delete from bjdmb where bjdm=04;

会话B这时被阻塞了！！！！！

步骤四：回滚会话A和会话B

会话A回滚：

gyj@OCM> insert into xsjbxxbvalues(00,'joe',03);

1 row created.

gyj@OCM> rollback;

Rollback complete.

会话B回滚：

gyj@OCM> delete from bjdmb wherebjdm=04;

1 row deleted.

gyj@OCM> rollback;

Rollback complete.

步骤五：在外键上创建索引

gyj@OCM>  create index idx_xsjbxxb_bjdm onxsjbxxb(bjdm);

Index created.

步骤六：然后重复执行步骤三的操作：

会话A上操作：

gyj@OCM> insert into xsjbxxbvalues(00,'joe',03);

1 row created.

会话B上操作：

gyj@OCM> delete from bjdmb wherebjdm=04;

1 row deleted.

没有被阻塞！！！！

死锁99%是外键上没加索引原因！！！！！！！！！！！！！

最后总结：外键要不需要加索引的情况

A)没有从交表删除行

B)没有更新父表的惟一键/主键值

C)没有从父表联结子表

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********

Name:    guoyJoe

QQ:        252803295

Email:    oracledba_cn@hotmail.com

OCM:    http://education.oracle.com/education/otn/YGuo.HTM

_____________________________________________________________加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!

Oracle@Paradise 　总群:127149411

Oracle@Paradise No.1群:177089463(已满)

Oracle@Paradise No.2群:121341761

Oracle@Paradise No.3群:140856036