oracle相关特性篇之闩锁、和并发性

Oracle是一个多用户并发处理系统，在同一个时间点上，可能会有多个1、这些用户之间的操作不会互相破坏，比如两个用户同时在相同的物2、在满足串行化的前提下，如何将并发性提升型的锁控制对数据的并发，以防止用户之间出现破坏性的交互操作。个进程同时资源，会造成资源的破坏，那么就需要给这种资源上一个锁，如果这种资源很简单，例如内存的分配和，那么就使用latch，如果这种资源相对复杂，有一定的逻辑判断，那么就需要使用lock。闩锁Oracle使用闩锁来实现内存的分配和例某个用户进程A发出一条update语句，要去58号数据块里的某条出insert语句，要将某个新的记录到58号数据块中，如果没有一定的保护机制，A正要写入的空间可能被B抢先写入，B要写入的空间可能被A抢先写入，不管哪个用户先抢先写入，造成的结果就是，58号数据块的数据都了在写入过程中，一直持有latch，写完以后，latch。B尝试latch，发现latchA持有，B进入等待状态，直Alatch，Blatch以后，才能在58号数据块中写入数据。凡是涉及内存的读和写，就需要通过latch来实现，一次只能有一Oracle在实例管理中，不管是buffercache、sharedpoollogbuffer，都引入了各种各样的latch。可以这样来理解latch，通过某个变量值的变化来实现，变0则说明latch没有被其它进程获取，变量值为非0则说明latch被其他进程获取Latch分为两种类这种类latch都是test-and-set的方式获得的，也就是说，如果当前进程不能获latch的时候CPU旋转，而不放CPU，这就占用着CPU。latch，为什么还要占用CPUlatch本身是但是没有获得latchCPU，那么需要进行上下文的切换，下次再次尝试获得latch时，又要进行上下文切换，可能消耗的时间，因此，会通过执行一段空代码绕着CPU转，然后再次尝试获得latch。如果仍然不能够获latch，继续旋转，当反复旋转CPU并尝试获得latch的次数超过某个上限时（该上限由隐藏参数控制，这时进程会CPUsleep状态，进程一旦进入sleep状态，就会抛出一个等待事件，并记录v$session_wait里，进程正在等latch的信息会出现一个进程会睡眠0.01秒，然后醒来，并再次尝试获得latch，如果旋转CPU的次数达到上限值，仍然不能获得latch，则再次进入睡眠，这时会睡眠两倍的时间，以此类推，直到达到睡眠的最大值：0.2秒。上面的情况是数据库服务器具有CPU的情况，如果只有CPU，就不存在旋转CPU的情况，一旦获得不了latch，就进入睡眠。2、不等待（no-这种类型的latch较少，对于这种类型latch来说，都会有很多个可用的latch。当一个进程请求其中的一个latch时no-wait模式开始请求，如果有当所有的latch都不能获得的时候，才会进入等待。源很紧张，利用率总是在90%以上，甚至总是在100%，主要原因如下时随意性过大，比如大小写混用、则OracleSQL语句都要进行解析，也就是非常频繁地读写sharedpool里的内存块，从而导致与解析SQL相关的latch争用。sqlSQL语句写的比较低一旦发生latch资源争用，就会导致CPU繁假设进程A执行一条SQL语句需要10000个数据块，那么该进扫描数据块的过程中，一直持有latch，而另一个进程B也要执行SQL得latch，直到睡眠才CPU，接下来C进程也要执行SQL，同样的，由于A持有了latchC无法获得，于是旋CPU，再去获latch，直到进入睡眠才CPU，如果类似进程很多的话，CPU总是在旋转，也就是在做空的循环，而无法做其他的事情，因此体现出CPU使用率过锁定Lock用来控制多个用户对表里相同数据如果没有使用锁定来管理事务，则在9：04的时候，用户A检索id=1列值时，显示为20，用户A的修改被B覆盖使用锁定以后的情况在锁定中，存在下面的两种基1、排他锁（X锁，一旦用户对某个资源添加了排他锁，则其他用户都不能再对该资源添加任何类型的锁，直到该用户了资源上的X锁在该资源上添加X锁，只能添加S锁，直到该用户了S锁为止DML事务锁定机制行级锁（TX锁updateemployeesetlast_name=‘xkj’where1、OracleSQL语句进行解析，找exployee_id100的记录所在的列上被更新前的旧值放入该undoSCN号、所使用的undo数据块的地址，以及未提交的标记等信息358号数据块中，找到被更新的数据行，在数据行头部设置一个锁定标记，并在头部记录当前使用的ITL槽号，做完这些工作X锁如果这时，另一个用B也对exployee_id100的记录进行修改，则其过上面描述一样B在对数据行的头部设置锁定标记的时候，发现该数据行头部已经有一个锁定标记了，说明该记录已经添加X锁，于是用户B必须等待，等待X锁被。锁定一条记录，并不影响其他用户对该记录的，例如果用户发出一SQLexployee_id100的记录信这时服了，但是还没有提交。于是根据数据行头部ITL槽的号码，在数据块头部ITL槽，并根据其中记录undo数据块的地址，找到undo读一致性）块，根据CR块的内容，将用户所需要的信息进行返回。表级A已经发出了更新exployee_id100的记录的SQL语句，当A还没有提交之前，另外一个用户D发出下面的语句droptable由于A还没有提交所做的事务，该事务还没有结束，其他用户不能删除该表，否则A所发出的事务就无法正常结束，为了用户D的删除操作，能够想到的最直观的方法是显然这样存在很大的性能问表级锁共5种模1、行级排他锁（Rowexclusive）RX当进行DML时，会自动在被更新的表上添加RX锁，可以执允许其他事务通过DML语句修改相同表里的其他数据行允许使用lock命令对表添加RX锁定不允许其他事务对表添加XSYS@orcl>createtabletestx(idnumber);SYS@orcl>insertintotestxvalues(1);在Sess1中数据，不提交，再打开Sess2，添加XSYS@orcl>locktabletestxinexclusive不能添加X中可以添加X锁。SYS@orcl>locktabletestxinexclusivemode;Table(s)Locked.成功添加XSYS@orcl>createtabletest1(idnumber);SYS@orcl>insertintotest1values(1);务使用lock添加一个行级排他锁SYS@orcl>locktabletest1inrowexclusivemode;Table(s)Locked.在Sess1中无法XSYS@orcl>locktabletest1inexclusive不成在sess2中rollbacksess1可以添加X锁，Sess2中无XSYS@orcl>SYS@orcl>locktabletest1inexclusivemode;Sess1成功SYS@orcl>locktabletest1inexclusiveSess2不成行级共享锁（rowsharedRS锁这些命令。查询完成以后，发出rollback命令表上添加RS锁以后，不允许其他事务对相同的表添X锁，但是允许SYS@orcl>createtabletest2(idnumber,namevarchar2(10));SYS@orcl>insertintotest2values(1,'aa');SYS@orcl>insertintotest2values(2,'bb');SYS@orcl>commit;SYS@orcl>select*fromtest2whereid=1forupdate;Sess2，不允许对锁定的数据进行修改SYS@orcl>deletefromtest2;SYS@orcl>deletefromtest2whereid=2;SYS@orcl>commit;在对表进行DML的时候，已经在表上添加了行级排他锁，因此在有行级共SYS@orcl>locktabletest2inrowexclusivemode;SYS@orcl>locktabletest2inexclusive不成Sess2，rollback后，sess1可以添加排他锁SYS@orcl>locktabletest2inexclusivemode;共享锁（shared）SSYS@orcl>createtabletest3(idnumber);SYS@orcl>insertintotest3values(1);SYS@orcl>insertintotest3values(2);SYS@orcl>commit;SYS@orcl>locktabletest3insharemode;Sess2，不允许进行DML操作SYS@orcl>insertintotest3values(5);Sess1，rollback后，sess2可以数据Sess2，允许进行RSSYS@orcl>locktabletest3inrowshare注：共享锁和排他锁的区别是可以允许排他锁（exclusive）X其他事务不能对表进行任何的DMLDDL操作，只能进行查SYS@orcl>locktabletest3inexclusive共享行级排他锁（sharedrowexclusive）SRX不能对相同的表进行DML操作，也不能添加共锁之间的兼容性，有了这个锁，能不能有另外一 exclusive][exclusive]mode;可以通过DML进行自动的加锁，也可以使用LOCK手工共享锁（ShareTableLockS加锁语ockTableTableNameInShare表。一个有效的共享锁明确地SelectForupdate形式锁定行，或执行LOCKTABLETableNameINSHAREROWEXCLUSIVEMODE;LOCKTABLETableNameINROWEXCLUSIVEMODE;排它锁（ExclusiveTableLockX加锁语ockTableTableNameInExclusive定义排它锁的语法LOCKTABLETableNameINEXCLUSIVE行级锁（RowShareTableLock，RS一个行级锁（有时SubshareTableLock，简SS，子共享锁）需要该事务在被锁定行的表update的形式加锁。当有下面语句被执行的时候SELECT...FROMTableName...FORUPDATEOF...;LOCKTABLETableNameINROWSHAREMODE;行级锁（RowShareTableLock）在锁类型中是限制最少的，也是在表的并允许的操作：行级共享锁由一个事务控制，允许其它事务查询、、更的操作：拥有行级锁的事务不允许其它事务执行排它锁，即LockTableTableNameInExclusive行级排它锁（RowExclusiveTableLock，RXINSERTINTOTableName...;UPDATETableName...;DELETEFROMTableName...;LOCKTABLETableNameINROWEXCLUSIVE行级排它锁比行级锁稍微多一些限制LOCKTABLEtableINSHARELOCKTABLEtableINSHAREEXCLUSIVEMODE;LOCKTABLEtableINEXCLUSIVEMODE共享行级排它锁（ShareRowExclusiveTableLock，SRX共享行级排它锁有时也称共享子排它锁（ShareSubexclusiveTableLock，SSX，它比共享锁有限制。定义共享行级排它锁的语法为：LockTableTableNameInShareRowExclusive锁事务允许其它事务在被锁定的表上执行查询或使用SelectFromTableNameForupdate…来准确在锁定行而不能更新行。LOCKTABLETableNameINSHAREMODE;LOCKTABLETableNameINSHAREROWEXCLUSIVEMODE;LOCKTABLETableNameINROWEXCLUSIVEMODE;LOCKTABLETableNameINEXCLUSIVE据库挂起，或导致死锁的发生，产生ORA-60的错误。解决DML事务锁定的兼容的锁定，就会发生锁定的现象。锁定相关视其中比较重要的字段包括XIDUSN表示当前事务使用的回滚段的、做槽号、XIDSQN说明序列号、STATUS说明该事务是否为活动的。记录了当session已经获得的锁定以及正在请求的锁定的信息。其中比较TX和TM、LMODE说明已经获得的锁定的模式，以数字编码表示、REQUEST说明正在请求的锁定的模式，以数字编码表示。BLOCK说明是否了其他用户获得锁定，大于0说明是，等于0说明否。该视图中包含的字段以及字段含义v$lock中的字段一模一样。只不过该录按照申请锁定的时间先后顺序排列，先申请锁定的session排面，排面的session将会先获得锁定。记录了当前已经被锁定的对象的信息。其中比较重要的字段包括XIDUSN表示当前事务使用的回滚段的、XIDSLOT说明该事务在回滚段头部的事务表中对应的记录、XIDSQN说明序列号、OBJECT_ID说明当前被锁定的对象的ID号，可以IDdba_objects里查找被锁定的对象名称、LOCKED_MODE说明锁定模式的数字编码。记录了当前session的相关信息。其中比较重要的字段包SID表示sessionSYS@orcl>createtabletest5(idnumber,namevarchar(10),salarynumber);SYS@orcl>insertintotest5values(1,'a',12000);SYS@orcl>insertintotest5values(1,'a',3000);SYS@orcl>insertintotest5values(1,'a',20000);SYS@orcl>insertintotest5values(2,'b',11000);SYS@orcl>commit;SYS@orcl>updatetest5setname='wl'whereid=1;SYS@orcl>selectsidfromv$mystatwhererownum=1;Sess1的sessionIDSYS@orcl>selectxidusn,xidslot,xidsqn,statusfromv$transaction; XIDSQNSTATUS 该会话使用了5号回该会话在回滚段事务表中的槽号是事务是活动的，也就是没有提SYS@orcl>selectsid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'rowsharee',3,'rowexclusive',4,'share',5,'sharerowexclusive',6,'exclusive') exclusive',4,'share',5,'sharerowexclusive',6,'exclusive')request_mode,blockfromv$lockwheresid=31;SIDTYID1ID2LOCK_MODEREQUEST_MODE row ID1：对于TM锁来说，被锁定对象的IDSYS@orcl>selectobject_namefromdba_objectswhereobject_id=88622;被锁定对象是另外启动一个sessionSYS@orcl>selectsidfromv$mystatwhererownum=1;SessionIDSYS@orcl>updatetest5setname='ww'where这个会话被锁定了回到session1，查看一下会话的锁的情况SYS@orcl>selectsid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'rowsharee',3,'rowexclusive',4,'share',5,'sharerowexclusive',6,'exclusive') exclusive',4,'share',5,'sharerowexclusive',6,'exclusive')request_mode,blockfromv$lockwheresidin(31,34);SIDTYID1ID2LOCK_MODEREQUEST_MODE000000row00row0134请求的锁是行级排他锁，但是该记录31会话锁定，因此锁定模式为NONE34在TM上获得了行级排他锁，因为这个锁和已有的锁并不，31的锁再次启动一个sessionSYS@orcl>selectsidfromv$mystatwhererownum=1;SYS@orcl>updatetest5setname='ww'where事务也被锁定回到session1执行这个命SYS@orcl>selectsid,type,decode(request,0,'none',1,'null',2,'rowshare',3,'rowexclusive',4,'share',5,'sharerowexclusive',6,'exclusive')request_modefromv$enqueue_lockwheresidin(31,34,56); TY TX TX AE AE AE exclusive',4,'share',5,'sharerowexclusive',6,'exclusive')lock_mode,b.ctimeas request_mode,c.ctimetime_waitedfromv$lockb,v$enqueue_lockc,v$sessionawherea.sid=b.sidandb.id1=c.id1(+)andb.id2=c.id2(+)andc.type(+)='TX'andb.type='TX'andBLOCKER_SIDSERIAL#BLOCKER_USERNAMETYLOCK_MODTIME_HELDWAITER_SIDREQUEST_MODE切换sess1会话，在31会话中，提交事务。再次运行查SYS@orcl> exclusive',4,'share',5,'sharerowexclusive',6,'exclusive')lock_mode,b.ctimeas request_mode,c.ctimetime_waitedfromv$lockb,v$enqueue_lockc,v$sessionawherea.sid=b.sidandb.id1=c.id1(+)andb.id2=c.id2(+)andc.type(+)='TX'andb.type='TX'andBLOCKER_SIDSERIAL#BLOCKER_USERNAMETYLOCK_MODTIME_HELDWAITER_SIDREQUEST_MODE SYS@orcl>altersystemkillsession'34,2657';再次查询，发现已经没有了锁SYS@orcl> exclusive',4,'share',5,'sharerowexclusive',6,'exclusive')lock_mode,b.ctimeas request_mode,c.ctimetime_waitedfromv$lockb,v$enqueue_lockc,v$sessionawherea.sid=b.sidandb.id1=c.id1(+)andb.id2=c.id2(+)andc.type(+)='TX'andb.type='TX'andb.block=1;norows2/*selectsid,id1,id2,lmode,request,typefromv$lockwhereid1in(selectid1fromv$lockwherelmode=0)orderbyid1,request;--TMTXselectsid,type,id1,id2, reqtime,blockfromv$lockwheretypein('TM','TX');--TMTX decode(request,0,'NONE',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X')request, ctime,blockfromv$lockwheretypein('TM','TX');3IDselectl.os_user_name,s.username, o.object_name,o.object_type,s.logon_timefromv$locked_objectl,v$sessions,dba_objectsowherel.session_id=orderbys.logon_time;或selectl.oracle_usernameo_name,l.session_idsid,o.object_name,o.object_type,l.xidusn,l.xidslot,l.xidsqnfromv$locked_objectl,all_objectsowherel.object_id=o.object_idorderby或SELECTp.spid,a.serial#,c.object_name,b.session_id,b.oracle_username,b.os_user_nameFROMv$processp,v$sessiona,v$locked_objectb,all_objectscWHEREp.addr=a.paddrANDcess=cessANDc.object_id=b.object_id;如何查找引起锁的S