并发管理

一丶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 the ALTER TABLE statement	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:    [e-mail]

Blog:      http://blog.csdn.net/guoyJoe

ITPUB:   http://www.itpub.net/space-uid-28460966.html

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

答案在：http://blog.csdn.net/guoyjoe/article/details/8624392

Oracle@Paradise 　总群:127149411

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

Oracle@Paradise No.2群:121341761

Oracle@Paradise No.3群:140856036