Understanding of Oracle Learning----Row-level locks

To understand the occurrence of row-level locks through experiments

1. Create the required tables

Sql> Conn/as SYSDBA
is connected.
Sql> CREATE TABLE Dept as select * from Scott.dept;

The table is created.

Sql> CREATE TABLE EMP as SELECT * from Scott.emp;

The table is created.

Sql> ALTER TABLE DEPT add constraint DEPT_PK primary key (DEPTNO);

The table has changed.

Sql> ALTER TABLE EMP add constraint EMP_PK primary key (EMPNO);

The table has changed.

Sql> ALTER TABLE EMP add constraint emp_fk_dept foreign key (DEPTNO) References dept (DEPTNO);

The table has changed.

Sql> CREATE INDEX EMP_DEPTNO_IDX on EMP (DEPTNO);

The index has been created.

2. Perform the update operation

Sql> Update Dept Set dname=initcap (dname);

4 rows have been updated.

3. View the system status at this time

Sql> Set Linesize 1000 pagesize
Sql> Select Username,
2 V$lock.sid,
3 Trunc (Id1/power (2,16)) RBS,
4 Bitand (Id1,to_number (' ffff ', ' xxxx ')) +0 slots,
5 Id2 seq,
6 Lmode,
7 Request
8 from V$lock,v$session
9 where v$lock.type= ' TX '
Ten and V$lock.sid=v$session.sid
One and V$session.username=user;

USERNAME SID RBS SLOT SEQ lmode REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYS 68 3 23 898 6 0

The list shows that Lmode=6 is an exclusive lock, request=0 means no lock request, that is, the data of the table is updated without another session updating the same piece of data, there is no lock request

Sql> select Xidusn,xidslot,xidsqn from v$transaction; The information that is detected is that the transaction ID can correspond to the lock information that is queried.

Xidusn Xidslot xidsqn
---------- ---------- ----------
3 23 898

4. Log in to the same user in a new session, perform the update operation

sql> Update emp Set Ename=upper (ename);

14 rows have been updated.

Sql> Update dept Set deptno=deptno-10;

5. View System Information

Sql> Select Username,
2 V$lock.sid,
3 Trunc (Id1/power (2,16)) RBS,
4 Bitand (Id1,to_number (' ffff ', ' xxxx ')) +0 slots,
5 Id2 seq,
6 Lmode,
7 Request
8 from V$lock,v$session
9 where v$lock.type= ' TX '
Ten and V$lock.sid=v$session.sid
One and V$session.username=user;

USERNAME SID RBS SLOT SEQ lmode REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYS 130 3 23 898 0 6
SYS 68 3 23 898 6 0
SYS 130 5 33 872 6 0

Sql> select xidusn,xidslot,xidsqn from V$transaction;

Xidusn Xidslot xidsqn
---------- ---------- ----------
3 898---first time
5 872---second time

One more in the transaction information, in the information of the lock more than two, one of the request is an exclusive lock 6, his rbs/slot/seq and the same as the first, indicating that he was locked in the first update operation, he is requesting this lock, to obtain resources, In the second session, the first statement was also not committed, so he also appeared in an exclusive lock.

Through the self-coupling of the lock, we can clearly see who is locked between them.
Sql> Select
2 (select username from v$session where sid=a.sid) blocker,
3 A.sid,
4 ' is blocking ',
5 (select username from v$session where sid=b.sid) Blockee,
6 B.sid
7 from V$lock A,v$lock b
8 where a.block=1
9 and B.request >0
Ten and A.id1=b.id1
One and A.id2=b.id2;

Blocker Sid ' Isblocking blockee SID
------------------------------ ---------- ----------- ------------------------------ ----------
SYS is blocking SYS 130

6. Commit the transaction, query the lock information

Sql> commit;

Submit complete.

Sql> Select Username,
2 V$lock.sid,
3 Trunc (Id1/power (2,16)) RBS,
4 Bitand (Id1,to_number (' ffff ', ' xxxx ')) +0 slots,
5 Id2 seq,
6 Lmode,
7 Request
8 from V$lock,v$session
9 where v$lock.type= ' TX '
Ten and V$lock.sid=v$session.sid
One and V$session.username=user;

USERNAME SID RBS SLOT SEQ lmode REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
SYS 130 5 33 872 6 0

Sql> select xidusn,xidslot,xidsqn from V$transaction;

Xidusn Xidslot xidsqn
---------- ---------- ----------
5 872

The lock for SID 68 is gone, 130 of the locks are left, and the information for the lock request disappears, which explains the lock mechanism of Oracle, first the lock queue management is not used for Oracle's Row lock, two update operations in the second session are not committed, and if the queue management should have two lock information, Now there's only one, that when the first session is not committed to appear two lock information, is because the first session update when the row is added to the lock, the second session to update the same row, the session will be issued a lock request, so in the lock information has its appearance, but it appears, his mode is 0 so that can be understood.

Related Script Summary

Query Lock Related Information

Query the lock state of the operation of the current session

Select Username,
V$lock.sid,
Trunc (Id1/power (2,16)) RBS,
Bitand (Id1,to_number (' ffff ', ' xxxx ')) +0 slots,
Id2 seq,
Lmode,
Request
From V$lock,v$session
where v$lock.type= ' TX '
and V$lock.sid=v$session.sid
and V$session.username=user;

Find the appropriate control information in the transaction view

Select Xidusn,xidslot,xidsqn from V$transaction;

View the self-junction information for a lock to see who locked it

Select
(select username from v$session where sid=a.sid) blocker,
A.sid,
' Is blocking ',
(select username from v$session where sid=b.sid) Blockee,
B.sid
From V$lock A,v$lock b
where a.block=1
and B.request >0
and A.ID1=B.ID1
and A.id2=b.id2;

Understanding of Oracle Learning----Row-level locks