A simple demonstration of Row-level locks caused by concurrent primary key columns in the Oracle database

Content
• Software environment
• A simple demonstration of Row-level locks caused by concurrent Oracle databases
This article briefly demonstrates the row-level locks caused by concurrent Table Primary keys. More than two concurrent users modify the same data (including insert, delete, and modify ). The lock is generated because of concurrency. There is no lock if there is no concurrency. Concurrency is generated because the system needs it and the system needs it because the user needs it.

Software Environment
--------------------------------------------------------------------------------
• Windows 2003 Server
• Oracle 11g Release 1 (11.1)
A simple demonstration of Row-level locks caused by concurrent Oracle databases
First, open session 1 and perform the following operations:

Copy codeThe Code is as follows: SQL> select distinct sid from V $ mystat;

SID
----------
118
SQL> create table t (x int primary key );
The table has been created.
SQL> insert into t values (1 );
One row has been created.
SQL> update t set x = 10 where x = 1;
1 row updated.
SQL>

First, view the SID of this session, and then create a table t with only one field named x, which is the primary key, insert a piece of data, and update the data.

Next, open another session 2:Copy codeThe Code is as follows: SQL> select distinct sid from V $ mystat;

SID
----------
137
SQL> update t set x = 10 where x = 1;

At this time, the session is "stuck" here. But the cursor keeps flashing ...... Because session 2 is blocked by session 1.
Now, view the locked view V $ LOCK.Copy codeThe Code is as follows: SQL> select sid, type, id1, id2, lmode, request, block
2 from v $ lock where sid in (118,137)
3 order by sid;

You have selected 6 rows.
SQL>
Description: The "TYPE" column indicates the lock TYPE. The "LMODE" column indicates the lock mode. The "ID1" and "ID2" columns indicate the lock-related information. What is the lock in the "REQUEST" column.

SID = 118 is the first session, and SID = 137 is the second session. In the third row, the first session BLOCK = 1 indicates that the session is blocking other sessions, and LMODE = 6 indicates the lock mode, that is, row-level exclusive lock. Row 6: The second session REQUEST = 6 indicates that the current session is waiting for a lock with LMODE = 6. Note that the ID1 and ID2 columns in the third and sixth rows are identical. Because they point to a unified resource, only one is the resource owner (SID = 118), and the other is the waiting person (SID = 137) of the resource ).

Through this view, it is easy to find the location-trouble shooting ). The reason why Session 2 is "stuck" is that Session 1 has not been submitted, and on this table, it is exactly the constraint that requires column value uniqueness.
View the view V $ SESSION by using the SID number to determine the user information.Copy codeThe Code is as follows: SQL> select machine from v $ session where sid in (118,137 );

MACHINE
----------------------------------------------------------------
NUODE \ LN
NUODE \ LN
SQL>

Because two sessions are on the same machine, the name is the same.