Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The mechanism of oracle lock

2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

In modern multi-user and multi-task systems, it is inevitable that multiple users access a shared object at the same time, which may be a table, row, or memory structure. in order to solve the problems of data security, integrity and consistency caused by multiple users' concurrent access, there must be a mechanism to serialize concurrent access to these shared resources, and locks in oracle can provide such a function. Before a transaction operates on an object, it sends a request to the system to add a corresponding lock. After locking, the transaction has certain control over the data object. Before the transaction releases the lock, other transactions cannot update the data object (you can do the select action, but select uses the previous mirrored data in undo).

Classification of Oracle locks

Oracle locks can basically be divided into two categories.

A: shared lock (share locks) is also called read lock, s lock

B: exclusive lock (exclusive locks), also known as write lock, x lock

There are two basic types of locks in the database: Exclusive Locks (X lock) and shared lock (Share Locks, S lock). When an exclusive lock is added to a data object, it cannot be read or modified by other transactions. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic lock types to control the concurrency of database transactions.

Classified by the content of lock protection

Oracle provides a multi-granularity blocking mechanism, which is divided by protection object, according to which it can be divided into

A:dml lock, data locks data lock, used to protect the integrity and consistency of data

B:ddl lock, dictionary locks dictionary lock, used to protect the structure of data objects, such as the definition of table,index

C: internal locks and latches internal locks and latchs are used to protect the internal structure of the database, such as sga memory structure

Dml lock

DML lock mainly includes TM lock and TX lock, in which TM lock is called table-level lock, there are five kinds of TM lock, and TX lock is called transaction lock or row-level lock. When Oracle executes the delete,update,insert,select for update DML statement, oracle first automatically requests a lock of type TM on the table you want to operate on. When the TM lock is obtained, the TX type lock is automatically applied for, and the lock log bit (lb, namely lock bytes) of the actual locked data row is set. After the record is locked by a session, other conversations that need to access the locked object will wait for the lock to be released on a first-in-first-out basis. For select operations, no lock is required, so even if the record is locked, the select statement can still be executed. In fact, in this case, oracle uses the contents of undo for consistent reading.

In an Oracle database, when a transaction initiates a DML statement for the first time, it acquires an TX lock that is held until the transaction is committed or rolled back. There are only X locks (exclusive locks) on data rows, which means that TX locks can only be exclusive locks, and there is no point in setting shared locks on record rows. When two or more sessions execute DML statements on the same record in the table, the first session locks the record and the other sessions wait. When the first session is committed, the TX lock is released so that other sessions can be locked.

On the data table, oracle defaults to a shared lock. When executing the dml statement, oracle will first apply for a shared lock on the object to prevent other sessions from making ddl statements on this object. After successfully applying for the shared lock on the table, other places will be added to the affected records to prevent other sessions from modifying these actions.

In this way, when checking the compatibility of the TX lock before the transaction is added, there is no need to check the lock flag line by line, but only to check the compatibility of the TM lock mode, which greatly improves the efficiency of the system. The TM lock includes many modes, such as SS, SX, S, X and so on, which is represented by 0Mel 6 in the database. Different SQL operations produce different types of TM locks. As shown in Table 1.

Introduction to lock-related performance views

V$lock

The sid of the SID session, which can be associated with v$session

TYPE distinguishes the types of objects protected by the lock, such as tm,tx,rt,mr, etc.

The ID1 lock represents 1. For more information, please see the instructions below.

The ID2 lock represents 2. See the instructions below for details.

LMODE lock mode, see below

Lock mode applied for by REQUEST, same as lmode

The time that CTIME has held or waited for the lock

Whether BLOCK blocks other session lock application 1: block 0: do not block

The value of LMODE is 0, 1, 2, 3, 4, 5, 5, 6, the larger the number, the higher the lock level, the more operations are affected.

Level 1 lock:

Select sometimes appears in v$locked_object.

Level 2 lock is RS lock

The corresponding sql are: Select for update, Lock xxx in Row Share mode,select for update

