Oracle's Locks

Oracle locks are mainly TX and TM two kinds of locks, of which TM has the following modes

0:none
1:null Empty
2:row-s line Sharing (RS): Shared row locks
3:row-x line-specific (RX): For line modifications
4:share shared Lock (S): block other DML operations
5:s/row-x shared row Private (SRX): Blocking other transaction operations
6:exclusive dedicated (X): Independent access use
　　
The larger the number, the higher the lock level, and the more operations it affects.

Level 1 locks are: Select, sometimes appearing in V$locked_object.
　　
Level 2 locks are: Select for Update,lock for Update,lock Row Share
Select for update when a dialog uses a for update substring to open a cursor, all data rows in the returned set are in row-level (row-x) exclusive locks, and other objects can only query the rows of data and cannot update, delete, or select for Update operation.
　　
Level 3 locks are: Insert, Update, Delete, lock Row Exclusive
Inserting the same record without a commit will not respond, because the next 3 lock will wait for the previous 3 lock and we must release the previous one to continue working.
　　
Level 4 locks are: Create Index, lock Share Locked_mode for 2,3,4 does not affect DML (Insert,delete,update,select) operations, but DDL (Alter,drop, etc.) The operation prompts a ora-00054 error.
　　
Ora-00054:resource busy and acquire with nowait specified
(ORA-00054: Resource is busy, but specifies to obtain resources in nowait way)
　　
Level 5 locks are: Lock Share Row Exclusive
In particular, there is the main foreign key constraints update/delete ...; A lock of 4, 5 may be generated.
　　
When the parent table is modified, if the corresponding record of the child table exists, it generates ORA-02292
Exception; TX Locks and Rx Locks are only added to the parent table;
　　
When the child table is modified, in addition to the child table Gaga TX lock and RX lock, the parent table
Also added SS lock, which is logical, because the parent table must satisfy the referential integrity at this time;
That is, the parent table plus SS Lock, to avoid the parent table at this time to modify the operation.
　　
The above lock-said modification is a field operation that has referential integrity to the 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

To view the current session SID:
Select Sid from V$mystat where rownum<=1;

View 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:

Select/*+ Rule * *
Ls.osuser Os_user_name,
Ls.username user_name,
Decode (Ls.type, ' RW ', ' Row wait enqueue lock ', ' TM ', ' DML enqueue lock ', ' TX ',
' Transaction enqueue lock ', ' UL ', ' User supplied lock ') Lock_type,
O.object_name object,
Decode (Ls.lmode, 1, NULL, 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' Share ', 5, ' Share Row Exclusive ',
6, ' Exclusive ', null) Lock_mode,
O.owner,
Ls.sid,
ls.serial# Serial_num,
LS.ID1,
Ls.id2
From Sys.dba_objects O,
(select S.osuser, S.username, L.type, L.lmode, S.sid, s.serial#, L.ID1, L.id2
From V$session S, V$lock l
where s.sid = l.sid) ls
where o.object_id = Ls.id1
and O.owner <> ' SYS '
Order by O.owner, O.object_name

Or:
Select s.username,o.object_name,s.machine,s.sid,s.serial#
From V$locked_object l,dba_objects O, v$session s
where l.object_id = o.object_id and l.session_id=s.sid;

If there is a long-term occurrence of a column, it is possible that there is no release of the lock. We can use the following SQL statement to kill a long time did not release the abnormal lock:
　　
Alter system kill session ' sid,serial# ';
　　
If a lock problem occurs, a DML operation may wait for a long time without a response.
　　
If you use the above command to kill a process, the process state is set to "killed", but the locked resource is not released for a long time, then you can kill the corresponding process (thread) at the OS 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是上面查到的sid)
　　
4. Kill this process (thread) on the OS:
1 on UNIX, execute the command with root:
#kill-9 #spid (that is, the SPID found above)
2 in Windows to kill a thread with Orakill, Orakill is an executable command provided by Oracle, syntax:
Orakill SID Thread
which
Sid: Represents the instance name of the process to kill
Thread: Is the number of threads to kill, that 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, kill the OS process and can not completely remove the lock problem.


Where 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 a difference, one is produced is not the same. Second, after the RX lock, the other transactions can not even read the result of the change. And RS is no problem for other transactions to read, so call it Rs. Although all refer to the table on the level prevents other transactions from manually locking the table, one for exclusive reading or writing, and another for EXCLU Sive Write access. This is where Rs s (share) behaves.
Hope it helps