Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "which types of locks are divided by oracle according to the type of operating resources". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Overview of database lock

Oracle automatically locks operation resources during transaction execution to prevent other transactions from making destructive access to the same resource. The database automatically adds various types of locks to resources according to different types of operating resources.

According to the type of operation resources, oracle classifies locks into the following categories

DML locks: protect data, such as table locks that lock the table, row-level locks that lock selected rows

DDL locks: definitions of protection objects, such as data dictionaries for tables and views

System locks: protect the structure of internal databases, such as data files, latch,mutexes, and internal locks, which are implemented automatically

Dml lock

A DML lock, also known as a data lock, is used to ensure data integrity when multiple users execute concurrent transactions.

For example, a DML lock can prevent two users from buying the last book in an online store. DML statements automatically acquire the following types of locks: row-level lock TX and table lock TM

Example

SQL > update t_test set axi1

2 rows updated.

It can be seen that the generating transaction will add two kinds of locks to the original basis, one is the table lock tm, the lock mode is row-level exclusive lock, the other is row lock tx, and the lock mode is exclusive lock.

SQL > /

SID TY ID1 ID2 LMODE REQUEST

25 AE 133 0 4 0

25 TX 65566 1359 6 0 dml lock

25 TM 76989 0 3 0 dml lock

SQL > desc dba_dml_locks

Name Null? Type

-

SESSION_ID NUMBER

OWNER NOT NULL VARCHAR2 (128)

NAME NOT NULL VARCHAR2 (128)

MODE_HELD VARCHAR2 (13)

MODE_REQUESTED VARCHAR2 (13)

LAST_CONVERT NUMBER

BLOCKING_OTHERS VARCHAR2 (40)

SQL > select session_id,owner,name,mode_held,mode_requested,last_convert,blocking_others from dba_dml_locks

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS

-

25 USER_DDL T_TEST Row-X (SX) None 1052 Not Blocking

Oracle stores lock-related information in the data block in which the row is locked (the data block that the transaction needs to modify). The database uses the queue mechanism to acquire row-level locks. If a transaction requires a lock of unlocked rows, the transaction places a lock in the data block, and each row modified by the transaction points to the transaction ID in the data block header (ITL). When the transaction ends, the transaction ID remains in the ITL in the block header. If a different transaction wants to modify a row of data, the database will use the ID of the original transaction in ITL to determine whether the transaction still exists and the lock exists by querying the relevant dynamic view. If the lock is still active, the session queues to wait for the notification after the transaction ends. If the lock is not active, the transaction gets the lock and updates the ITL table.

Summary

Oracle records lock information and transaction information in the data block.

If an oracle session needs to acquire a lock for a table record, it first looks up whether a lock already exists in the data block to which the table record belongs. The data structure that stores transactions and locks in the data block is called itl.

Itl in the head block of the data block

The oracle session found that the modified data block has an active transaction, that is, holding the lock, and it will wait

Oracle dml lock is implemented by queuing mechanism, that is, first come, first come, last come, and so on.

When oracle acquires the data block for modification, it needs to modify the transaction state of the data block in the itl of the data block header, indicating that the data block is being modified.

Tm lock is table lock. There will be five different lock modes. As mentioned in the previous article, I will not repeat them.

Ddl lock

When DDL operates or is associated with an object, the DDL lock protects the definition of the object. Only objects modified or referenced in the DDL statement are locked, and the database does not lock the entire data dictionary. The oracle database automatically implements DDL locks on behalf of DDL transactions.

The user cannot explicitly acquire the DDL lock. For example, a user creates a stored procedure and the database automatically acquires the DDL lock of the object introduced in the stored procedure. DDL locks prevent the modification and deletion of these objects during the compilation of stored procedures.

Summary

The ddl lock holds the data structure defined by the data

Ddl locks hold ddl locks only if they modify the data structure of the data definition or refer to the data structure of the data definition.

Data structure operations that modify data definitions, such as create table,alter table-like statements

Reference data-defined data structure operations, such as creating a stored procedure based on a source table or calling to execute a stored procedure (because the integrity of the base table must be guaranteed during the execution of the stored procedure)

