13. Oracle Lock

13, management data concurrency--lock

1. Lock mechanism

Advanced Data concurrency Processing

Use row-level locking when inserting, updating, and deleting

Query does not require any locks

Automatic queue Management

Hold locks until transaction processing (commit or rollback) ends

Undo Fast record fast mirroring block before immage.

DML Lock: Exclusive (Exclusive)

Table Lock table locks row exclusive row-level lock

2. Lock conflict

Pessimistic lock, optimistic lock

Pessimistic Lock: SELECT * from a for update; Auto Locking (for update)

Optimistic Lock: Database server locks on condition

Lock conflict Reason: No changes committed, long running transactions. Unnecessary high-level lock (Pessimistic lock)

V$lock

V$locked_object

Dba_waiters

Dba_blockers;

V$transaction;

3. Resolve Lock conflicts

Update scott.emp set sal=sal+100 where empno= ' 7369 ';

Commit a transaction or kill session

(1) Query to deadlock session

Select Sid,serial#,username from V$session where SID in (select Blocking_session from v$session);

Sql> Select Sid,serial#,username from v$session where SID in (select Blocking_session from

V$session);

SID serial# USERNAME

---------- ---------- ------------------------------

7 SYS

(2) Kill session;

Alter system kill session ' 21,7 ' immediate;

Production environment:

Sql>spool Kill_session.sql

Select ' Alter system kill session ' | | sid| | ', ' | | serial#| | '; ' from V$session where SID in (select Blocking_session from v$session);

Sql>spool off

4. Deadlock

Select Sid,type,id1,id2,lmode,request,block from V$lock where type in (' TM ', ' TX ') Order by 1, 2;

Sql> Select Sid,type,id1,id2,lmode,request,block from V$lock where type in (' TM ', ' TX ') Orde

R by 1, 2;

SID TY ID1 ID2 lmode REQUEST BLOCK

---------- -- ---------- ---------- ---------- ---------- ----------

TM 73201 0 30 0

TX 393218 1952 06 0

148 TM 73201 0 30 0

148 TX 393218 1952 60 1

Sql> Select Xidusn,xidslot,xidsqn from v$transaction where xidsqn=1952;

Xidusn xidslot xidsqn

---------- ---------- ----------

6 2 1952

The SID indicates that the session ID,TM represents a table-level lock, TX represents a row-level lock, the Xidslot table represents a transaction slot, ID2 (1952) represents a transaction, and Xidusn represents a rollback segment.

Select Trunc (393218/65535), mod (393218,65535) from dual;

Sql> Select Trunc (393218/65535), mod (393218,65535) from dual;

TRUNC (393218/65535) MOD (393218,65535)

------------------- -----------------

6 8

This article is from the "Liang blog" blog, make sure to keep this source http://7038006.blog.51cto.com/7028006/1939484

13. Oracle Lock