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

Brief introduction of the lock:

Oracle implements concurrent access control, which is realized by locking.

Locks are divided into pessimistic locks (transaction scheduling may be scheduled serially)-row-level locks at the transaction level.

Optimistic lock (transaction is scheduled serially)-timestamp and transaction scheduling based on validation.

The so-called optimistic lock means that the serial mechanism of transaction scheduling will not be disrupted to ensure that the data changes between each transaction will not be affected by each other and maintain data consistency.

Oracle implicitly executes locking mechanism by default, using row-level locks with the least number of rows to store locked row information in the database block.

Locks are for the entire process of a transaction, and the lock automatically disappears after a commit or rollback is issued.

The locking method of the lock:

Oracle uses row-level granularity to lock objects, and a transaction affects several rows of data.

The smaller the granularity, the greater the concurrency supported.

Lock type:

The object of the lock can be a user-level table, index, etc., or a shared data structure (data dictionary).

DML lock

For protecting the user's table and index, an exclusive row-level lock is added to the row of the transaction operation to prevent the same data row from being changed by multiple transactions at the same time. Before a transaction is committed, the latter transaction can only wait until the previous transaction is committed.

A DDL table lock is held at the same time as the DML lock, and another transaction cannot define the table structure operation during the DML (insert,update,delete) operation. The same transaction can be operated, and the waiting time depends on the operation ddl_lock_timeout

The default value of this parameter is 0

Show parameter ddl_

NAME TYPE VALUE

Ddl_lock_timeout integer 0

Wait events can be set at the session level

Alter session set ddl_lock_timeout = 30

-sets the time for the same transaction ddl lock to wait for the dml lock to 30 seconds. By default, it does not wait for immediate execution.

The operation waiting time exceeds the set value and becomes invalid directly. The premise is that the dml operation takes a long time and exceeds the ddl wait time.

DDL lock

There is a DML lock with the corresponding table in the DDL lock.

Latch

Latches are used to protect shared data structures in sga and control access to memory structures.

Data dictionary lock

The corresponding lock when the dictionary object is modified

Distributed lock

Locks used in RAC structures or distributed systems

Internal lock

Locks used by Oracle to protect access to data files, tablespaces, and rollback segments

Lock Management:

In the practical application system, there will be the phenomenon of process waiting, which is caused by blocking lock, which is commonly known as' deadlock', which is wrong.

A deadlock is a dead cycle in which two processes wait for each other. A blocking lock is a process waiting for another process, and after the current process commits or rolls back, the latter process can gain control of the corresponding object.

Deadlocks are handled internally by Oracle. Request transactions after killing without human involvement.

Blocking lock

A blocking lock occurs when a transaction's lock on one object blocks or blocks other transactions from accessing the same object.

You can query the currently blocked session ID in the view dba_blocks

Select * from dba_blockers

HOLDING_SESSION

-

thirty-nine

You can also query the currently blocked session in the v$session view

Select t.SID,t.USERNAME,t.BLOCKING_SESSION_STATUS,t.BLOCKING_SESSION from v$session t

Where t.BLOCKING_SESSION_STATUS = 'VALID'

SID USERNAME BLOCKING_SE BLOCKING_SESSION

-

42 halee VALID 39

Deadlock

Two transactions hold row-level exclusive locks on the same object at the same time, and blocking each other will form a deadlock. Oracle automatically kills the post-holder of the modified object lock

Manage View

Dba_blockers-record the processes blocking other processes in the current library

Dba_waiters-record waiting process, blocked process, lock type, lock type to be requested

Select * from dba_waiters

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2

42 39 Transaction Exclusive Exclusive 39214 2964

Dba_locks-record the specific lock type for each session

Generate the lock in the library

Execute the utllockt.sql file in the installation directory to generate the specific conditions corresponding to the lock waiting

WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2

--

39 None

42 Transaction Exclusive Exclusive 393241 2964

Return result description:

Session 42 is waiting for session 39 to commit or roll back to the exclusive lock

You can use the following sql to query the operation corresponding to the lock

SELECT / * + ORDERED * / sql_text

FROM v$sqltext a

WHERE (a.hash_value, a.address) IN (

SELECT DECODE (sql_hash_value

0, prev_hash_value

Sql_hash_value

),

DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)

FROM v$session b,dba_blockers c

WHERE b.SID = c.holding_session)

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.