Oracle Learning Performance Optimization (10) lock

Lock is a mechanism for preventing harmful interactions (deposits, fetching) between transactions when accessing the same resources (including user objects, system objects, memory, shared data structures in Oralce data dictionaries, most commonly, database tables table objects).

Different types of locks represent whether the current user allows or prevents other users from accessing the same resource simultaneously, ensuring that the integrity, consistency, and parallelism of the system data are not compromised.

Locking is a very important technology to realize concurrency control of database. When a transaction makes a request to the system before it operates on a data object, it locks it. After locking, the transaction has some control over the data object, and other transactions cannot update the data object until the transaction releases the lock.

Classification of Locks

• DML locks: SELECT, INSERT, UPDATE, DELETE, merge operations

• DDL locks: Create and ALTER statement operations

• Internal locks and Latches: Oracle uses these locks to contain internal data structures, such as the execution plan generated by the Oracle query, which is saved in the library cache, and a latch is added to the execution plan when it is used (latch)
  

DML lock

Used to ensure that only one person can modify a row of data at a time. And when you work with the table normally, others cannot delete the table.

1. TX LOCK, when the transaction initiates the first modification, it gets a TX lock (transaction Lock) and holds the transaction until the end of the transaction (commit or rollback). Each row of a transaction that is modified or a select for update points to the TX lock for that transaction.

2. TM Lock, which ensures that the structure of the table is not changed when the contents of the table are modified.

The following examples illustrate

Log in to the Scott user and determine the session ID

Sql> Grant Select any dictionary to Scott; Grant succeeded.       Sql> Conn scott/tigersql> select Sid from V$mystat where Rownum=1; SID----------37sql>

Open another session to monitor lock usage

sql> Set linesize 200sql> select * from V$lock where sid=37; ADDR kaddr SID TY ID1 ID2 lmode REQUEST CTIME BLOCK---------------------------------------------------   -----------------------------------------------------0000000090d8fc88 0000000090d8fce0 notoginseng AE 100 0 4 0 4810sql>

Session 1 performing an update operation

sql> Update emp Set ENAME=INITCAP (ename), and rows updated.

Session 2 Viewing results

sql> /addr kaddr sid ty        id1   id2   lmode    request ctime     BLOCK---------------- ---------------- ---------- -- ---------- ---------- ----- ----- ---------- ---------- ----------0000000090d8fc88 0000000090d8fce0  37  AE        100    0        4  0   944000007ff9eeda4ab0 00007ff9eeda4b10  37  TM      87108    0        3  0     30000000008F673658 000000008F6736D0  37  Tx     131074  908       6  0      30

One more transaction lock, one TM lock.

Another table update operation is performed in session 1

Sql> Update Dept Set dname=initcap (dname); 4 rows updated.

Session 2 Viewing results

sql> /addr kaddr sid ty        id1   id2   lmode    request ctime     BLOCK---------------- ---------------- ---------- -- ---------- ---------- ----- ----- ---------- ---------- ----------0000000090d8fc88 0000000090d8fce0  37  AE        100    0        4  0  1174000007ff9eeda7b58 00007ff9eeda7bb8  37 tm       87108    0       3   0   233000007FF9EEDA7B58 00007FF9EEDA7BB8  37 TM       87106    0       3  0     270000000008f673658 000000008f6736d0  37 tx     131074  908        6  0   2330

The transaction lock is not changed, but there is one more TM lock.

For TM locks, the value of ID1 is the ID of the object

Sql> COL object_name for a30sql> select object_name,object_id from dba_objects where owner= ' SCOTT ' and object_name I N (' DEPT ', ' EMP '); object_nameobject_id----------------------------------------DEPT 87106EMP 87108

For a TX lock, the ID1 is converted by transaction ID.

Let's start by looking at the information about the transaction.

Sql> SELECT addr,xidusn,xidslot,xidsqn from V$transaction; ADDR xidusn xidslot xidsqn----------------------------------------------000000008f673658 2 2 908

Addr corresponds to the addr of the TX lock, XIDUSN represents the rollback segment number, Xidslot represents the number on the transaction table , xidsqn represents sequence (number of overrides)

The value of the ID1 of the TX lock equals Xidusn*power (2,16) +xidslot

Sql> Select 2*power (2,16) +2 from Dual;2*power (2,16) +2---------------131074

the relationships between transaction tables, rollback blocks, and transaction slots are as follows :

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/72/D8/wKiom1XuaU2wbsNRAAF48Zkh6Os546.jpg "title=" Transaction with rollback segment. jpg "alt=" wkiom1xuau2wbsnraaf48zkh6os546.jpg "/>

For a TX lock, there is no view that provides which rows the transaction modifies. The information for the row lock is stored in the data block.

Below we will dump the Dept data block to see the details of the block

Sql> Select Dbms_rowid.rowid_relative_fno (ROWID) fno,dbms_rowid.rowid_block_number (ROWID) bno from dept; FNO BNO--------------------4 135 4 135 4 135 4 135

Consumes only one block of data and dumps the block

