The higher the level of the six major ORACLE lock modes, the more impact

The higher the level of the six major ORACLE lock modes, the more impact the ORACLE locks have the following modes: 0: none1: null 2: Row-S Row sharing (RS): Share table lock, sub Branch 3: Row-X exclusive (RX): used for Row modification, sub exclusive4: Share lock (S): blocks other DML operations, limit 5: s/Row-X shared Row exclusive (SRX): blocks other transaction operations. share/sub exclusive6: exclusive (X): used for independent access. The larger the exclusive number, the higher the lock level, the more actions are affected. Level 1 locks include Select, which sometimes appears in v $ locked_object. Level 2 locks include Select for update, Lock For Update, and Lock Row Shareselect for update. When a cursor is opened using the for update substring in the dialog box, all data rows in the return set will be locked exclusively at the Row level (Row-X). Other objects can only query these data rows and cannot perform update, delete, or select for update operations. Level 3 locks include Insert, Update, Delete, Lock Row Exclusive, and Insert the same record before commit, because the last three locks will always wait for the last three locks, we must release the previous one to continue working. Level 4 locks include: Create Index, Lock Sharelocked_mode is 2, 3, and 4, which does not affect DML (insert, delete, update, select) operations, but DDL (alter, drop, etc) an ora-00054 error is prompted for the operation. 00054,000 00, "resource busy and acquire with NOWAIT specified" // * Cause: Resource interested is busy. // * Action: Retry if necessary.5 locks include: Lock Share Row Exclusive. Specifically, update/delete when the master foreign key constraint Exclusive ...; locks may be 4 or 5. Level 6 locks include:

Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive

 

You can use the following SQL statement to view the locks in the current database as a DBA:

<CCID_NOBR><CCID_CODE>col owner for a12col object_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 a long-standing column appears, the lock may not be released. We can use the following SQL statement to kill abnormal locks that have not been released for a long time:

<CCID_NOBR><CCID_CODE>alter system kill session 'sid,serial#';

 

If a lock problem occurs, a DML operation may wait for a long time and no response is returned. When you connect to the database directly, do not use the OS system command $ kill process_num or $ kill-9 process_num to terminate the user connection, because a user process may generate more than one lock, killing the OS process cannot completely clear the lock.