Oracle lock management

xiaoxiao2021-03-06  136

Recently, a system failure was encountered. The phenomenon is a crash in the client's machine for no reason.

Find related information, I feel that it is likely to be an Oracle lock problem. Put the relevant knowledge first.

Oracle locks have the following modes: 0: None 1: Null Empty 2: Row-S Row Sharing (RS): Shared table lock, Sub Share 3: Row-X row exclusive (RX): For rows, Sub Exclusive 4: Share Shared Lock (S): Block Other DML Operation, Share 5: S / Row-X Shared Row (SRX): Block other transaction operations, Share / Sub Exclusive 6: Exclusive exclusive (X): Separate access Use, the higher the EXCLUSIVE number, the higher the lock level, the more affected operations. Level 1 lock has: SELECT, sometimes in V $ locked_object appears. The 2-level lock has: SELECT FOR UPDATE, LOCK for Update, Lock Row Share Select FOR UPDATE When the conversation uses the for Update Skeker, all the data lines in the returned set will be in the row (ROW-X) single lock Other objects can only query these data lines and cannot perform Update, Delete or SELECT for Update operation. Level 3 locks: INSERT, UPDATE, DELETE, LOCK ROW EXCLUSIVE No contact before inserting the same record, because the latch of the last 3 will wait for a 3 lock, we must release the last one to continue working . The 4-level lock has: CREATE INDEX, LOCK Share Locked_Mode is 2, 3, 4 does not affect DML (INSERT, DELETE, UPDATE, SELECT), but DDL (ALTER, DROP, etc.) will prompt ORA-00054 errors. 00054, 00000, "Resource Busy and Acquire with NOWAIT Specified" // * cause: Resource Intesed is busy. // * action: retry if Necessary. 5-level locks: Lock Share Row Exclusive Specific TERRESTRODM Update / delete ... may result in 4, 5 locks. Level 6 locks: Alter Table, Drop Table, Drop INDEX, TRUNCATE TABLE, LOCK EXCLUSIVE Take the current database locks in the current database locks: Col Owner For A12 Col Object_name for a16 SELECT B.OWNER, B .object_name, l.session_id, l.locked_mode from v $ locked_object l, dba_objects b where b.object_id = l.object_id / select t2.username, t2.sid, t2.serial #, t2.logon_time from v $ locked_object t1, v $ session t2 where t1.session_id = t2.sid ORDER BY T2.Logon_time / If there is a long-term appearance, there may be no released locks. We can use the following SQL statement to kill the long-term non-normal lock: Alter System Kill Session 'SID, Serial #'; if there is a lock problem, a DML operation may wait for a long time.

转载请注明原文地址:https://www.9cbs.com/read-127582.html

New Post(0)