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

Lock is a mechanism used to prevent harmful interactions (save, fetch) between transactions when accessing the same resources (including user objects, system objects, memory, shared data structures in Oralce data dictionaries, and most commonly database table Table objects).

Different types of locks represent whether the current user allows or prevents other users from accessing the same resources at the same time, so as to ensure that the integrity, consistency and parallelism of the system data are not destroyed.

Locking is a very important technology to realize database concurrency control. When a transaction makes a request to the system and locks it before operating on a data object. 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 lock: SELECT, INSERT, UPDATE, DELETE, MERGE operation

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 added a latch when this execution plan is used

DML lock

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

The TX lock, when the transaction initiates the first modification, gets a TX lock (transaction lock) and holds the transaction until the transaction ends (COMMIT or ROLLBACK). Each line of modification or select for update in a transaction points to the transaction's TX lock.

TM lock, which is used to ensure that the structure of the table is not changed when the contents of the table are modified.

The following examples are illustrated

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 the use of locks

SQL > set linesize 200SQL > select * from v$lock where sid=37 ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -0000000090D8FC88 0000000090D8FCE0 37 AE 1000 0 4 0481 0SQL >

Session 1 performs an update operation

SQL > update emp set ename=initcap (ename); 14 rows updated.

Session 2 View results

SQL > / ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -0000000090D8FC88 0000000090D8FCE0 37 AE 100 0 4 0944 000007FF9EEDA4AB0 00007FF9EEDA4B10 37 TM 87108 0 3 0 3 0000000008F673658 000000008F6736D0 37 TX 131074 908 6 0 3 0

There is an extra transaction lock and a TM lock.

Perform another table update operation in session 1

SQL > update dept set dname=initcap (dname); 4 rows updated.

Session 2 View results

SQL > / ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -0000000090D8FC88 0000000090D8FCE0 37 AE 1000 0 4 0 1174 000007FF9EEDA7B58 00007FF9EEDA7BB8 37 TM 87108 0 3 0233 000007FF9EEDA7B58 00007FF9EEDA7BB8 37 TM 87106 0 3 0 27 0000000008F673658 000000008F6736D0 37 TX 131074 908 6 0 233 0

The transaction lock has not changed, but there is another 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 IN ('DEPT','EMP'); OBJECT_NAME OBJECT_ID---DEPT 87106EMP 87108

For TX locks, id1 is transformed through a transactional id.

Let's take a look at the relevant information about the transaction.

Addr XIDUSN XIDSLOT XIDSQN--000000008F673658 2 2908 for SQL > SELECT addr,xidusn,xidslot,xidsqn FROM transaction

ADDR corresponds to the ADDR of the TX lock, XIDUSN represents the rollback segment number, XIDSLOT represents the number on the transaction table, and XIDSQN represents sequence (number of overrides)

The value of the id1 of the TX lock is equal to XIDUSN*power (2j 16) + XIDSLOT

SQL > select 2*power (2pr 16) + 2 from dual;2*POWER (2pr 16) + 2-131074

The relationship among transaction table, rollback block and transaction slot is as follows:

For TX locks, there is no view that provides which rows have been modified by the transaction. The information about the row lock is stored in the data block.

Next, we dump the data blocks of dept to view the details of the blocks.

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 4 135 4

Only one block is occupied, and the block is dump

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

View the trace file

Block header dump: 0x01000087 Object id on Block? Y seg/obj: 0x15442 csc: 0x00.fab7a itc: 2 flg: e typ: 1-DATA brn: 0bdba: 0x1000080 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0009.002.00000364 0x00c167b0.009a.2e Cmuri-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=4 offs=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:-- Hmurf FLmuri-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:-- Hmurf FLmuri-lb: 0x2 cc: 3col 0: [2] C1 15col 1: [8] 52 65 73 61 63 68col 2: [6] 44 41 4c 4c 41 53tab 0, row 2 0x1f54tl: 20 fb:-- Hmurf FLmuri-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:-- Hmurf FLmuri-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 line-changing data is locked with a flag of 2, which represents the second transaction information of the ITL transaction slot; and the second transaction information

Flag is empty, indicating that there is no commit, so the line is locked (of course, we need to look at the commit flag in the transaction table).

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

There are several kinds of lock mode

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 considers that there is a deadlock, in this case, at the expense of one user, the other user continues to execute, and the transaction of the sacrificed user will be 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), 14 rows updated.

Session 1, and then perform the update to emp

SQL > update emp set ename=lower (ename)

Session 1 is blocked at this point.

Session 2, performing updates to the dept table

SQL > update dept set dname=lower (dname)

Session 2 is also blocked, but session 1 reports a deadlock error

SQL > update emp set ename=lower (ename); update emp set ename=lower (ename) * ERROR at line 1:ORA-00060: deadlock detected while waiting for resource

Session 1 needs to be committed or rolled back for session 2 to execute properly.

Deadlock problems in Oracle are actually rare. If they occur, they are basically caused by incorrect programming. After adjustment, deadlocks are basically avoided.

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.