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

1. Definition:

The lock mechanism is used to manage concurrent access to shared resources (serializing concurrent access to these shared resources) while also providing data integrity and consistency.

2. Oracle lock features:

Locking is not a rare resource in Oracle

Oracle doesn't automatically upgrade locks, never.

In Oracle, row-level locks have no associated overhead

3. Oracle lock classification

DML lock

DDL lock

Internal lock or LATCH

4. Lock diagram

Lock code

Lock mode name

Lock mode abbreviation

Lock mode level

Remarks

Implicitly generated lock

Explicitly generate locks

0

None

None

Unlocked mode, which is the case with select operation

one

Null

Null

Table level lock

Oracle automatically adds locks in memory, mainly for notification.

two

Row-S

SS

Table level lock

Before 10g: FOR UPDATE

LOCK TABLE table name IN ROW SHARE MODE

three

Row-SX

SX

Table level lock

Row-level exclusive locks, mainly caused by DML statements

After 10g version: FOR UPDATE, DML statement

LOCK TABLE table name IN ROW EXCLUSIVE MODE

four

Share

S

Table level lock

Shared lock, read-only lock

LOCK TABLE table name IN SHARE MODE

five

S/Row-X

SSX

Table level lock

Shared row exclusive lock

LOCK TABLE table name IN SHARE ROW EXCLUSIVE MODE

six

Exclusive

X

Table level lock / row level lock

Exclusive lock

R:Row S:Share X:Exclusive

5. TM lock compatibility

N

SS

SX

S

SSX

X

N

YES

YES

YES

YES

YES

YES

SS

YES

YES

YES

YES

YES

NO

SX

YES

YES

YES

NO

NO

NO

S

YES

YES

NO

YES

NO

NO

SSX

YES

YES

NO

NO

NO

NO

X

YES

NO

NO

NO

NO

NO

6. Lock commonly used sql

-- Test subtable: score sheet

SELECT * FROM score

-- Test parent table: student information table

SELECT * FROM student

-- query the current session value

SELECT * FROM v$mystat WHEREROWNUM='1'

-- the meaning of the fields in the dynamic performance view v$locked_object

SELECT l.XIDUSN AS rollback segment number

L.XIDSLOT AS rollback slot number

L.XIDSQN AS rollback sequence number

L.OBJECT_ID AS locked object ID

L.SESSION_ID AS holds lock object session ID

The oracle user name of the l.ORACLE_USERNAME AS that holds the lock

L.OS_USER_NAME AS holds the lock operating system user name

L.PROCESS AS operating system process number

L.LOCKED_MODE AS lock mode

FROM v$locked_object l

-- all the objects created by the current user, listing the meanings of some commonly used fields

SELECT uo.object_name AS object name

Uo.object_id AS object ID

Uo.object_typeAS object type

FROM user_objects uo

Through the association of v$locked_object and user_objects tables, you can find out which table is locked and which user is locked.

SELECT uo.object_name AS object name

Uo.object_type AS object type

The oracle user name of the l.ORACLE_USERNAME AS that holds the lock

L.LOCKED_MODE AS lock mode

FROM v$locked_object l,user_objects uo

WHERE l.OBJECT_ID = uo.object_id

/ * AND l.SESSION session IDC session session ID match /

-- query all session ID and lock types that are waiting to acquire a lock

SELECT w.waiting_session AS session ID waiting for lock

W.holding_session AS session ID that holds the lock

W.lock_type AS lock type

W.mode_held AS locking mechanism

FROM dba_waiters w

Dynamic performance view transaction execution list (uncommitted state). BLOCK=1 is the blocker and BLOCK=0 is the blocker.

SELECT * FROM v$transaction_enqueue

-- query blocking relationships

SELECT (SELECT s.USERNAME FROM v$session s WHERE s.SID = a.SID) AS blocker name

A.SID AS Blocker session ID

"blocking" AS behavior

(SELECT s.USERNAME FROM v$session s WHERE s.SID = b.SID) name of the person whose AS is blocked

B.SID AS blocked session ID

FROM v$lock a, v$lock b

WHERE a.ID1 = b.ID1

AND a.ID2 = b.ID2

AND a.BLOCK = 1

AND b.REQUEST > 0

-- query the previous sql operation statement according to the session ID, which is not suitable for the application

SELECT sq.sql_text

FROM v$sql sq

WHERE sq.SQL_ID =

(SELECT s.SQL_ID FROM v$session s WHERE s.SID = & session_id)

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.