An in-depth understanding of oracle-based locks

ORACLE locks have the following modes:
0: none
1: null
2: Row-S Row sharing (RS): Share table lock
3: Row-X (RX): used for Row Modification
4: Share lock (S): blocks other DML operations
5: S/Row-X dedicated shared rows (SRX): block other transaction operations
6: exclusive dedicated (X): used for independent access

The larger the number, the higher the lock level, the more operations 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 Share.
Select for update when a cursor is opened using the for update sub-string in the dialog box, all data rows in the returned dataset are 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, and Lock Row Exclusive.
Inserting the same record before the commit operation does not respond, because the last 3 lock will always wait for the last 3 lock, and we must release the previous one to continue working.

Level 4 locks include: Create Index, Lock Share locked_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.

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

Level 5 locks: Lock Share Row Exclusive
Specifically, when there is a primary foreign key constraint, update/delete...; may produce 4 or 5 locks.

When the parent table is modified, if the corresponding record of the child table exists, a ORA-02292 is generated
Exception; otherwise, only the TX lock and RX lock are applied to the parent table;

When the sub-table is modified, in addition to adding the TX lock and RX lock to the sub-table
The SS lock is also added, which is logical because the parent table must satisfy the integrity of the reference;
That is, apply the SS lock to the parent table to avoid modifying the parent table.

All the modifications mentioned in the above locks are performed on the Child parent table with reference integrity fields.

Level 6 locks include Alter table, Drop table, Drop Index, Truncate table, and Lock Exclusive.
Take the DBA role as follows:
Copy codeThe Code is as follows:
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 Status:
Select machine, username, sid, serial #, type, osuser, status from v $ session;

You can use the following SQL statement to view the locks in the current database:
Copy codeThe Code is as follows:
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 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: 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.

If the process state is set to "killed" after a process is killed by using the preceding command, but the locked resources are not released for a long time, then, the corresponding process (thread) can be killed at the OS level. First, execute the following statement to obtain the process (thread) Number:
Copy codeThe Code is as follows:
Select spid, osuser, s. program
From v $ session s, v $ process p
Where s. paddr = p. addr and s. sid = # sid (# sid is the sid found above)

4. Killing the process (thread) on the OS ):
1) run the command as root on unix:
# Kill-9 # spid (the spid found above)
2) In windows, use orakill to kill the thread. orakill is an executable Command provided by oracle. The syntax is as follows:
Orakill sid thread
Where:
Sid: indicates the Instance name of the process to be killed.
Thread: the number of threads to be killed, that is, the spid found above.
Example: c:> orakill orcl # spid
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 worker cannot completely clear the lock.
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 another difference. First, they are generated differently. Second, after the rx lock, other transactions cannot even read the changed result. Rs has no read problems for other transactions, so it is called rs. Although they all refer to the table level Prevents other transactions from manually locking the table, one is for exclusive reading or writing, and the other is for exclusive write access. this is what rs s (share) shows.