Oracle LOCK internal mechanism and Best Practices Series

Introduction: Oracle locks will have an uncertain feeling when I first came into contact, just as distant aliens could not see but feel it, I encountered a ORA-00054 in my actual work: resource busy acquire with nowait specified error cannot be inserted into the table, I knew it was locked, according to V $ LOCK, the blocked session is also located, but I don't know how to solve it for a long time. The reason is that I don't know the internal mechanism and release principle, the following is an example of unlocking the Oracle lock.
 
Simulate insert, update, and delete blocking examples, describe the corresponding information in v $ lock, and give an SQL demonstration.
 
LEO1 @ LEO1> create user leo2 identified by leo2 default tablespace leo1; create a LEO2 user
User created.
LEO1 @ LEO1> grant connect, resource to leo2; grant basic Permissions
Grant succeeded.
LEO1 @ LEO1> select owner, table_name, tablespace_name from dba_tables where owner = 'leo1 ';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------------------------------------------------------------------
LEO1 LEO_WAGE LEO1
 
LEO1 @ LEO1> create table t1 (id int primary key); create table t1 and set the id column as the primary key
Table created.
LEO1 @ LEO1> insert into t1 values (1 );
1 row created.
LEO1 @ LEO1> select * from t1;
ID
----------
1
LEO2 @ LEO1> insert into leo1.t1 values (1); when no submission is made, session blocking occurs when the same values is inserted. hang cannot proceed here
LEO1 @ LEO1> commit; blocking is terminated only after submission. That is to say, commit can release blocking.
Commit complete.
LEO2 @ LEO1> insert into leo1.t1 values (1); because 1 already exists, it violates the primary key constraint.
Insert into leo1.t1 values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (LEO1.SYS _ c0020.17) violated
 
Update lock Blocking
 
LEO1 @ LEO1> select * from t1;
ID
----------
1
2
100
LEO1 @ LEO1> update t1 set id = 200 where id = 100; update a row that is not submitted is invisible to others, but the physical block is actually modified. Others can only access images in the undo rollback segment.
1 row updated.
LEO2 @ LEO1> update leo1.t1 set id = 300 where id = 100; the same row in the same table is also updated on session leo2. At this time, the hang remains unchanged, because two sessions are competing for the modification permission of the same record
LEO1 @ LEO1> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2; generally, the TM and TX locks affect business performance.
Sid ty ID1 ID2 LMODE REQUEST BLOCK
--------------------------------------------------------------
138 TM 73449 0 3 0 0
138 TX 196615 912 6 0 1
156 TM 73449 0 3 0 0
156 TX 196615 912 0 6 0
 
SID: Session ID
TYPE: lock TYPE
ID1: ID of the session operation object
ID2: ID1 + ID2 locates an address in the rollback segment (that is, the address of the Data image before modification). Because 138 and 156 sessions are the same, they point to the same address, in other words, operations are performed on the same row of data.
LMODE: Lock mode. Different numbers indicate different lock modes. For example, 0 is not applied to lock 3. Shared lock mode (segment-level shared lock). 6. exclusive lock mode. The higher the lock level, the more limited.
REQUEST
BLOCK: several sessions are being blocked. For example, 1 is currently blocking one session. 2 is currently blocking two sessions.
The essence of a lock is to maintain the integrity of a transaction. The lock information is a data block attribute and physical, not logically a table or several rows.
 
LEO1 @ LEO1> select distinct sid from v $ mystat; the current session id is 138. How can we differentiate it? => LEO1 user = 138 LEO2 user = 156
SID
----------
138
LEO1 @ LEO1> select object_name from dba_objects where object_id = 73449; 138 the object of the session operation is table T1
OBJECT_NAME
--------------------------------------------------------------------------------
T1
Note: The 138 session adds the TM and TX locks to the T table. The tmlock mode is 3 (shared lock). The TX lock mode is 6 (exclusive lock ), currently, the TX lock is blocking a session (that is, 156 session ).
156 the session is the currently blocked session. 156 The session operation object is also in Table T1 (ID1 is the same), and The tmlock mode is also 3 (the shared lock means that several shared locks can be created after several sessions, at the same time), TX has not applied for the lock, and is applying for the lock 6, and the lock 6 is held by the 138 SESSION (because the two sessions operate on the same row of data)
LEO1 @ LEO1> select sid, event from v $ session_wait where sid in (138,156); from the session wait view, we can see which sessions cannot wait for the event due to any reason
SID EVENT
--------------------------------------------------------------------------
138 SQL * Net message to client
156 enq: TX-row lock contention
156 session hang cannot move forward due to TX lock contention, enq = enqueues queue lock (usually related to the business, in order to protect the business lock)
Summary: now we should clearly see that 138 sessions are blocked 156 sessions, as well as the reasons for blocking and the number of sessions and lock types.
 
Insert lock Blocking
 