sql> alter system dump datafile 4 block 135; System altered. sql> select    d.value       | |   '/'        | |  LOWER  (rtrim  (i.instance, chr  (0))        | |   ' _ora_ '        | |  p.spid       | |   '. TRC '           trace_file_name  FROM  ( select p.spid          from v$mystat m,  v$session s, v$process p         where  m.statistic  2    3    4    5     6    7    8    9   10  # = 1 and s.sid = m.sid and p.addr =  S.PADDR)  p,        (select t.instance           FROM v$thread t, v$parameter v          WHERE     v.name =  ' thread '                 AND  (v.value = 0  or t.thread# = to_number  (V.value))  i,        ( Select value           11   12    13   14   15   16  from v$ parameter         where name =  ' User_dump_dest ')  d;    17  trace_file_name--------------------------------------------------------------------------------/ U01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5593.trc

Viewing trace files

Block header dump:  0x01000087 object id on block? y seg/obj :  0x15442  csc: 0x00.fab7a  itc: 2  flg: e  typ:  1 - data     brn: 0  bdba: 0x1000080 ver:  0x01 opc: 0     inc: 0  exflg: 0  itl            Xid                   Uba          Flag  Lck        Scn/Fsc0x01    0x0009.002.00000364  0x00c167b0.009a.2e  c---    0   scn 0x0000.000e0ef00x02   0x0002.002.0000038c  0x00c00591.0088.26   ----    4  fsc 0x0000.00000000bdba: 0x01000087data_block_dump,data header at  0x7f23e518ea64===============tsiz: 0x1f98hsiz: 0x1apbl: 0x7f23e518ea64      76543210flag=--------ntab=1nrow=4frre=-1fsbo=0x1afseo=0x1f3cavsp=0x1f22tosp=0x1f220xe:pti[0]nrow=4offs= 00x12:pri[0]offs=0x1f7e0x14:pri[1]offs=0x1f680x16:pri[2]offs=0x1f540x18:pri[3]offs=0x1f3cblock_row_dump:tab  0, ROW 0, @0X1F7ETL: 26 FB: --H-FL-- LB: 0X2  CC:  3col  0: [ 2]  c1 0bcol  1: [10]  41 63  63 6f 75 6e 74 69 6e 67col  2: [ 8]  4e  45 57 20 59 4f 52 4btab 0, row 1, @0x1f68tl: 22  fb: --h-fl-- lb: 0x2  cc: 3col  0: [ 2]  c1  15col  1:&nbsP [ 8]  52 65 73 65 61 72 63 68col  2: [ 6 ]  44 41 4c 4c 41 53tab 0, row 2, @0x1f54tl: 20  fb: --h-fl-- lb: 0x2  cc: 3col  0: [ 2]  c1  1fcol  1: [ 5]  53 61 6c 65 73col  2: [  7]  43 48 49 43 41 47 4ftab 0, row 3, @ 0x1f3ctl: 24 fb: --h-fl-- lb: 0x2  cc: 3col  0: [  2]  c1 29col  1: [10]  4f 70 65 72 61 74  69 6f 6e 73col  2: [ 6]  42 4f 53 54 4f  4eend_of_block_dumpend dump data blocks tsn: 4 file#: 4 minblk  135 maxblk 135 

1) lb:0x2 indicates that the row data is locked and the flag is 2, which represents the second transaction information of the ITL transaction slot; and the second transaction information

Flag is empty, indicating no commit, so the row is locked (of course we need to see the commit flag in the transaction table).

2)lck=4 indicates that 4 rows of data are locked.

The lock mode has the following types

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/72/D8/wKiom1XubwWhtulEAALZ7CqgjkY557.jpg "title=" lock Mode. PNG "alt=" Wkiom1xubwwhtuleaalz7cqgjky557.jpg "/>

Deadlock-deadlock 
definition: A deadlock occurs when two users want to hold each other's resources.
that is, when two users wait for each other to release resources, Oracle determines that a deadlock is generated, in which case another user continues to execute at the expense of one user, and the transaction of the sacrificed user is rolled back.
Example:
Session 1, do the following:

Sql> Conn scott/tigerconnected.sql> Update dept set dname=lower (dname); 4 rows updated.

Session 2, do the following:

sql> Conn scott/tigerconnected.sql> update emp set Ename=lower (ename), rows updated.

Session 1, and then perform an update to the EMP

sql> Update emp Set ename=lower (ename);

Session 1 is blocked at this time.

Session 2, performing an update to the Dept table

Sql> Update Dept Set dname=lower (dname);

Session 2 is also blocked at this time, but session 1 will report a deadlock error

sql> Update emp Set ename=lower (ename); update emp set Ename=lower (ename) *error at line 1:ora-00060:deadlock det ected while waiting for resource

Session 1 needs to be committed or rolled back, Session 2 can be performed normally.

Oracle's deadlock problem is actually very rare, and if it happens, it's basically an incorrect design, and after the adjustment, it basically avoids the deadlock.

This article is from the "Ding Dong" blog, please be sure to keep this source http://lqding.blog.51cto.com/9123978/1692746

Oracle Learning Performance Optimization (10) lock