The ddl lock is controlled by the oracle itself and cannot be controlled by man, so the ddl lock cannot be explicitly acquired.

Ddl locks are generally invisible because ddl operations are extremely fast

The underlying layer of ddl lock is realized by locking data dictionary.

SQL > desc dba_ddl_locks

Name Null? Type

-

SESSION_ID NUMBER

OWNER VARCHAR2 (128)

NAME VARCHAR2 (1000)

TYPE VARCHAR2 (40)

MODE_HELD VARCHAR2 (9)

MODE_REQUESTED VARCHAR2 (9)

Even if no database transaction is performed, there are still ddl locks. The following ddl locks protect the definition structures of various object types from being broken.

SQL > select session_id,owner,name,type,mode_held,mode_requested from dba_ddl_locks

SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU

-

32 SYS KUPU$UTILITIES Table/Procedure/Type Null None

32 SYS STANDARD Table/Procedure/Type Null None

32 SYS STANDARD Table/Procedure/Type Null None

32 SYS DBMS_PRVT_TRACE Table/Procedure/Type Null None

74 SYSTEM SYSTEM 18 Null None

76 SYSTEM SYSTEM 18 Null None

75 SYSTEM SYSTEM 18 Null None

74 SYS DBMS_OUTPUT Body Null No

SQL > grant execute on dbms_lock to system

Grant succeeded.

Create or replace procedure proc_t_test

As

V_cnt int

Begin

Dbms_lock.sleep (300)

Select count (a) into v_cnt from t_test

End

/

-- execution of running information related to the first two DDL test sessions of 25 and 74 respectively

SQL > select session_id,owner,name,type,mode_held,mode_requested from dba_ddl_locks where session_id in (255.74)

SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU

--

25 SYSTEM SYSTEM 18 Null None

74 SYSTEM SYSTEM 18 Null None

74 SYS DBMS_OUTPUT Body Null None

25 SYS DBMS_LOCK Body Null None

74 SYS DBMS_OUTPUT Table/Procedure/Type Null None

25 LBACSYS LBAC_EVENTS Body Null None

25 LBACSYS LBAC_EVENTS Table/Procedure/Type Null None

25 SYS DBMS_APPLICATION_INFO Body Null None

74 SYS DBMS_APPLICATION_INFO Body Null None

25 SYS DBMS_STANDARD Table/Procedure/Type Null None

74 SYS PLITBLM Table/Procedure/Type Null None

SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU

--

74 SYSTEM 73 Share None

25 SYSTEM 73 Share None

25 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None

74 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None

25 MDSYS GETMDSYSEVENT Table/Procedure/Type Null None

25 SYS DBMS_LOCK Table/Procedure/Type Null None

25 SYS DATABASE 18 Null None

74 SYS DATABASE 18 Null None

19 rows selected.

-conversation 25

Execute stored procedure

SQL > exec proc_t_test

-conversation 74

Deleting stored procedures during the execution of stored procedures

Stuck

SQL > drop procedure proc_t_test

SQL > select session_id,owner,name,type,mode_held,mode_requested from dba_ddl_locks where session_id in (255.74) and mode_held='Exclusive' or mode_requested='Exclusive'

SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU

74 SYSTEM PROC_T_TEST Table/Procedure/Type Exclusive None

SQL > /

SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU

25 SYSTEM PROC_T_TEST Table/Procedure/Type Null None lock session (lock mode is null)

74 SYSTEM PROC_T_TEST Table/Procedure/Type Exclusive None wait for session (request lock mode is exclusive)

SQL > select distinct type from dba_ddl_locks

TYPE

-

seventy-three

Table/Procedure/Type

eighteen

ten

Body

twenty-three

6 rows selected.

-it can be seen that ddl locks are generated and will not be reflected in v$lock.

SQL > select sid,type,id1,id2,lmode,request from v$lock where sid in (255.74)

SID TY ID1 ID2 LMODE REQUEST

74 AE 133 0 4 0

25 AE 133 0 4 0

-- it can be seen that a ddl lock is generated, and the session wait event holding the ddl lock is library cache pin

SID STATUS BLOCKING_SESSION EVENT

