怎么建购物网站,天津设计工作室,如何免费自做企业网站,东莞住房和城乡建设局网站今天在开发自动预警的时候#xff0c;发现一个表被锁了#xff0c;mark 之后细看#xff1a; ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效的快速解决方法 今天在导一个临时表的数据#xff0c;导出完成后准备清空数据#xff0c;执行tr…今天在开发自动预警的时候发现一个表被锁了mark 之后细看 ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效的快速解决方法 今天在导一个临时表的数据导出完成后准备清空数据执行truncate命令时遇到如下问题 ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效 解决方法如下 SQL select session_id from v$locked_object; SESSION_ID---------- 56 SQL SELECT sid, serial#, username, osuser FROM v$session where sid 142; SID SERIAL# USERNAME OSUSER---------- ---------- ------------------------------ ------------------------------ 56 2088 ghb fy SQL ALTER SYSTEM KILL SESSION 56,2088; System altered 执行完上述命令后提示会话断开。重新连接数据库然后执行truncate操作成功 以下是原理部分 Oracle数据库的锁类型 根据保护的对象不同Oracle数据库锁可以分为以下几大类DML锁data locks数据锁用于保护数据的完整性DDL锁dictionary locks字典锁用于保护数据库对象的结构如表、索引等的结构定义内部锁和闩internal locks and latches保护数据库的内部结构。 DML锁的目的在于保证并发情况下的数据完整性。在Oracle数据库中DML锁主要包括TM锁和TX锁其中TM锁称为表级锁TX锁称为事务锁或行级锁。 当Oracle 执行DML语句时系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后系统再自动申请TX类型的锁并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志而只需检查TM锁模式的相容性即可大大提高了系统的效率。TM锁包括了SS、SX、S、X 等多种模式在数据库中用06来表示。不同的SQL操作产生不同类型的TM锁。 在数据行上只有X锁排他锁。在 Oracle数据库中当一个事务首次发起一个DML语句时就获得一个TX锁该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时第一个会话在该条记录上加锁其他的会话处于等待状态。当第一个会话提交后TX锁被释放其他会话才可以加锁。 当Oracle数据库发生TX锁等待时如果不及时处理常常会引起Oracle数据库挂起或导致死锁的发生产生ORA-60的错误。这些现象都会对实际应用产生极大的危害如长时间未响应大量事务失败等。 悲观封锁和乐观封锁 一、悲观封锁锁在用户修改之前就发挥作用Select ..for updatenowait)Select * from tab1 for update用户发出这条命令之后oracle将会对返回集中的数据建立行级封锁以防止其他用户的修改。如果此时其他用户对上面返回结果集的数据进行dml或ddl操作都会返回一个错误信息或发生阻塞。1对返回结果集进行update或delete操作会发生阻塞。2对该表进行ddl操作将会报Ora-00054:resource busy and acquire with nowait specified. 原因分析此时Oracle已经对返回的结果集上加了排它的行级锁所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放产生的外在现象就是其他的操作将发生阻塞这个这个操作commit或rollback.同样这个查询的事务将会对该表加表级锁不允许对该表的任何ddl操作否则将会报出ora-00054错误:resource busy and acquire with nowait specified. 二、乐观封锁乐观的认为数据在select出来到update进取并提交的这段时间数据不会被更改。这里面有一种潜在的危险就是由于被选出的结果集并没有被锁定是存在一种可能被其他用户更改的可能。因此Oracle仍然建议是用悲观封锁因为这样会更安全。 阻塞 定义当一个会话保持另一个会话正在请求的资源上的锁定时就会发生阻塞。被阻塞的会话将一直挂起直到持有锁的会话放弃锁定的资源为止。4个常见的dml语句会产生阻塞INSERTUPDATEDELETESELECT…FOR UPDATE INSERT Insert发生阻塞的唯一情况就是用户拥有一个建有主键约束的表。当2个的会话同时试图向表中插入相同的数据时其中的一个会话将被阻塞直到另外一个会话提交或会滚。一个会话提交时另一个会话将收到主键重复的错误。回滚时被阻塞的会话将继续执行。 UPDATE 和DELETE当执行Update和delete操作的数据行已经被另外的会话锁定时将会发生阻塞直到另一个会话提交或会滚。 Select …for update 当一个用户发出select..for update的错作准备对返回的结果集进行修改时如果结果集已经被另一个会话锁定就是发生阻塞。需要等另一个会话结束之后才可继续执行。可以通过发出 select… for update nowait的语句来避免发生阻塞如果资源已经被另一个会话锁定则会返回以下错误Ora-00054:resource busy and acquire with nowait specified. 死锁-deadlock 定义:当两个用户希望持有对方的资源时就会发生死锁.即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚.例子1用户1对A表进行Update没有提交。2用户2对B表进行Update没有提交。此时双反不存在资源共享的问题。3如果用户2此时对A表作update,则会发生阻塞需要等到用户一的事物结束。4如果此时用户1又对B表作update则产生死锁。此时Oracle会选择其中一个用户进行会滚使另一个用户继续执行操作。起因:Oracle的死锁问题实际上很少见如果发生基本上都是不正确的程序设计造成的经过调整后基本上都会避免死锁的发生。 DML锁分类表 表1 Oracle的TM锁类型锁模式 锁描述 解释 SQL操作0 none1 NULL 空 Select2 SS(Row-S) 行级共享锁其他对象只能查询这些数据行 Select for update、Lock for update、Lock row share 3 SX(Row-X) 行级排它锁在提交前不允许做DML操作 Insert、Update、Delete、Lock row share 4 S(Share) 共享锁 Create index、Lock share5 SSX(S/Row-X) 共享行级排它锁 Lock share row exclusive6 X(Exclusive) 排它锁 Alter table、Drop able、Drop index、Truncate table 、Lock exclusive 1.关于V$lock表和相关视图的说明 Column Datatype DescriptionADDR RAW(4 | 8) Address of lock state objectKADDR RAW(4 | 8) Address of lockSID NUMBER Identifier for session holding or acquiring the lockTYPE VARCHAR2(2) Type of user or system lockThe locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:TM - DML enqueueTX - Transaction enqueueUL - User supplied--我们主要关注TX和TM两种类型的锁--UL锁用户自己定义的一般很少会定义基本不用关注--其它均为系统锁会很快自动释放不用关注ID1 NUMBER Lock identifier #1 (depends on type)ID2 NUMBER Lock identifier #2 (depends on type)---当lock type 为TM时id1为DML-locked object的object_id---当lock type 为TX时id1为usnslot而id2为seq。--当lock type为其它时不用关注LMODE NUMBER Lock mode in which the session holds the lock:0 - none1 - null (NULL)2 - row-S (SS)3 - row-X (SX)4 - share (S)5 - S/Row-X (SSX)6 - exclusive (X)--大于0时表示当前会话以某种模式占有该锁等于0时表示当前会话正在等待该锁资源即表示该会话被阻塞。--往往在发生TX锁时伴随着TM锁比如一个sid9会话拥有一个TM锁一般会拥有一个或几个TX锁但他们的id1和id2是不同的请注意REQUEST NUMBER Lock mode in which the process requests the lock:0 - none1 - null (NULL)2 - row-S (SS)3 - row-X (SX)4 - share (S)5 - S/Row-X (SSX)6 - exclusive (X)--大于0时表示当前会话被阻塞其它会话占有改锁的模式CTIME NUMBER Time since current mode was grantedBLOCK NUMBER The lock is blocking another lock0, Not Blocking,1, Blocking,2, Global, --该锁是否阻塞了另外一个锁 2.其它相关视图说明视图名 描述 主要字段说明v$session 查询会话的信息和锁的信息。 sid,serial#表示会话信息。program表示会话的应用程序信息。row_wait_obj#表示等待的对象,和dba_objects中的object_id相对应。lockwait :该会话等待的锁的地址,与v$lock的kaddr对应.v$session_wait 查询等待的会话信息。 sid表示持有锁的会话信息。Seconds_in_wait表示等待持续的时间信息Event表示会话等待的事件锁等于enqueue dba_locks 对v$lock的格式化视图。 Session_id和v$lock中的Sid对应。Lock_type和v$lock中的type对应。Lock_ID1 和v$lock中的ID1对应。Mode_held,mode_requested和v$lock中的lmode,request相对应。v$locked_object 只包含DML的锁信息包括回滚段和会话信息。 Xidusn,xidslot,xidsqn表示回滚段信息。和v$transaction相关联。Object_id表示被锁对象标识。Session_id表示持有锁的会话信息。Locked_mode表示会话等待的锁模式的信息和v$lock中的lmode一致。 以下是命令行部分 1.查询数据库中的锁 select * from v$lock;select * from v$lock where block1; 2.查询被锁的对象 select * from v$locked_object; 3.查询阻塞 查被阻塞的会话select * from v$lock where lmode0 and type in (TM,TX); 查阻塞别的会话锁select * from v$lock where lmode0 and type in (TM,TX); 4.查询数据库正在等待锁的进程 select * from v$session where lockwait is not null; 5.查询会话之间锁等待的关系 select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock bwhere a.id1b.id1 and a.id2b.id2 and a.block1 and b.block0; 6.查询锁等待事件select * from v$session_wait where eventenqueue; 解决方案select session_id from v$locked_object; --首先得到被锁对象的session_idSELECT sid, serial#, username, osuser FROM v$session where sid session_id; --通过上面得到的session_id去取得v$session的sid和serial#然后对该进程进行终止。ALTER SYSTEM KILL SESSION sid,serial;example:ALTER SYSTEM KILL SESSION 13, 8; Oracle 锁表续 ora-00031:session marked for kill处理oracle中杀不掉的锁 一些ORACLE中的进程被杀掉后状态被置为killed但是锁定的资源很长时间不释放有时实在没办法只好重启数据库。现在提供一种方法解决这种问题那就是在ORACLE中杀不掉的在OS一级再杀。 1.下面的语句用来查询哪些对象被锁 select object_name,machine,s.sid,s.serial# from v$locked_object l,dba_objects o ,v$session swhere l.object_id o.object_id and l.session_ids.sid; 2.下面的语句用来杀死一个进程alter system kill session 24,111; (其中24,111分别是上面查询出的sid,serial#) 【注】以上两步可以通过Oracle的管理控制台来执行。 3.如果利用上面的命令杀死一个进程后进程状态被置为killed但是锁定的资源很长时间没有被释放那么可以在os一级再杀死相应的进程线程首先执行下面的语句获得进程线程号select spid, osuser, s.program from v$session s,v$process pwhere s.paddrp.addr and s.sid24 24是上面的sid 4.在OS上杀死这个进程线程1)在unix上用root身份执行命令: #kill -9 12345即第3步查询出的spid2)在windowsunix也适用用orakill杀死线程orakill是oracle提供的一个可执行命令语法为orakill sid thread其中sid表示要杀死的进程属于的实例名thread是要杀掉的线程号即第3步查询出的spid。例c:orakill orcl 12345 ORA-00031: session marked for kill Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done. Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner. kill -9 12345 转载于:https://www.cnblogs.com/leonkobe/p/3302698.html
