Oracle Locks Oracle (Oracle lock mode) detailed

There are several modes of locking in Oracle:
0:none
1:null Empty
2:row-s line Sharing (RS): Shared table locks, Sub share
3:row-x Line Exclusive (RX): For row modifications, sub Exclusive
4:share shared Lock (S): Prevents other DML operations, Share
5:s/row-x shared Row Exclusive (SRX): Block other transaction operations, Share/sub exclusive
6:exclusive Exclusive (X): Independent access use, exclusive

The types provided by 1.oracle can be queried according to the type in v$lock_type, and we usually contact the two

The code is as follows	Copy Code
SELECT * from V$lock_type where type in (' TM ', ' TX ')

View the description, you can probably learn the information of the two kinds of locks. TM is used to synchronize Access objects, and TX is related to transactions.

3. There are 2 concepts to be aware of:

(1). Lock data locks, which are row-level locks, only one: Exclusive lock exclusive (ROW)
(2). Lock the lock on the table, that is, lock metadata (table) of the metadata, there are 5 kinds:

2 Rs:row Share
3 Rx:row Exclusive
4 S:share
5 Srx:share Row Exclusive
6 X:exclusive4. Based on the concepts of the Oracle online documentation

We can find at least 2 things from this table. The first is what each database operation corresponds to what kind of lock (refer to the middle of that column), the second is between each lock, if the encounter will be a conflict, the so-called conflict is whether the current database operation Tamping. Among them y*, Indicates that if two operations are locked in the same row, then there will be conflict, after the operation will wait until the previous operation is completed before the completion, otherwise it will be rammed there; if not the same row, then there will be no conflict, and then the operation will not wait. Give an example to illustrate: Suppose now a action is: to Id= 1 of the records are update, the B action is: To delete the record of the id=2, according to the table, the operation on a at the TM level of the lock will be RX,TX level only one is X, on B there will be a TM level of the lock will be RX,TX level only one x, and according to the table description, When the RX encountered Rx, if 2 operations are not the same record, then there will be no conflict, so AB two operations will be in accordance with their respective first plus a TM lock, plus a TX lock, and then smoothly perform their respective operations, will not RAM. If the record ID of the B operation is replaced by 1, then two actions are recorded as the same record. In the TM lock will show a conflict, so the B operation will wait for a operation to complete the submission (that is, a TX lock released after), B and corresponding to generate a TX lock and a TM lock to complete the operation, otherwise it will be rammed, Wait for a to release the TX lock.

5. Commonly used dynamic performance view:

SELECT * from V$lock_type where type in (' TM ', ' TX ');

The code is as follows	Copy Code
SELECT * from V$lock; SELECT * from V$transaction;

Highlight the V$lock view:

Set up a scene: The record of a table in session A is updated, the update is not submitted, in session B, the same record of the change table is deleted

The code is as follows	Copy Code
Session A: Sql> CREATE TABLE TTT as SELECT * from Dba_objects where rownum<=10;

Table has been created.

The code is as follows	Copy Code
sql> update TTT Set object_name= ' TEST ' where object_id=20;

1 rows have been updated.

The code is as follows	Copy Code
Sql> Session B: Sql> Delete from TTT where object_id=20;

At this point, because a is not committed, B will be rammed and then open a session C, query related V$lock view

The code is as follows	Copy Code
Sql> SELECT * from V$lock where type in (' TM ', ' TX '); ADDR kaddr SID TY ID1 ID2 lmode REQUEST CTIME block -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- CA4244F4 CA424508 147 TX 393242 563 0 6 270 0 c8e7f704 c8e7f71c 140 TM 55156 0 3 0 301 0 C8e7f7c8 c8e7f7e0 147 TM 55156 0 3 0 270 0 C8ed3c38 c8ed3d54 140 TX 393242 563 6 0 301 1

You can see clearly that 2 SIDs have a lock. For a session with a SID of 140, a TM and a TX lock are generated, and the TM Lock mode (lmode) = 3, (3 for Rx:row exclusive and Table control, when the action is update, produces an RX lock; for a session with a SID of 147, a TM and a TX lock are also generated, and the TM Lock mode (Lmode) =3 (3 for Rx:row exclusive and table control, which produces an RX lock when the action is delete), and the lock mode of TX ( Lmode = 0, the representative is waiting for a lock. From the definition of v$lock_type, we can also see that the type is a TM lock, ID1 represents object_id, and query dba_objects can easily conclude that the object being locked is TTT this obj. From the last column block (which does not represent blocks, but represents blocking) =1 can also see that sid=140 's session after the generation of TX lock, after the discovery of an operation is also modify the record, so block+1, means blocking other operations at the same time operation of this record.

In addition, the query select * from V$transaction view, you can also get the associated information

From the V$lock_type description of the TX lock, we can know that the TX is related to the transaction. Therefore, to view information about the TX lock prior to V$lock, you can see that the addr value is the same as the v$transaction value. Can even be calculated according to the value of ID1, Which segment is locked: According to the ID1 of TX and take the remaining 2 of the 16 times, get the relevant information:

The code is as follows	Copy Code
sql> Select 393242/65536, mod (393242, 65536) from dual; 393242/65536 MOD (393242,65536) ------------ ----------------- 6.00039673 26

Can be a magical discovery and v$transaction in the Xidusn and xidslot corresponding to the!

6. Finally, add an operation to create the index when the lock operation will be generated:

First insert the TTT table into a lot of data

The code is as follows	Copy Code
sql> INSERT INTO TTT select * from Dba_objects; Sql> commit;

Submit completed.

The code is as follows	Copy Code
Sql> Select COUNT (*) from TTT; COUNT (*) ---------- 1739045

And then create an index on the change table.

The code is as follows	Copy Code
Sql> CREATE index IDX_TTT on TTT (object_id);

Querying v$lock tables while creating an index

It can be found that 2 TM locks are generated at the creation of the index, and the lock classes are 4 and 3 respectively, and we query what these 2 TM locked separately:

According to the results of the query, it is found that the object_id of lmode=4 55160 corresponds to the TTT table, and lmode=4 corresponds to the TM s lock.

Summarize

The larger the number, the higher the lock level, and the more operations it affects.

Level 1 locks are: Select, sometimes appearing in V$locked_object.
Level 2 locks are: Select for Update,lock to Update,lock row share 
Select for Update when a dialog uses a for update substring to open a cursor, all data rows that return a set will be in row-level (row-x) exclusive locks, and other objects can query only those rows of data and cannot perform an update, delete, or select for update operation.
Level 3 locks are: Insert, Update, Delete, lock Row Exclusive
inserting the same record without a commit will not respond, because the next 3 lock will wait for the previous 3 lock, We have to release the last to continue working.
Level 4 locks are: Create Index, lock Share
Locked_mode is 2,3,4 does not affect DML (Insert,delete,update,select) operations, but DDL (Alter,drop, etc.) The operation prompts a ora-00054 error.
00054, 00000, resource busy and acquire with nowait specified '
//*cause:resource interested is busy.
//*action:retry if necessary.
Level 5 locks are: Lock Share Row exclusive 
Specifically, when a primary foreign key constraint update/delete ...; a lock of 4, 5 may be generated.
Level 6 locks are: Alter table, drop table, drop Index, Truncate table, lock Exclusive