LEO1 @ LEO1> select * from leo1.t1;
ID
----------
1
2
200
LEO1 @ LEO1> insert into leo1.t1 values (3); insert a row but not commit. This is a pending status and it is unclear whether the row is actually inserted.
1 row created.
LEO2 @ LEO1> insert into leo1.t1 values (3); we inserted the same data on session leo2. At this time, hang does not move. In fact, two independent records are inserted here, it cannot be considered the same record, but the value is the same
LEO1 @ LEO1> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;
Sid ty ID1 ID2 LMODE REQUEST BLOCK
--------------------------------------------------------------
138 TM 73449 0 3 0 0
138 TX 65555 681 6 0 1 138 is blocking another session
156 TM 73449 0 3 0 0
156 TX 458766 669 6 0 0 this also shows that the second record is not blocked when it is inserted
156 TX 65555 681 0 4 0 and blocked after the inserted modified value is the same, the lock level is 4
During insert, the v $ lock view has a TX lock (that is, the last row). The differences between the insert and update delete operations are described first, the latter two are both blocked for the modification Right Contention of the same record (the modification value is not involved here), while the insert operation actually inserts two different records, because the modified values of the two different records violate the primary key constraints and thus cause blocking, the change actually causes blocking for the same modified values. The lock level is 4, which is lower than the update lock level. The lower the lock level, the less the limit.
 
Delete lock Blocking
 
LEO1 @ LEO1> select * from leo1.t1; Table t1 has five records. We plan to delete the last record.
ID
----------
1
2
4
5
200
LEO1 @ LEO1> delete from leo1.t1 where id = 200; 138 the session is deleting the record with id = 200, but it is not submitted. In this case, a tm tx lock is added.
1 row deleted.
LEO2 @ LEO1> delete from leo1.t1 where id = 200; at this time, the 158 session also performs the same action, so it cannot be moved by hang. Let's take a look at the locking situation.
LEO1 @ LEO1> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;
Sid ty ID1 ID2 LMODE REQUEST BLOCK
--------------------------------------------------------------
138 TM 73449 0 3 0 0
138 TX 524316 935 6 0 1 138 session holds a Level 6 exclusive lock and is blocking a session
156 TM 73449 0 3 0 0
156 TX 524316 935 0 6 0 156 the session is blocked and no lock is obtained. Applying for a Level 6 lock
LEO1 @ LEO1> select object_name from dba_objects where object_id = 73449; now we know that table t1 is locked.
OBJECT_NAME
--------------------------------------------------------------------------------
T1
LEO1 @ LEO1> select sid, event from v $ session_wait where sid in (138,156); from the session wait view, we can see that 138 blocks 156 sessions.
SID EVENT
--------------------------------------------------------------------------
138 SQL * Net message from client
156 enq: TX-row lock contention
156 session hang cannot move forward due to TX lock contention, enq = enqueues queue lock
Summary: While unlocking, we should also try to avoid them in the business design process. For example, the work of two people is not properly coordinated, if you do the same thing at the same time, the lock may be generated.
 
Select... for update lock blocking this is a protection mechanism for modifying the result set.
 
Scenario: this command is used to lock the result set when multiple records are modified at a time, which is also a blocking caused by modification of the result set.
LEO1 @ LEO1> select * from leo1.t1;
ID
----------
1
2
3
200
LEO1 @ LEO1> select * from leo1.t1 where id <= 3 for update; if we want to exclusive the query result set and do not allow other sessions to be modified at this time, you can write it like this.
ID
----------
1
2
3
LEO1 @ LEO1> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;
Sid type ID1 ID2 LMODE REQUEST BLOCK
----------------------------------------------------------------------
138 TM 73449 0 3 0 0
138 TX 589839 915 6 0 0 all three rows of records have been locked by the TX lock and cannot be modified before submission.
LEO2 @ LEO1> update leo1.t1 set id = 4 where id = 1;
LEO2 @ LEO1> update leo1.t1 set id = 4 where id = 2;
LEO2 @ LEO1> update leo1.t1 set id = 4 where id = 3; each record in the update result set will be hang and cannot be moved forward.
The result set has been locked.
LEO1 @ LEO1> select sid, type, id1, id2, lmode, request, block from v $ lock where type in ('TT', 'tx ') order by 1, 2;
Sid ty ID1 ID2 LMODE REQUEST BLOCK
--------------------------------------------------------------
138 TM 73449 0 3 0 0
138 TX 589839 915 6 0 1 138 is blocking another session
156 TM 73449 0 3 0 0
156 TX 589839 915 0 6 0
 
LEO1 = 138 session LEO2 = 156 session, we can see that 138 session is blocked 156 session, 156 session TX is requesting an exclusive lock on the 6th, because both sessions are modifying the same result set. This method can lock n rows of records at a time.
Note: A table can have only one lock number 6

1 2 3 4 5 Next Page

[Content navigation]
Page 1: Simulate insert | update | delete to cause blocking and description	Page 1: Simulate scenarios where RI locking causes blocking and analyze v $ lock
Page 1: a scenario where manual locking is used to address business needs	Page 1: analyze the mutex between tmlocks in mode 2-6
Page 1: An SQL example that causes a deadlock