The content of this article
• Software Environment
• Simple demonstration of Oracle database concurrency causing row-level locks
This article provides a simple demonstration of row-level locks that are caused by concurrency for table primary keys. Concurrency is a modification of the same data by more than two users (including inserts, deletions, and modifications). The lock is generated because of concurrency. Without concurrency, there is no lock. Concurrency arises because the system needs it because the user needs it.
Software Environment
--------------------------------------------------------------------------------
Windows 2003 Server
Oracle 11g Release 1 (11.1)
Simple demonstration of Oracle database concurrency causes row-level locks
First, open a session 1 and do the following:
Copy Code code as follows:
Sql> select distinct SID from V$mystat;
Sid
----------
118
Sql> CREATE table t (x int primary key);
Table has been created.
Sql> INSERT INTO T values (1);
1 lines have been created.
Sql> Update T set x=10 where x=1;
1 rows have been updated.
Sql>
Review the SID for this session, and then create a table T with only one field named X, and the field as the primary key, insert a piece of data, and update the data.
Next, turn on another session 2:
Copy Code code as follows:
Sql> select distinct SID from V$mystat;
Sid
----------
137
Sql> Update T set x=10 where x=1;
At this point, the session is "stuck" here. Just the cursor has been flashing ... Because session 2 is blocked by session 1.
Now, view the locked view V$lock.
Copy Code code as follows:
Sql> Select Sid,type,id1,id2,lmode,request,block
2 from V$lock where SID in (118,137)
3 order by Sid;
6 rows have been selected.
Sql>
Description: The "Type" column represents the type of lock, the "Lmode" column represents the mode of the lock, and the "ID1" and "ID2" columns are information about the lock. What the "REQUEST" column is locking.
SID=118 is the first session, SID=137 is the second session. The third line, the first session block=1, indicates that the session is blocking other sessions, lmode=6 represents the mode of the lock, the row-level exclusive lock. Line six, the second session request=6 indicates that the current session is waiting for a lmode=6 lock. Note that the ID1 and ID2 columns in the third and sixth rows are exactly the same. Because they point to the unified resource, only one is the resource owner (sid=118), one is the resource's waiting person (sid=137).
Through this view, it is easy to find the location-fault location (trouble shooting). Session 2 is "stuck" because session 1 has not yet been committed, and on this table there is exactly the required column value uniqueness constraint.
By using the SID number, view the view v$session to determine the user's information.
Copy Code code as follows:
Sql> Select machine from v$session where SID in (118,137);
MACHINE
----------------------------------------------------------------
Nuode\ln
Nuode\ln
Sql>
Because, the two sessions are the same machine, so the name is the same.
