事务的四大特性、事务处理开始与结束、v$transactio、 v$LOCK
生活随笔
收集整理的这篇文章主要介绍了
事务的四大特性、事务处理开始与结束、v$transactio、 v$LOCK
小编觉得挺不错的,现在分享给大家,帮大家做个参考.
数据库事务的四大特性：ACID
事务的4大特性（ACID）： 原子性(Atomicity)：事务是数据库的逻辑工作单位，它对数据库的修改要么全部执行，要么全部不执行。一致性(Consistemcy)：事务前后，数据库的状态都满足所有的完整性约束。隔离性(Isolation)：并发执行的事务是隔离的，一个不影响一个。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。通过设置数据库的隔离级别，可以达到不同的隔离效果。持久性(Durability)：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。并发事务引起的问题： 更新丢失：两个事务都同时更新一行数据，但是第二个事务却中途失败退出，导致对数据的两个修改都失效了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。脏读：脏读又称无效数据读出。一个事务读取另外一个事务还没有提交的数据叫脏读。例如：事务T1修改了一行数据，但是还没有提交，这时候事务T2读取了被事务T1修改后的数据，之后事务T1因为某种原因Rollback了，那么事务T2读取的数据就是脏的。不可重复读：不可重复读是指在同一个事务内，两个相同的查询返回了不同的结果。例如：事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。幻读：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据例如：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样。这就叫幻读。事务的隔离级别： 以上的4种问题（更新丢失、脏读、不可重复读、幻读）都和事务的隔离级别有关。通过设置事务的隔离级别，可以避免上述问题的发生。未授权读取级别：也称为读未提交（Read Uncommitted）。以操作同一行数据为前提，读事务允许其他读事务和写事务，未提交的写事务禁止其他写事务（但允许其他读事务）。此隔离级别可以防止更新丢失，但不能防止脏读、不可重复读、幻读。此隔离级别可以通过“排他写锁”实现。授权读取级别：也称为读提交（Read Committed）。以操作同一行数据为前提，读事务允许其他读事务和写事务，未提交的写事务禁止其他读事务和写事务。此隔离级别可以防止更新丢失、脏读，但不能防止不可重复读、幻读。此隔离级别可以通过“瞬间共享读锁”和“排他写锁”实现。可重复读取级别：可重复读取（Repeatable Read）。以操作同一行数据为前提，读事务禁止其他写事务（但允许其他读事务），未提交的写事务禁止其他读事务和写事务。此隔离级别可以防止更新丢失、脏读、不可重复读，但不能防止幻读。此隔离级别可以通过“共享读锁”和“排他写锁”实现。序列化级别：序列化（Serializable）。提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。此隔离级别可以防止更新丢失、脏读、不可重复读、幻读。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免更新丢失、脏读，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。 ---------------------事务处理开始与结束
v$transactio
SQL> show user USER 为 "U1" SQL> update t1 set id = 1 where name = 'a';已更新 1 行。SQL> show user USER 为 "SYS" SQL> select s.username,t.status from v$transaction t,v$session s where t.addr = s.taddr;USERNAME STATUS ------------------------------ ---------------- U1 ACTIVEV$SESSION taddr ,paddr 分别对应 v$transaction addr,V$PROCESS addrv$transaction的used_ublk可以查看进度 used_ublk 在做回滚时有多少块在使用 used_now 现在有多少块在使用 used_later 在60秒后有多少块使用 (used_later /used_ublk - used_later ) = ???多少时间rollback ---------------------v$LOCK
为了实现并发，oracle数据库使用了锁机制。要了解锁，首先要了解视图v$lock。v$lock这个视图列出 Oracle 服务器当前拥有的锁以及未完成的锁请求。如果你觉着 session 处于等待事件队列当中，那你应该检查视图v$lock。v$lock中的常用列有以下列：sid：持有锁的会话SID，通常与v$session关联。type：锁的类型，其中TM表示表锁或DML锁，TX表示行锁或事务锁，UL表示用户锁。我们主要关注TX和TM两种型的锁，其它均为系统锁，会很快自动释放，不用关注。当 Oracle执行 DML 语句时，系统自动在所要操作的表上申请 TM 类型的锁。当 TM锁获得后，系统再自动申请 TX 类型的锁，并将实际锁定的数据行的锁标志位进行置位。TM 锁包括了SS 、 SX、 S 、X 等多种模式，在数据库中用 0 －6 来表示。不同的 SQL 操作产生不同类型的 TM锁。lmode：会话保持的锁的模式。0＝None；1＝Null ;2＝Row-S (SS,行级共享锁，其他对象只能查询这些数据行)，sql操作有select for update、lock for update、lock row share；3＝Row-X (SX,行级排它锁,在提交前不允许做DML操作)，sql操作有insert、update、delete、lock row share；4＝Share(共享锁)，sql操作有create index、lock share；5＝S/Row-X (SSX,共享行级排它锁)，sql操作有lock share row exclusive；6＝Exclusive(排它锁)，alter table、drop table、drop index、truncate table、look exclusive等DDLID1,ID2: ID1,ID2的取值含义根据type的取值而有所不同。（1）对于TM 锁ID1表示被锁定表的object_id 可以和dba_objects视图关联取得具体表信息，ID2 值为0；（2）对于TX 锁ID1以十进制数值表示该事务所占用的回滚段号和事务槽slot number号，其组形式: 0xRRRRSSSS,RRRR=RBS/UNDO NUMBER,SSSS=SLOT NUMBER，ID2 以十进制数值表示环绕wrap的次数，即事务槽被重用的次数。实际上这两个字段构成了事务在回滚段中的位置。 --------------------- SQL> desc v$lock; Name Type Nullable Default Comments ------- ----------- -------- ------- -------- ADDR RAW(8) Y -- KADDR RAW(8) Y -- SID NUMBER Y --正拥有或请求锁会话的ID。若LMODE>0时，表示正拥有锁的会话。REQUEST>0时，表示正请求锁的会话。 TYPE VARCHAR2(2) Y --Enqueue锁资源类型。 ID1 NUMBER Y --资源ID1 ID2 NUMBER Y --资源ID2 LMODE NUMBER Y --正拥有锁的模式。1~6 REQUEST NUMBER Y --正请求锁的模式。1~6 CTIME NUMBER Y --进入当前模式后的时间，即拥有或请求锁的时间。 BLOCK NUMBER Y --当前锁是否阻碍正在执行的另外的锁。1：正在阻碍另外的锁。0：为不阻碍另外的锁。接下来是因为对相同的行的更新引起的TX锁争用，并通过v$lock视图观察的例子。SQL> select * from v$lock where sid in(144,150) order by sid; ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 28A1C374 28A1C398 144 TX 65569 223 6 0 354 1 29434394 294343A8 150 TX 65569 223 0 6 339 0说明： BLOCK=1：这个会话正在阻塞其他的会话，这个SID就持有一个锁，并阻塞别人获得这个锁。REQUEST=6：非0表示他就是在等待一个锁。这里是6表示他正在等待一个LMODE=6的锁。第一行和第二行的值完全相同，这是必然的，因为他们都视图锁住同一个对象。从这个上面的查询可以看出，SID=150的会话有一个LMODE=6的请求，但是他没有获得 ，而是在等待。SID=144的会话持有一个LMODE=6的锁，并阻塞了SID=150的会话请求（BLOCK=1）。与锁相关的等待事件： Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as hbteleps@ORCL_192.168.6.244SQL> select * from v$event_name where name like '%enq:%';EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------83 3141712284 enq: PW - flush prewarm buffers name|mode 0 0 4217450380 1 Application103 3500532018 enq: RO - contention name|mode 2 0 4217450380 1 Application104 143262751 enq: RO - fast object reuse name|mode 2 0 4217450380 1 Application105 4205197519 enq: KO - fast object checkpoint name|mode 2 0 4217450380 1 Application149 1567037747 enq: MV - datafile move name|mode type file # 4166625743 3 Administrative231 668627480 enq: TM - contention name|mode object # table/partition 4217450380 1 Application232 1649608974 enq: ST - contention name|mode 0 0 3290255840 2 Configuration237 310662678 enq: TX - row lock contention name|mode usn<<16 | slot sequence 4217450380 1 Application238 281768874 enq: TX - allocate ITL entry name|mode usn<<16 | slot sequence 3290255840 2 Configuration239 1035026728 enq: TX - index contention name|mode usn<<16 | slot sequence 3875070507 4 Concurrency240 1435178951 enq: TW - contention name|mode 0 operation 4166625743 3 Administrative250 1645217925 enq: HW - contention name|mode table space # block 3290255840 2 Configuration251 3890744969 enq: SS - contention name|mode tablespace # dba 3290255840 2 Configuration256 2322460838 enq: SQ - contention name|mode object # 0 3290255840 2 Configuration268 122034066 enq: WG - lock fso name|mode kdlw lobid first half kdlw lobid sec half 3875070507 4 Concurrency278 3304404527 enq: BB - 2PC across RAC instances name|mode gtrid hash value bqual hash value 3386400367 5 Commit309 3835660459 enq: DB - contention name|mode EnqMode 0 4166625743 3 Administrative324 2649722911 enq: RC - Result Cache: Contention name|mode chunkNo blockNo 4217450380 1 Application325 1636695715 enq: JX - SQL statement queue name|mode sqlid execid 2396326234 10 Scheduler326 3822543692 enq: JX - cleanup of queue name|mode sqlid execid 2396326234 10 SchedulerEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------339 845337552 enq: ZG - contention name|mode file group id version id 4166625743 3 Administrative360 738183602 enq: UL - contention name|mode id 0 4217450380 1 Application401 2390244800 enq: WM - WLM Plan activation name|mode 0 0 1893977003 0 Other417 1760863753 enq: CI - contention name|mode opcode type 1893977003 0 Other418 3733307663 enq: PR - contention name|mode 0 0 1893977003 0 Other423 4129138703 enq: PE - contention name|mode parno 0 1893977003 0 Other424 925144608 enq: PG - contention name|mode 0 0 1893977003 0 Other434 3750685624 enq: FP - global fob contention name|mode low file obj add high file obj add 1893977003 0 Other435 1272752883 enq: RE - block repair contention name|mode File Type or File number block number 1893977003 0 Other439 2775294757 enq: KD - determine DBRM master name|mode 0 0 1893977003 0 Other440 1504129838 enq: KM - contention name|mode type type 1893977003 0 Other441 1301289702 enq: KT - contention name|mode plan # 0 1893977003 0 Other442 2962754459 enq: CA - contention name|mode 0 0 1893977003 0 Other448 3063016909 enq: PV - syncstart name|mode 0 0 1893977003 0 Other449 2894614381 enq: PV - syncshut name|mode 0 0 1893977003 0 Other450 2215147320 enq: SP - contention name|mode id1 id2 1893977003 0 Other451 351221043 enq: SP - contention 2 name|mode id1 id2 1893977003 0 Other452 839059449 enq: SP - contention 3 name|mode id1 id2 1893977003 0 Other453 1340803846 enq: SP - contention 4 name|mode id1 id2 1893977003 0 Other463 1830645796 enq: FM - contention name|mode 0 0 1893977003 0 Other464 107371275 enq: XY - contention name|mode id1 id2 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------466 2558928434 enq: AS - service activation name|mode 0 0 1893977003 0 Other467 245034220 enq: PD - contention name|mode property name key hash 1893977003 0 Other469 3530171172 enq: RU - contention name|mode 0 0 1893977003 0 Other470 2020359573 enq: RU - waiting name|mode 0 0 1893977003 0 Other474 1933295843 enq: MX - sync storage server info name|mode nodeid instanceid 1893977003 0 Other565 1494394835 enq: CF - contention name|mode 0 operation 1893977003 0 Other566 608373385 enq: SW - contention name|mode 0 0 1893977003 0 Other567 1608438935 enq: DS - contention name|mode 0 0 1893977003 0 Other568 1231322525 enq: TC - contention name|mode checkpoint ID 0 1893977003 0 Other569 718572278 enq: TC - contention2 name|mode checkpoint ID 0 1893977003 0 Other573 1340848367 enq: PW - perwarm status in dbw0 name|mode 0 0 1893977003 0 Other588 2576559565 enq: CR - block range reuse ckpt name|mode 2 0 1893977003 0 Other593 2704451831 enq: WL - Test access/locking name|mode log # / thread id # sequence # 1893977003 0 Other598 1710302097 enq: WL - RAC-wide SGA contention name|mode log # / thread id # sequence # 1893977003 0 Other620 784686743 enq: WL - RFS global state contention name|mode log # / thread id # sequence # 1893977003 0 Other629 1211302889 enq: WR - contention name|mode thread id # sequence # 1893977003 0 Other633 1555037586 enq: WL - contention name|mode log # / thread id # sequence # 1893977003 0 Other634 1281359820 enq: RN - contention name|mode thread number log number 1893977003 0 Other636 217146514 enq: DF - contention name|mode 0 file # 1893977003 0 Other637 1184948750 enq: IS - contention name|mode 0 type 1893977003 0 Other638 1309055815 enq: FS - contention name|mode 0 type 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------639 3294817508 enq: DM - contention name|mode type type 1893977003 0 Other640 647852263 enq: RP - contention name|mode file # 1 or block 1893977003 0 Other642 1143317824 enq: RT - contention name|mode redo thread type 1893977003 0 Other643 2588100800 enq: RT - thread internal enable/disable name|mode redo thread type 1893977003 0 Other644 4021392840 enq: IR - contention name|mode 0 0/1 1893977003 0 Other645 3041521765 enq: IR - contention2 name|mode 0 0/1 1893977003 0 Other646 3113891348 enq: MR - contention name|mode 0 or file # type 1893977003 0 Other647 3480024039 enq: MR - standby role transition name|mode 0 or file # type 1893977003 0 Other651 3954034543 enq: BR - file shrink name|mode operation file # 1893977003 0 Other652 1796838767 enq: BR - proxy-copy name|mode operation file # 1893977003 0 Other653 1785130586 enq: BR - multi-section restore header name|mode operation file # 1893977003 0 Other654 1429048489 enq: BR - multi-section restore section name|mode operation file # 1893977003 0 Other655 3146685455 enq: BR - space info datafile hdr update name|mode operation file # 1893977003 0 Other656 4186138071 enq: BR - request autobackup name|mode operation file # 1893977003 0 Other657 120917685 enq: BR - perform autobackup name|mode operation file # 1893977003 0 Other658 4055016213 enq: ID - contention name|mode 0 0 1893977003 0 Other662 513503674 enq: AB - ABMR process start/stop name|mode operation operation parm 1893977003 0 Other663 4289089804 enq: AB - ABMR process initialized name|mode operation operation parm 1893977003 0 Other666 1489162918 enq: MN - contention name|mode session ID 0 1893977003 0 Other667 622794581 enq: PL - contention name|mode 0 0 1893977003 0 Other668 2727448356 enq: SB - contention name|mode 0 0 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------674 707813539 enq: XR - quiesce database name|mode operation 0 1893977003 0 Other675 3609173507 enq: XR - database force logging name|mode operation 0 1893977003 0 Other682 1487467637 enq: CT - global space management name|mode operation operation parm 1893977003 0 Other683 3466352936 enq: CT - local space management name|mode operation operation parm 1893977003 0 Other684 690018733 enq: CT - change stream ownership name|mode operation operation parm 1893977003 0 Other685 2979034732 enq: CT - state name|mode operation operation parm 1893977003 0 Other686 790405879 enq: CT - state change gate 1 name|mode operation operation parm 1893977003 0 Other687 2477507895 enq: CT - state change gate 2 name|mode operation operation parm 1893977003 0 Other688 3488556206 enq: CT - CTWR process start/stop name|mode operation operation parm 1893977003 0 Other689 513540187 enq: CT - reading name|mode operation operation parm 1893977003 0 Other694 2897929244 enq: RS - file delete name|mode record type record id 1893977003 0 Other695 313351369 enq: RS - record reuse name|mode record type record id 1893977003 0 Other696 3504071695 enq: RS - prevent file delete name|mode record type record id 1893977003 0 Other697 3170569251 enq: RS - prevent aging list update name|mode record type record id 1893977003 0 Other698 3178040924 enq: RS - persist alert level name|mode record type record id 1893977003 0 Other699 115643876 enq: RS - read alert level name|mode record type record id 1893977003 0 Other700 3488540422 enq: RS - write alert level name|mode record type record id 1893977003 0 Other701 3383573524 enq: FL - Flashback database log name|mode Log # zero 1893977003 0 Other702 511900941 enq: FL - Flashback db command name|mode Log # zero 1893977003 0 Other703 1057119651 enq: FD - Marker generation name|mode Internal Internal 1893977003 0 Other704 120716488 enq: FD - Tablespace flashback on/off name|mode Internal Internal 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------705 3377895670 enq: FD - Flashback coordinator name|mode Internal Internal 1893977003 0 Other706 1783985711 enq: FD - Flashback on/off name|mode Internal Internal 1893977003 0 Other707 795831746 enq: FD - Restore point create/drop name|mode Internal Internal 1893977003 0 Other708 2085703061 enq: FD - Flashback logical operations name|mode Internal Internal 1893977003 0 Other731 2605232529 enq: TA - contention name|mode operation undo segment # / other 1893977003 0 Other732 1629782133 enq: TX - contention name|mode usn<<16 | slot sequence 1893977003 0 Other733 2458904239 enq: US - contention name|mode undo segment # 0 1893977003 0 Other737 2301151944 enq: IM - contention for blr name|mode pool # 0 1893977003 0 Other738 1752153790 enq: TD - KTF dump entries name|mode 0 0 1893977003 0 Other739 4241032297 enq: TE - KTF broadcast name|mode 0 0 1893977003 0 Other740 706934628 enq: CN - race with txn name|mode reg id 0 1893977003 0 Other741 4060759714 enq: CN - race with reg name|mode reg id 0 1893977003 0 Other742 613420004 enq: CN - race with init name|mode reg id 0 1893977003 0 Other744 606473003 enq: CO - master slave det name|mode inst id 0 1893977003 0 Other745 3932945957 enq: FE - contention name|mode 0 0 1893977003 0 Other747 896842959 enq: TF - contention name|mode tablespace # relative file # 1893977003 0 Other758 319052581 enq: DT - contention name|mode 0 0 1893977003 0 Other759 2667616873 enq: TS - contention name|mode tablespace ID dba 1893977003 0 Other760 1238611814 enq: FB - contention name|mode tablespace # dba 1893977003 0 Other761 2678203686 enq: SK - contention name|mode tablespace # dba 1893977003 0 Other762 2910919470 enq: DW - contention name|mode tablespace # dba 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------763 3652428359 enq: SU - contention name|mode table space # 0 1893977003 0 Other764 4205956891 enq: TT - contention name|mode tablespace ID operation 1893977003 0 Other767 887351382 enq: SJ - Slave Task Cancel name|mode Slave process id task id 1893977003 0 Other770 2908893972 enq: HV - contention name|mode object # 0 1893977003 0 Other774 1349347951 enq: DL - contention name|mode object # 0 1893977003 0 Other775 403868930 enq: HQ - contention name|mode object # hash value 1893977003 0 Other776 893937616 enq: HP - contention name|mode tablespace # dba 1893977003 0 Other777 3445676385 enq: WG - delete fso name|mode kdlw lobid first half kdlw lobid sec half 1893977003 0 Other778 3380482012 enq: SL - get lock name|mode kdlw lobid first half kdlw lobid sec half 1893977003 0 Other779 1973889364 enq: SL - escalate lock name|mode kdlw lobid first half kdlw lobid sec half 1893977003 0 Other780 3555038872 enq: SL - get lock for undo name|mode kdlw lobid first half kdlw lobid sec half 1893977003 0 Other781 2630448508 enq: ZH - compression analysis name|mode obj# ulevel 1893977003 0 Other783 2210508267 enq: DV - contention name|mode object # 0 1893977003 0 Other784 2584404246 enq: SO - contention name|mode object # 0 1893977003 0 Other785 3937527428 enq: TP - contention name|mode 0 0 1893977003 0 Other786 1305530676 enq: RW - MV metadata contention name|mode table obj# 0 1893977003 0 Other787 1911278064 enq: OC - contention name|mode 1 2 1893977003 0 Other788 3443769614 enq: OL - contention name|mode hash value 0 1893977003 0 Other796 325782660 enq: CU - contention name|mode handle handle 1893977003 0 Other797 3963940642 enq: AE - lock name|mode edition obj# 0 1893977003 0 Other798 2211137592 enq: PF - contention name|mode 0 0 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------799 1682769842 enq: IL - contention name|mode object # 0 1893977003 0 Other800 1619270516 enq: CL - drop label name|mode object # 0 1893977003 0 Other801 3032662556 enq: CL - compare labels name|mode object # 0 1893977003 0 Other802 3559219295 enq: MK - contention name|mode 0 0 1893977003 0 Other803 4033490427 enq: OW - initialization name|mode 0 0 1893977003 0 Other804 964463181 enq: OW - termination name|mode 0 0 1893977003 0 Other805 3147636141 enq: RK - set key name|mode 0 0 1893977003 0 Other806 2687533677 enq: RL - RAC wallet lock name|mode 0 0 1893977003 0 Other807 1248642937 enq: ZZ - update hash tables name|mode KSBXIC Action 0 1893977003 0 Other808 1704200513 enq: AU - audit index file name|mode XML audit index file 0 1893977003 0 Other809 1444835457 enq: ZA - add std audit table partition name|mode KZAM Aud Partition 0 1893977003 0 Other810 2473367936 enq: ZF - add fga audit table partition name|mode KZAM Fga Partition 0 1893977003 0 Other811 2881503609 enq: DX - contention name|mode transaction entry # 0 1893977003 0 Other812 721778600 enq: DR - contention name|mode 0 0 1893977003 0 Other857 2016557033 enq: JD - contention name|mode 0 0 1893977003 0 Other858 4077422893 enq: JQ - contention name|mode 0 0 1893977003 0 Other859 143199788 enq: OD - Serializing DDLs name|mode object # 0 1893977003 0 Other864 3735259588 enq: MD - contention name|mode master object # 0 1893977003 0 Other865 3982742587 enq: MS - contention name|mode master object # 0 1893977003 0 Other885 2143046196 enq: PI - contention name|mode operation serial # 1893977003 0 Other886 2071012023 enq: PS - contention name|mode instance slave ID 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------896 3076848514 enq: AY - contention name|mode Op1 Op2 1893977003 0 Other897 3701540314 enq: TO - contention name|mode object # 1 1893977003 0 Other898 2239268215 enq: IT - contention name|mode object # 0 1893977003 0 Other899 2212717019 enq: BF - allocation contention name|mode node#/parallelizer# bloom# 1893977003 0 Other900 2476632936 enq: BF - PMON Join Filter cleanup name|mode node#/parallelizer# bloom# 1893977003 0 Other901 3612504684 enq: RD - RAC load name|mode sqlid execid 1893977003 0 Other908 4149771879 enq: KP - contention name|mode 0 0 1893977003 0 Other913 3056899600 enq: SR - contention name|mode operation sequence # / apply # 1893977003 0 Other916 3398735787 enq: SI - contention name|mode object # 0 1893977003 0 Other918 2673474179 enq: IA - contention name|mode 0 0 1893977003 0 Other919 2959147725 enq: JI - contention name|mode view object # 0 1893977003 0 Other921 545059663 enq: AT - contention name|mode 0 0 1893977003 0 Other924 4187088707 enq: CQ - contention name|mode 0 0 1893977003 0 Other929 3429311972 enq: SE - contention name|mode Session-id Serial# 1893977003 0 Other932 1371132976 enq: TQ - TM contention name|mode QT_OBJ# 0 1893977003 0 Other933 717240345 enq: TQ - DDL contention name|mode QT_OBJ# 0 1893977003 0 Other934 451597502 enq: TQ - INI contention name|mode QT_OBJ# 0 1893977003 0 Other935 3544909312 enq: TQ - DDL-INI contention name|mode QT_OBJ# 0 1893977003 0 Other937 1150705222 enq: DP - contention name|mode 0 0 1893977003 0 Other938 798324601 enq: MH - contention name|mode 0 0 1893977003 0 Other939 4174613831 enq: ML - contention name|mode 0 0 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------940 148033462 enq: PH - contention name|mode 0 0 1893977003 0 Other941 3322341088 enq: SF - contention name|mode 0 0 1893977003 0 Other942 3111555168 enq: XH - contention name|mode 0 0 1893977003 0 Other943 3791087697 enq: WA - contention name|mode 0 0 1893977003 0 Other949 300221745 enq: XC - XDB Configuration name|mode 1893977003 0 Other952 2609577957 enq: RF - synch: DG Broker metadata name|mode lock operation lock value 1893977003 0 Other953 3779250430 enq: RF - atomicity name|mode lock operation lock value 1893977003 0 Other954 770131870 enq: RF - synchronization: aifo master name|mode lock operation lock value 1893977003 0 Other955 1283852695 enq: RF - new AI name|mode lock operation lock value 1893977003 0 Other956 3573752690 enq: RF - synchronization: critical ai name|mode lock operation lock value 1893977003 0 Other957 1173618054 enq: RF - RF - Database Automatic Disable name|mode lock operation lock value 1893977003 0 Other958 1422251130 enq: RF - FSFO Observer Heartbeat name|mode lock operation lock value 1893977003 0 Other959 2735020950 enq: RF - DG Broker Current File ID name|mode lock operation lock value 1893977003 0 Other972 3728184289 enq: AW - AW$ table lock name|mode operation workspace # 1893977003 0 Other973 2438512004 enq: AW - AW state lock name|mode operation workspace # 1893977003 0 Other974 541043045 enq: AW - user access for AW name|mode operation workspace # 1893977003 0 Other975 2247828282 enq: AW - AW generation lock name|mode operation workspace # 1893977003 0 Other976 2009316128 enq: AG - contention name|mode workspace # generation 1893977003 0 Other977 2454416615 enq: AO - contention name|mode workspace # object # 1893977003 0 Other978 523388040 enq: OQ - xsoqhiAlloc name|mode resource id 0 1893977003 0 Other979 3682185722 enq: OQ - xsoqhiFlush name|mode resource id 0 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------980 681553729 enq: OQ - xsoq*histrecb name|mode resource id 0 1893977003 0 Other981 2148426979 enq: OQ - xsoqhiClose name|mode resource id 0 1893977003 0 Other982 4194119705 enq: OQ - xsoqhistrecb name|mode resource id 0 1893977003 0 Other983 409056071 enq: AM - client registration name|mode id1 id2 1893977003 0 Other984 4003623979 enq: AM - shutdown name|mode id1 id2 1893977003 0 Other985 3718305 enq: AM - rollback COD reservation name|mode id1 id2 1893977003 0 Other986 2163538166 enq: AM - background COD reservation name|mode id1 id2 1893977003 0 Other987 2512559650 enq: AM - ASM cache freeze name|mode id1 id2 1893977003 0 Other988 1784903832 enq: AM - ASM ACD Relocation name|mode id1 id2 1893977003 0 Other989 3865222969 enq: AM - group use name|mode id1 id2 1893977003 0 Other990 2069926448 enq: AM - group block name|mode id1 id2 1893977003 0 Other991 827631834 enq: AM - ASM File Destroy name|mode id1 id2 1893977003 0 Other992 1369527695 enq: AM - ASM User name|mode id1 id2 1893977003 0 Other993 1010353447 enq: AM - ASM Password File Update name|mode id1 id2 1893977003 0 Other994 2754586444 enq: AM - ASM Amdu Dump name|mode id1 id2 1893977003 0 Other995 1111439585 enq: AM - disk offline name|mode id1 id2 1893977003 0 Other996 1569316226 enq: AM - PST split check name|mode id1 id2 1893977003 0 Other1008 4186342174 enq: CM - gate name|mode disk group # type 1893977003 0 Other1009 3394651275 enq: CM - instance name|mode disk group # type 1893977003 0 Other1010 3051305468 enq: XQ - recovery name|mode disk group # unused 1893977003 0 Other1011 1688174401 enq: XQ - relocation name|mode disk group # unused 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------1012 3970804539 enq: XQ - purification name|mode disk group # unused 1893977003 0 Other1013 2345563243 enq: AD - allocate AU name|mode group and disk number AU number 1893977003 0 Other1014 143335296 enq: AD - deallocate AU name|mode group and disk number AU number 1893977003 0 Other1015 2372793991 enq: AD - relocate AU name|mode group and disk number AU number 1893977003 0 Other1016 1035436594 enq: DO - disk online name|mode disk group # disk # 1893977003 0 Other1017 3543159936 enq: DO - disk online recovery name|mode disk group # disk # 1893977003 0 Other1018 3796486218 enq: DO - Staleness Registry create name|mode disk group # disk # 1893977003 0 Other1019 1190657481 enq: DO - startup of MARK process name|mode disk group # disk # 1893977003 0 Other1020 329663708 enq: DO - disk online operation name|mode disk group # disk # 1893977003 0 Other1022 187881552 enq: XL - fault extent map name|mode map id nothing 1893977003 0 Other1024 685231043 enq: DG - contention name|mode disk group type 1893977003 0 Other1025 3688395675 enq: DD - contention name|mode disk group type 1893977003 0 Other1026 4039922119 enq: HD - contention name|mode disk group 0 1893977003 0 Other1027 2043768807 enq: DN - contention name|mode 0 0 1893977003 0 Other1034 3182712926 enq: FA - access file name|mode disk group number file number 1893977003 0 Other1035 2145403328 enq: RX - relocate extent name|mode disk group #:file # virtual extent number 1893977003 0 Other1039 1893664477 enq: FR - contention name|mode disk group thread 1893977003 0 Other1040 3144887602 enq: FR - use the thread name|mode disk group thread 1893977003 0 Other1041 2085113153 enq: FR - recover the thread name|mode disk group thread 1893977003 0 Other1042 1457820412 enq: FG - serialize ACD relocate name|mode disk group type 1893977003 0 Other1043 903153076 enq: FG - FG redo generation enq race name|mode disk group type 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------1044 3282679649 enq: FG - LGWR redo generation enq race name|mode disk group type 1893977003 0 Other1045 4166969980 enq: FT - allow LGWR writes name|mode disk group thread 1893977003 0 Other1046 3784147810 enq: FT - disable LGWR writes name|mode disk group thread 1893977003 0 Other1047 1342373550 enq: FC - open an ACD thread name|mode disk group thread 1893977003 0 Other1048 4041110320 enq: FC - recover an ACD thread name|mode disk group thread 1893977003 0 Other1049 642072527 enq: FX - issue ACD Xtnt Relocation CIC name|mode disk group unused 1893977003 0 Other1052 11871430 enq: RB - contention name|mode disk group 0 1893977003 0 Other1054 1698021162 enq: PT - contention name|mode disk group # type 1893977003 0 Other1071 3738539678 enq: KQ - access ASM attribute name|mode group entry 1893977003 0 Other1074 1869608638 enq: AV - persistent DG number name|mode persistent DG number non-DG number enqs 1893977003 0 Other1075 3917752439 enq: AV - volume relocate name|mode persistent DG number non-DG number enqs 1893977003 0 Other1076 1293149770 enq: AV - AVD client registration name|mode persistent DG number non-DG number enqs 1893977003 0 Other1077 845350487 enq: AV - add/enable first volume in DG name|mode persistent DG number non-DG number enqs 1893977003 0 Other1079 1641438405 enq: WF - contention name|mode 0 0 1893977003 0 Other1080 2389341073 enq: WP - contention name|mode 0 0 1893977003 0 Other1081 2941830639 enq: FU - contention name|mode 0 0 1893977003 0 Other1082 3384888932 enq: MW - contention name|mode Schedule Id 0 1893977003 0 Other1085 3315745697 enq: TB - SQL Tuning Base Cache Update name|mode 1 2 1893977003 0 Other1086 2878002362 enq: TB - SQL Tuning Base Cache Load name|mode 1 2 1893977003 0 Other1087 3691188946 enq: SH - contention name|mode 0 0 1893977003 0 Other1088 955173113 enq: AF - task serialization name|mode task id 0 1893977003 0 OtherEVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------- ----------- ----------------------------------------------------------------1091 3932123875 enq: MO - contention name|mode 0 0 1893977003 0 Other1092 2407027636 enq: TL - contention name|mode 0 0 1893977003 0 Other1093 3243557671 enq: TH - metric threshold evaluation name|mode 0 0 1893977003 0 Other1094 917975477 enq: TK - Auto Task Serialization name|mode 0-MMON, 1-MMON Slave 0 1893977003 0 Other1095 1739764420 enq: TK - Auto Task Slave Lockout name|mode 0-MMON, 1-MMON Slave 0 1893977003 0 Other1096 2152050500 enq: RR - contention name|mode lock# not used 1893977003 0 Other1102 3797524291 enq: JS - contention name|mode service ID queue type 1893977003 0 Other1103 6961751 enq: JS - job run lock - synchronize name|mode service ID queue type 1893977003 0 Other1104 1043516589 enq: JS - job recov lock name|mode service ID queue type 1893977003 0 Other1105 306423829 enq: JS - queue lock name|mode service ID queue type 1893977003 0 Other1106 1430268372 enq: JS - sch locl enqs name|mode service ID queue type 1893977003 0 Other1107 1053495082 enq: JS - q mem clnup lck name|mode service ID queue type 1893977003 0 Other1108 551173053 enq: JS - evtsub add name|mode service ID queue type 1893977003 0 Other1109 4064242319 enq: JS - evtsub drop name|mode service ID queue type 1893977003 0 Other1110 3418411601 enq: JS - wdw op name|mode service ID queue type 1893977003 0 Other1111 3478457902 enq: JS - evt notify name|mode service ID queue type 1893977003 0 Other1112 903782161 enq: JS - aq sync name|mode service ID queue type 1893977003 0 Other1113 3791535926 enq: XD - ASM disk drop/add name|mode opcode notused 1893977003 0 Other290 rows selectedSQL>
总结
以上是生活随笔为你收集整理的事务的四大特性、事务处理开始与结束、v$transactio、 v$LOCK的全部内容，希望文章能够帮你解决所遇到的问题。
- 上一篇： oracle 分析函数、GROUPING
- 下一篇： Oracle 共享锁和排它锁、 DML和