Here is a small example showing how this happens. We will use three V$ tables in order to see how this works; V$TRANSACTION, V$SESSION, and V$LOCK. V$TRANSACTION contains an entry for every active transaction. V$LOCK contains an entry for all locks held as well as locks being waited on. V$SESSION shows us the sessions logged in. We'll start by starting a transaction in one session and looking at the state of the system at that point:
tkyte@TKYTE816> update dept set deptno = deptno+10;
4 rows updated.
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 46 160
The interesting things to note here are:
ć 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 Server Reference, you will find that LMODE=6 is an exclusive lock. A value of 0 in the request means we are not making a request ¡V we 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 there would be four rows in V$LOCK since we have four rows locked. What you must remember however is 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 a bit of bit manipulation on them.
Oracle needed to save three 16bit 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 the two numbers that are hiding in that one number back out.
ć The RBS, SLOT, and SEQ values match the V$TRANSACTION information. This is my transaction ID.
Now I'll start another session using the same user name, update some rows in EMP, and then try to update DEPT:
tkyte@TKYTE816> update emp set ename = upper(ename);
14 rows updated.
tkyte@TKYTE816> update dept set deptno = deptno-10;
I am now blocked in this session. If we run the V$ queries again, we see:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0
TKYTE 9 2 46 160 0 6
TKYTE 9 3 82 163 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 82 163
2 46 160
What we see here is that a new transaction has begun ¡V (3,82,163) is the transaction ID.
It has two rows in V$LOCK this time. One row represents the locks that it owns (where LMODE=6). It also has a row in there that shows a REQUEST with a value of 6. 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. We can easily see that the transaction with SID=8 is blocking the transaction with SID=9. Now, if we commit in SID = 8 the above changes:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request, block
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 9 3 82 163 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 82 163
that request row has gone ¡V it 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. Note that the above gives us a very easy way to see blockers and waiters:
tkyte@TKYTE816> select
(select username from v$session where sid=a.sid) blocker,
2 a.sid,
3 ' is blocking ',
4 (select username from v$session where sid=b.sid) blockee,
5 b.sid
6 from v$lock a, v$lock b
7 where a.block = 1
8 and b.request > 0
9 and a.id1 = b.id1
10 and a.id2 = b.id2
11 /
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
-------- ---------- ------------- -------- ----------
TKYTE 8 is blocking TKYTE 9
simply by doing a self-join of V$LOCK with itself (I ran this query before committing the session with SID=8).
2) exclusive lock -- I updated a row. no one else can update it until I commit. I have an X lock on that row and only one person at a time can have an X lock. an X lock provides serialization to a resource.
A shared lock -- when I update a table, I take a shared lock on the DEFINITION of the table. Everyone else can do that as well (more then one session can get a shared lock on the table definition). So, more than one person at a time can update the table. If you wanted to ALTER the table, you would need an X lock on the defintion. You cannot get an X lock when there are shared locks so you wait until there are no shared locks.
Friday, March 28, 2008
Thursday, March 27, 2008
Optimistic Locking Using ORA_ROWSCN
Optimistic Locking Using ORA_ROWSCN
Starting with Oracle 10g Release 1, you have the option to use the built-in ORA_ROWSCN function. It works very much like the version column technique described previously, but it can be performed automatically by Oracle¿you need no extra column in the table and no extra update/maintenance code to update this value.
ORA_ROWSCN is based on the internal Oracle system clock, the SCN. Every time you commit in Oracle, the SCN advances (other things can advance it as well, but it only advances; it never goes back). The concept is identical to the previous methods in that you retrieve ORA_ROWSCN upon data retrieval, and you verify it has not changed when you go to update. The only reason I give it more than passing mention is that unless you created the table to support the maintenance of ORA_ROWSCN at the row level, it is maintained at the block level. That is, by default many rows on a single block will share the same ORA_ROWSCN value. If you update a row on a block with 50 other rows, then they will all have their ORA_ROWSCN advanced as well. This would almost certainly lead to many false positives, whereby you believe a row was modified that in fact was not. Therefore, you need to be aware of this fact and understand how to change the behavior.
To see the behavior and then change it, we¿ll use the small DEPT table again:
ops$tkyte@ORA10G> create table dept 2 (deptno, dname, loc, data, 3 constraint dept_pk primary key(deptno) 4 ) 5 as 6 select deptno, dname, loc, rpad('*',3500,'*') 7 from scott.dept;Table created.
Now we can inspect what block each row is on (it is safe to assume in this case they are in the same file, so a common block number indicates they are on the same block). I was using an 8KB block size with a row width of about 3,550 bytes, so I am expecting there to be two rows per block for this example:
ops$tkyte@ORA10G> select deptno, dname, 2 dbms_rowid.rowid_block_number(rowid) blockno, 3 ora_rowscn 4 from dept; DEPTNO DNAME BLOCKNO ORA_ROWSCN---------- -------------- ---------- ---------- 10 ACCOUNTING 20972 34676029 20 RESEARCH 20972 34676029 30 SALES 20973 34676029 40 OPERATIONS 20973 34676029
And sure enough, that is what we observe in this case. So, let¿s update the row where DEPTNO = 10 on block 20972:
ops$tkyte@ORA10G> update dept 2 set dname = lower(dname) 3 where deptno = 10;1 row updated. ops$tkyte@ORA10G> commit;Commit complete.
What we¿ll observe next shows the consequences of ORA_ROWSCN being tracked at the block level. We modified and committed the changes to a single row, but the ORA_ROWSCN values of both of the rows on block 20972 have been advanced:
ops$tkyte@ORA10G> select deptno, dname, 2 dbms_rowid.rowid_block_number(rowid) blockno, 3 ora_rowscn 4 from dept; DEPTNO DNAME BLOCKNO ORA_ROWSCN---------- -------------- ---------- ---------- 10 accounting 20972 34676046 20 RESEARCH 20972 34676046 30 SALES 20973 34676029 40 OPERATIONS 20973 34676029
It would appear to anyone else that had read the DEPTNO=20 row that it had been modified, even though it was not. The rows on block 20973 are 'safe'¿we didn¿t modify them, so they did not advance. However, if we were to update either of them, both would advance. So the question becomes how to modify this default behavior. Well, unfortunately, we have to re-create the segment with ROWDEPENDENCIES enabled.
Row dependency tracking was added to the database with Oracle9i in support of advanced replication, to allow for better parallel propagation of changes. Prior to Oracle 10g, its only use was in a replication environment, but starting in Oracle 10g we can use it to implement an effective optimistic locking technique with ORA_ROWSCN. It will add 6 bytes of overhead to each row (so it is not a space saver compared to the do-it-yourself version column) and that is, in fact, why it requires a table re-create and not just a simple ALTER TABLE: the physical block structure must be changed to accommodate this feature.
Let¿s rebuild our table to enable ROWDEPENDENCIES. We could use the online rebuild capabilities in DBMS_REDEFINITION (another supplied package) to do this, but for something so small, we¿ll just start over:
ops$tkyte@ORA10G> drop table dept;Table dropped. ops$tkyte@ORA10G> create table dept 2 (deptno, dname, loc, data, 3 constraint dept_pk primary key(deptno) 4 ) 5 ROWDEPENDENCIES 6 as 7 select deptno, dname, loc, rpad('*',3500,'*') 8 from scott.dept;Table created. ops$tkyte@ORA10G> select deptno, dname, 2 dbms_rowid.rowid_block_number(rowid) blockno, 3 ora_rowscn 4 from dept; DEPTNO DNAME BLOCKNO ORA_ROWSCN---------- -------------- ---------- ---------- 10 ACCOUNTING 21020 34676364 20 RESEARCH 21020 34676364 30 SALES 21021 34676364 40 OPERATIONS 21021 34676364
We¿re back where we were before: four rows on two blocks, all having the same initial ORA_ROWSCN value. Now when we update DEPTNO=10
ops$tkyte@ORA10G> update dept 2 set dname = lower(dname) 3 where deptno = 10;1 row updated. ops$tkyte@ORA10G> commit;Commit complete.
we should observe the following upon querying the DEPT table:
ops$tkyte@ORA10G> select deptno, dname, 2 dbms_rowid.rowid_block_number(rowid) blockno, 3 ora_rowscn 4 from dept; DEPTNO DNAME BLOCKNO ORA_ROWSCN---------- -------------- ---------- ---------- 10 accounting 21020 34676381 20 RESEARCH 21020 34676364 30 SALES 21021 34676364 40 OPERATIONS 21021 34676364
The only modified ORA_ROWSCN at this point belongs to DEPTNO = 10, exactly what we wanted. We can now rely on ORA_ROWSCN to detect row-level changes for us.
Welcome to OraDBArchitect Blob!!
Hi,
I am very happy to welcome you all to this BLOB OraDBArchiect....!!
Best Wishes
RK
I am very happy to welcome you all to this BLOB OraDBArchiect....!!
Best Wishes
RK
Subscribe to:
Comments (Atom)
