(Reprinted from: http://fengyu.china.com/lock.htm) Oracle Lock Management
2003-03 Yu Feng
Oracle lock has the following modes:
0: None
1: null empty
2: ROW-S Row Sharing (RS): Shared table lock
3: ROW-X line special (RX): Mode for rows
4: Share shared lock (s): block other DML operations
5: S / ROW-X share line dedicated (SRX): Block other transaction operations
6: Exclusive dedicated (X): Separate access
The higher the number, the higher the lock level, the more affected operations.
A general query statement such as Select ... from ...; is less than 2 locks, sometimes appears in V $ locked_Object.
Select ... from ... for update; is 2 lock.
When the conversation uses the for Update substring to open a camper,
All returned data lines will be in the row level (ROW-X) exclusive lock,
Other objects can only query these data lines and cannot perform Update, DELETE, or SELECT ... for Update operations.
INSERT / UPDATE / Delete ...; is a lock.
There is no reaction before inserting the same record before no commit.
Because the latch of the latter is waiting for the lock, we must release the previous one to continue working.
3, 4 locks are generated when creating an index.
Locked_Mode is 2, 3, 4 does not affect DML (INSERT, DELETE, UPDATE, SELECT) operation,
But DDL (ALTER, DROP, etc.) will prompt ORA-00054 errors.
Update / delete ... may result in 4, 5 locks when there is a primary key constraint.
DDL statements are 6 locks.
With the DBA role, see the current database locks can be used as the following SQL statement:
Select Object_ID, Session_ID, Locked_Mode from V $ locked_Object;
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.
When you use it directly to connect to the database,
Also 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 does not completely clear the lock problem.
Remember to use Alter System Kill Session 'SID, Serial #'; kill an abnormal lock.
