This article is slightly modified by Gototop according to YUFENG. Http://www.ncn.cn/ Oracle lock has the following modes: 0: none1: null empty 2: Row-S row shared (RS): shared table lock, Sub Share 3: Row-X row exclusive ( RX): Used for rows, Sub Exclusive 4: Share Complex (s): Block Other DML operations, Share5: S / Row-x share row (SRX): Block other transaction operations, Share / Sub Exclusive 6: Exclusive exclusive (X): Separate access, Exclusive
The higher the 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 for Update substrings open a cursor, all the data lines in the returned set will be in a row-class (ROW-X) exclusive lock Other objects can only query these data, 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 SHARELOCKED_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 Interested is busy./// * action: Retry if Necessary.5 Locks: Lock Share Row Exclusive Specific Termination When there is a primary key constraint Update / delete ... may result in 4, 5 locks. Level 6: Alter Table, Drop Table, Drop Index, Truncate Table, Lock Exclusive
With the DBA role, check the current database locks to use the following SQL statement:
Col Owner For A16SELECT_NAME for A16SELECT B.OWNER, B.Object_name, L.Session_ID, L.LOCKED_MODEFROM V $ locked_Object L, DBA_Objects bwhere 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 list, it may be no lock. We can use the SQL statement below to kill for a long time without release of abnormal locks:
Alter System Kill Session 'SID, Serial #';
If a lock problem occurs, a DML operation may wait for a long time without reactions.
