In the modern multi-user multitasking system, there will inevitably be multiple users accessing a shared object at the same time, this object may be a table, row, or memory structure, in order to solve the data security, integrity and consistency of multiple user concurrency access, there must be a mechanism, To serialize the concurrency access to these shared resources, the lock in Oracle can provide the ability to make a request to the system before the transaction is performed on an object, lock it appropriately, and lock the transaction with some control over the data object before the transaction releases the lock. Other transactions cannot update this data object (you can do a select action, but select uses the pre-mirrored data in undo).
Classification of Oracle Locks
Oracle locks can basically be divided into two categories
A: Shared lock (share locks)? Also called read lock, S lock
B: Exclusive lock (exclusive locks) also called write lock, X lock
There are two basic types of locks in the database: Exclusive lock (Exclusive Locks, X Lock) and shared lock (Share Locks, S lock). When a data object is added to an exclusive lock, other transactions cannot read and modify it. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic types of locks to control the concurrency of a database's transactions.
Category of content protected by lock
Oracle provides a multi-granularity blocking mechanism that can be divided into protected objects and
A:DML Lock, data locks Lock, to protect data integrity and consistency
B:ddl Lock, dictionary locks dictionary lock, used to protect the structure of the data object, such as the definition of Table,index
C: Internal lock and latch internal locks and latchs to protect the internal structure of the database, such as the SGA memory structure
DML lock
DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and there are S,X,SR,SX,SRX five types of TM locks, and TX locks are called transaction or row-level locks. When Oracle executes delete,update,insert,select for update? DML statement, Oracle first automatically requests a TM-type lock on the table to be operated on. When the TM lock is obtained, the lock of the TX type is automatically applied, and the lock flag bit (lb, lock bytes) of the data row that is actually locked is placed. When a record is locked by a session, other sessions that require access to the locked object wait for the lock to be released in a first-in, out-of-order manner, and for a select operation, no lock is required, so even if the record is locked, the SELECT statement can be executed, in fact, in this case, Oracle is implemented using the content of undo for consistent reads.
In an Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time, and the lock remains until the transaction is committed or rolled back. There is only an X lock (exclusive lock) on the data line, which means that the TX lock can only be an exclusive lock, and it is meaningless to set a shared lock on the record line. When two or more sessions execute DML statements on the same record on a table, the first session is locked on that record, and the other sessions are waiting. When the first session is committed, the TX lock is released and other sessions can be locked.
On the datasheet, Oracle defaults to a shared lock, and when executing a DML statement, Oracle requests a shared lock on the object, prevents other sessions from making DDL statements on the object, successfully requests a shared lock on the table, and then adds it to the affected record to prevent other sessions from modifying the action.
In this way, checking the compatibility of the TX lock before the transaction is locked, it is necessary to check the compatibility of the TM lock mode and greatly improve the efficiency of the system. TM locks include SS, SX, S, X and other modes, which are represented in the database by 0-6. Different SQL operations produce different types of TM locks. As shown in table 1.
Description of the performance view associated with the lock
V$lock
Sid????????? The SID of the session, which can be associated with V$session???
TYPE???????? Distinguish the type of object that the lock protects, such as TM,TX,RT,MR, etc.
ID1????????? The lock represents 1, in detail see the following instructions????????????????
ID2????????? The lock represents 2, in detail see the following instructions???????????
Lmode??????? Lock mode, see instructions below?????????????
REQUEST????? Application lock mode, same as Lmode?????????????????
CTIME??????? The time that has been held or is waiting for the lock????????????????
BLOCK??????? Whether to block other session lock requests 1: Block 0: No blocking??
Lmode value 0,1,2,3,4,5,6, the larger the number, the higher the lock level, and the more actions are affected.
Level 1 Lock:
Select, which sometimes appears in V$locked_object.
Level 2 Lock is RS lock
The corresponding SQL is: Select for update, Lock XXX in? Row Share mode,select for update when
When a cursor is opened with the for update substring, all data rows in the returned set will be at the row level (Row-x)
Placeholder locks, other objects can only query these data rows, not update, delete, or select for update
Operation.
Level 3 lock is RX lock
The corresponding SQL is: Insert, Update, Delete, Lock xxx in Row Exclusive mode, no commit
Before inserting the same record will not react, because after a 3 lock will wait for the last 3 lock, we
You must release the previous to continue working.
Level 4 Lock is S lock
The corresponding SQL is: Create Index, Lock xxx in Share mode
5 level lock is SRX lock
The corresponding SQL has: Lock xxx in Share Row Exclusive mode, update when there is a primary foreign KEY constraint
/delete ...; A lock of 4, 5 may be generated.
Level 6 lock is x lock
The corresponding SQL is: Alter table, drop table, drop Index, Truncate table, Lock xxx in Exclusive
Mode
The value of the ID1,ID2 is different depending on the value of the type
For TM Locks
ID1 indicates that the object_id of the locked table can be associated with the Dba_objects view to obtain specific table information, with a ID2 value of 0
For TX Locks
? ID1 a decimal value that represents the rollback segment number that the firm occupies and the number of the transaction slot slot, in the form of a group:
? 0xrrrrssss,rrrr=rbs/undo Number,ssss=slot Number
ID2 the number of times a decimal value surrounds wrap, that is, the number of times a transaction slot is reused
?????????????????????????????????????????????
V$locked_object
Xidusn?????????????? Undo segment number, which can be associated with v$transaction????
Xidslot????????????? Undo Slot Number??????
XIDSQN?????????????? Serial number?????????????????????????
OBJECT_ID??????????? The object_id of the object being locked,?? can be associated with dba_objects
session_id?????????? The session_id holding the lock,???? can be associated with v$session
Oracle_username?? The Oracle account that holds the lock?????????????????????
Os_user_name?????? The operating system account that holds the lock??????????????????????
PROCESS????????????? The operating system's process number, which can be associated with v$process??????
Locked_mode??????? Lock mode, meaning same as V$lock.lmode
Dba_locks and V$lock content are similar, slightly
V$session If a session is blocked because some rows are locked by another session, the following four fields in the View list information about the objects to which the rows belong
The file number of the line where the row_wait_file# waits
Row_wait_obj#? The object_id to which the waiting line belongs
BLOCK where the row_wait_block# waiting line belongs
row_wait_row#?? The position of the waiting line in Blcok
Manual release Lock
Alter system kill session ' sid,serial# ';
?
Select from V$lock;
Select from V$locked_objecit;
Select * from V$session;
Alter system kill session ' sid,serial$ '
Several ways to lock the table process
1 First to view the information that is locked. The
Query statement is select from V$locked_object;
You can find the following fields to see the operation information of some of the lock-table operators?
SESSION_ID: We need to see the SID value in the V$session table
Oracle_username: The ORACLE account used by the lock table?
Os_user_name: Lock table The user name of this operating system
2, through the above three points we can lock to the partial lock table.
If you are not sure that you can view the select from V$session t where sid = ' 572 ' The SID here is what we found in the V$locked_object table above session_id can find many related fields, We only look at the few fields that we need to process the transaction
SID, serial# These two fields are the fields used to kill the process.
Program can view the tools used by the consumer
Prev_hash_value can be viewed by this field in which SQL statement executes the lock table
3, via select * from v$sql where hash_value = ' Prev_hash_value ' can view the related lock table statement.
4, determine can be removed without affecting other business statements can be killed can be executed
ALTER system kill session ' 2281,27935 '; the two numbers here are SID, serial#, respectively.
5, normally to this step can complete the relevant unlock operation, if the prompt cannot be manipulated can view
Select Pro.spid from V$session ses,v$process Pro where ses.sid=1335 and ses.paddr=pro.addr;? The
Sid here is the session_id above 1.
Find the SPID and then view the related processes in Linux
Ps-ef|grep? spid
SID is the SID above and can then kill the process
KILL-9 ' The spid just detected '
================ First find the blocked user ====================
SELECT/+ rule /s.username,
Decode (L.type, ' TM ', ' TABLE LOCK ',
' TX ', ' ROW LOCK ',
NULL) Lock_level,
O.owner,o.object_name,o.object_type,
S.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
From V$session s,v$lock l,dba_objects o
WHERE L.sid = S.sid
and L.ID1 = o.object_id (+)
And S.username is not Null
--==========kill off that process ==============================
Alter system kill session ' sid,serial# ';
--========== if not released, kill the corresponding OS process ===============
Kill-s PID
--========== Clear Cache =====================================
Alter system SET events = ' Immediate Trace name Flush_cach
Mechanism of Oracle Lock
