Deep understanding of lock based on oracle

There are several modes of locking in ORACLE:
0: none
1: null empty
2: Row-S row sharing (RS): Shared table lock
3: Row-X row-specific (RX): for row modification
4: Share Shared Lock (S): Prevents other DML operations
5: S/Row-X Shared Row Dedicated (SRX): Block other transaction operations
6: exclusive Private (X): Use for stand-alone access

The larger the number, the higher the lock level, and the more operations affected.
Level 1 locks have: Select, sometimes occurring at v $locked_object.

Level 2 locks are: Select for update, Lock For Update, Lock Row Share
select for update When a dialog opens a cursor using the for update substring, all data rows in the return set will be locked exclusively at the row level (Row-X), and other objects can only query these data rows, and cannot perform update, delete or select for update operations.

Level 3 locks are: Insert, Update, Delete, Lock Row Exclusive
Inserting the same 1 record before commit will not respond, because the last 3 lock will wait for the last 3 lock, and we must release the last 1 to continue working.

Level 4 locks: Create Index, Lock Share locked_mode are 2, 3, 4, which does not affect DML (insert, delete, update, select) operation, but DDL (alter, drop, etc.) operation will prompt ora-00054 error.

ORA-00054: resource busy and acquire with NOWAIT specified
(ORA-00054: Resource is busy, but NOWAIT is specified to get the resource)

Level 5 locks: Lock Share Row Exclusive
Specifically, update/delete with primary foreign key constraints...; Locks of 4 or 5 may be generated.

When the parent table is modified, if the records corresponding to the child table exist, ORA-02292 will be generated
Abnormal; Otherwise, only TX lock and RX lock are added to the parent table;

When modifying the sub-table, in addition to adding TX lock and RX lock to the sub-table; Parent table
The SS lock is also added, which is logical because the parent table must satisfy referential integrity at this time;
That is, add SS lock to the parent table to avoid modifying the parent table at this time.

The modifications mentioned above are all field operations with reference integrity in the child parent table.

Level 6 locks are: Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
In the DBA role:

col owner for a12
col object_name for a30
col machine for a32
    col USERNAME for a16
    col OSUSER for a16

View the current session sid:
select sid from v$mystat where rownum < =1 ;
View the current session situation:
select machine,username,sid,serial#,type,osuser,status from v$session;

The following SQL statement can be used to view the locks in the current database:

select s.username,o.object_name,s.machine,s.sid,s.serial#,k.type
from v$locked_object l,dba_objects o ,v$session s,v$lock k
where l.object_id  =  o.object_id and l.session_id=s.sid and k.sid=s.sid

If there is a long-term occurrence of 1 column, it may be a lock that has not been released. We can use the following SQL statement to kill abnormal locks that have not been released for a long time: alter system kill session 'sid, serial #';
If there is a lock problem, an DML operation may wait for a long time without responding.

If the process state is set to "killed" after killing a process with the above command, but the locked resource has not been released for a long time, then the corresponding process (thread) can be killed at os1 level. First, execute the following statement to obtain the process (thread) number:

select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=#sid  ( #sid It's from above sid ) 

4. Kill the process (thread) on OS:
1) On unix, execute the command as root:
# kill-9 # spid (i.e. spid found above)
2) Kill threads in windows with orakill, which is an executable command provided by oracle with the syntax:
orakill sid thread
Among them:
sid: Indicates the instance name of the process to be killed
thread: Is the thread number to be killed, which is the spid found above.
Example: c: > orakill orcl #spid
When using a direct connection to the database, 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 produce more than one lock, killing OS process can not completely clear the lock problem.
What is the difference between rs and rx?
Row Exclusive (RX): INSERT, UPDATE, DELETE
Row Share (RS): SELECT... FOR UPDATE
Both are TM table lock modes held by DML transactions. There is still one difference, one is not one. 2 is the rx lock, other transactions can not even read the result of the change. rs is no problem to read for other transactions, so it is called rs. Although they all refer to Prevents other transactions from manually locking the table at the table level, one is for exclusive reading or writing, and the other is for exclusive write access. This is where s (share) of rs appears.