Here is a small example showing how this happens, using three V$ tables:
•\ V$TRANSACTION, which contains an entry for every active transaction.
•\ V$SESSION, which shows the sessions logged in.
•\ V$LOCK, which contains an entry for all enqueue locks being held as well as for sessions that are waiting on locks. You will not see a row in this view for each row locked by a session. As stated earlier, that master list of locks at the row level doesn’t exist. If a session has one row in the EMP table locked, there will be one row in this view for that session indicating that fact. If a session has millions of rows in the EMP table locked, there will still be just one row in this view. This view shows what enqueue locks individual sessions have.
First, let’s get a copy of the EMP and DEPT tables. If you already have them in your schema, replace them with the following definitions:
$ sqlplus eoda/foo@PDB1
SQL> create table dept as select * from scott.dept; Table created.
SQL> create table emp as select * from scott.emp; Table created.
SQL> alter table deptadd constraint dept_pk primary key(deptno);Table altered.
SQL> alter table emp add constraint emp_pk primary key(empno);Table altered.
SQL> alter table emp add constraint emp_fk_deptforeign key (deptno)references dept(deptno);Table altered.
SQL> create index emp_deptno_idx on emp(deptno); Index created.
Let’s start a transaction now:
SQL> update dept set dname = initcap(dname); 4 rows updated.
Now, let’s look at the state of the system at this point. This example assumes a single- user system; otherwise, you may see many rows in V$TRANSACTION. Even in a single-user system, do not be surprised to see more than one row in V$TRANSACTION, as many of the background Oracle processes may be performing a transaction as well.
SQL> select username,v$lock.sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot,id2 seq,lmode,request
from v$lock, v$session
The interesting points to note here are as follows:
•\ The LMODE is 6 in the V$LOCK table and the REQUEST is 0. If you refer to the definition of the V$LOCK table in the Oracle Database Reference manual, you will find that LMODE=6 is an exclusive lock. A value of 0 in the request means you are not making a request; you have the lock.
•\ There is only one row in this table. This V$LOCK table is more of a queuing table than a lock table. Many people expect four rows in V$LOCK since we have four rows locked. Remember, however, that Oracle does not store a master list of every row locked anywhere. To find out if a row is locked, we must go to that row.
•\ I took the ID1 and ID2 columns and performed some manipulationon them. Oracle needed to save three 16-bit numbers, but only had two columns in order to do it. So, the first column ID1 holds two of these numbers. By dividing by 2^16 with trunc(id1/power(2,16)) rbs, and by masking out the high bits with bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot, I am able to get back the two numbers that are hiding in that one number.
•\ The RBS, SLOT, and SEQ values match the V$TRANSACTION information.
This is my transaction ID.
Now we’ll start another session using the same username, update some rows in EMP, and then try to update DEPT:
SQL> update emp set ename = upper(ename); 14 rows updated.
SQL> update dept set deptno = deptno-10;
We’re now blocked in this session. If we run the V$ queries again, we see the following:
SQL> select username,v$lock.sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot, 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;
What we see here is that a new transaction has begun, with a transaction ID of (8,17,21403). Our new session, SID=17, has two rows in V$LOCK this time. One row represents the locks that it owns (where LMODE=6). It also has a row that shows a REQUEST with a value of six. This is a request for an exclusive lock. The interesting thing to note here is that the RBS/SLOT/SEQ values of this request row are the transaction ID of the holder of the lock. The transaction with SID=22 is blocking the transaction with SID=17. We can see this more explicitly simply by doing a self-join of V$LOCK:
SQL> select(select username from v$session where sid=a.sid) blocker, a.sid,
Now, if we commit our original transaction, SID=22, and rerun our lock query, we find that the request row is gone:
SQL> select username,v$lock.sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot,id2 seq,lmode,requestfrom v$lock, v$session where v$lock.type = ‘TX and v$lock.sid = v$session.sid and v$session.username = USER;
The request row disappeared the instant the other session gave up its lock. That request row was the queuing mechanism. The database is able to wake up the blocked sessions the instant the transaction is completed. There are prettier displays with various GUI tools, but in a pinch, having knowledge of the tables you need to look at is very useful.
Leave a Reply