When you use the for update substring to open a cursor, all rows in the returned set will be at the Row-X level

Occupy locking, 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 are: Insert, Update, Delete, Lock xxx in Row Exclusive mode, no commit

Inserting the same record before will not respond, because the lock of the latter 3 will always wait for the last lock of 3, we

The last one must be released before you can continue to work.

Level 4 lock is S lock

The corresponding sql are: Create Index, Lock xxx in Share mode

Level 5 lock is SRX lock

The corresponding sql are: Lock xxx in Share Row Exclusive mode, update when there are primary foreign key constraints

/ delete...; a lock of 4pm 5 may be generated.

Level 6 lock is X lock

The corresponding sql are: Alter table, Drop table, Drop Index, Truncate table, Lock xxx in Exclusive

Mode

The value meaning of ID1,ID2 varies according to the value of 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. The ID2 value is 0.

For TX locks

ID1 represents the rollback segment number and transaction slot slot number number occupied by the transaction in decimal values, in the form of a group:

0xRRRSSSS NUMBER,SSSS=SLOT NUMBER, RRRSS, undo, undo, etc.

ID2 represents the number of times around the wrap in decimal values, that is, the number of times the 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 locked object, which can be associated with dba_objects

SESSION_ID holds the session_id of the lock, which can be associated with v$session

ORACLE_USERNAME holds the oracle account of the lock

OS_USER_NAME holds the operating system account of the lock

The process number of the PROCESS operating system, which can be associated with v$process

LOCKED_MODE lock mode, with the same meaning as v$lock.lmode

The contents of Dba_locks and v$lock are similar, slightly

V$session if a session is blocked because some rows are locked by another session, the following four fields in this view list information about the objects to which those rows belong

The file number of the line that ROW_WAIT_FILE# is waiting for

The object_id to which the row that ROW_WAIT_OBJ# is waiting belongs to

The block to which the row that ROW_WAIT_BLOCK# is waiting belongs to

The position of the row that ROW_WAIT_ROW# is waiting for in blcok

Manually release the 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 methods of locking table process

1 the first step is to check the locked information.

The query statement is select from v$locked_object

You can find the following fields to see the operation information of some table lockers.

SESSION_ID: the sid value that we need to look at in the v$session table

ORACLE_USERNAME: the oracle account used by the table lock

OS_USER_NAME: lock the user name of the operating system

2. Through the above three points, we can lock to some of the watch locks.

If you are not sure, you can check select from v$session t where sid = '572'. The sid here is the SESSION_ID found in the vested locketed _ object table above. We can find many related fields. We only look at a few fields where we need to process the transaction.

The fields sid and SERIAL# are used to kill the process.

PROGRAM can view the tools used by users.

PREV_HASH_VALUE can use this field to find out which sql statement executes the lock table.

3. You can view the relevant lock table statements through select * from v$sql where hash_value = 'PREV_HASH_VALUE'.

4. Make sure that statements that can be removed and do not affect other businesses can be executed if they can be killed.

ALTER system KILL session '2281 and 27935; the two numbers here are sid and SERIAL#, respectively.

5. Under normal circumstances, the relevant unlocking operation can be completed at this step. If the prompt is unable to operate, you can check it.

Select pro.spid from v$session ses,v$process pro where ses.sid=1335 and ses.paddr=pro.addr

Here sid is the SESSION_ID of 1 above.

Check out the spid and then view the relevant processes in Linux.

Ps-ef | grep spid

Sid is the sid above, and then you can kill the process.

KILL-9 'just found out the SPID'

= find blocked users first =

SELECT / + rule / s.username

Decode (l.type.thumbnadecommerce) 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 sjaw vandlock lje dbaum objects o

WHERE l.sid = s.sid

AND l.id1 = o.object_id (+)

AND s.username is NOT Null

-- = kill drop that process = =

Alter system kill session 'sid,serial#'

-- = if not released, kill the corresponding OS process

Kill-s 15 pid

-- = clear cache = =

Alter system set events = 'immediate trace name flush_cach

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report