-

25 ACTIVE PL/SQL lock timer

74 ACTIVE 25 library cache pin

The wait event corresponds to the following library cache pin,library cache pin saving mechanism for sga's library cache in-memory data structure

SQL > col type for A50

SQL > col name for A35

SQL > col id1_tag for A30

SQL > col id2_tag for A30

SQL > col description for A50

SQL > select type,name,id1_tag,id2_tag,is_user,description from v$lock_type where lower (description) like'% library%'

TYPE NAME ID1_TAG ID2_TAG IS_ DESCRIPTION

V Library Cache Lock 3 hash value hash value NO Synchronizes accesses to library cache objects

E Library Cache Lock 2 hash value hash value NO Synchronizes accesses to library cache objects

L Library Cache Lock 1 hash value hash value NO Synchronizes accesses to library cache objects

Y Library Cache Pin 3 hash value hash value NO Synchronizes accesses to the contents of library cache objects

G Library Cache Pin 2 hash value hash value NO Synchronizes accesses to the contents of library cache objects

N Library Cache Pin 1 hash value hash value NO Synchronizes accesses to the contents of library cache objects

IV Library Cache Invalidation object # time stamp NO Synchronizes library cache object invalidations across instances

7 rows selected.

There are several types of ddl locks:

Exclusive ddl lock

Exclusive DDL locks prevent other sessions from acquiring DDL and DML locks. For example, deleting a table prevents DDL operations that add a column to the table at the same time, and vice versa. Exclusive DDL locks are valid for the entire DDL operation, and are automatically committed at the end of execution

Shared ddl lock

Shared DDL locks prevent other conflicting DDL operations, but allow similar DDL operations to be executed concurrently. For example, when performing a DDL operation, DDL shared locks are added to all referenced tables, and other transactions can add shared DDL locks when building stored procedures, but

It is not allowed to add other DDL locks.

Fragile analytic lock

Sql or pl/sql will hold the parsing lock of the application object. Parsing locks are used to invalidate the shared sql region when the referenced object is modified or deleted. The parsing lock is fragile because it does not allow DDL operations when DDL conflicts

When, it will be broken.

Summary

The above ddl lock is not quite the same as the dml lock and is difficult to understand. There will be special articles to share later.

System lock

Oracle uses system locks to protect internal database and memory structures. Users cannot manipulate these internal locks, which are controlled by the database itself.

The system lock is divided into latch,mutexes, internal lock internal lock

Latch latch

Latch is a bottom-level locking mechanism implemented to protect the consistency of sga memory data structures.

SQL > select count (*) from v$latchname

COUNT (*)

-

nine hundred and two

SQL > select distinct type from v$latchname

TYPE

-

SGA

OSP

SQL >

SQL > select name,hash,type from v$latchname where lower (name) like'% library%'

NAME HASH TYPE

Library cache load lock 2952162927 SGA

Mutex

Mutex is different from the above latch,latch to protect a set of objects, while mutex is lower-level, it protects only one object, it is code-level, quite low-level

SQL > select mutex_type from v$mutex_sleep

MUTEX_TYPE

-

Row Cache

Library Cache

Cursor Pin

Internal lock

It is high-level, more replicative than latch and mutex, and used for other purposes. The database has some types of internal locks:

Data dictionary cache lock

This kind of lock time is short and is used to protect the relevant content when the data dictionary entity is modified. This lock ensures that a consistent view of the object can be seen during statement parsing. data

Dictionary locks are shared and exclusive. At the end of parsing, the shared lock is released, and when the DDL operation ends, the exclusive lock is released.

File and log management lock

This lock protects a variety of files, for example, internal locks protect control files, ensuring that only one process can be modified at a point in time. Additional locks coordinate archiving and online logging. When

When a database is mounted in multi-instance sharing mode or when a single instance is mounted exclusively, the data file is locked. Because file locks identify the status of files, these locks generally last for a long time

Tablespace and undo segment locks

Used to protect tablespaces and undo segments, for example, all instances must agree on whether tablespaces are online

This is the end of the content of "which types of locks oracle divides according to the type of operating resources". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.