Oracle data buffers and 10g automatic memory mgt, - renamed from "oracle lock issue" - Options

[gautamvv]

i have developed a vb oracle application

let us assume 100 users are using my application.

what if more than one user tries to update a particular row

at the same time? (more than one user updating the same row at the

same time)

do i need to handle such situation in oracle?

or oracle takes care of such situation automatically?

[dave]

everyone will wait until the first person who tried the update commits or rollsback

[burleson]

Hi,

Here are some details on concurrent update locking:

http://www.dba-oracle.com/t_v$bh_stat...ffer_blocks.htm

[mbobak]

Hmm.....I see a few problems with that article.
Row level locks don't have much to do with the mode of the buffer header in v$bh.
Sure, a block that undergoes changes will be in XCUR mode, but that does NOT imply
that a block in XCUR mode is being changed or has any kind of lock imposed. I'd
argue that the definition "XCUR -- This is a RAM block that has an exclusive lock" is
wrong, and more precisely, misses the point. Blocks in the buffer cache will be
either current mode (XCUR) or consistent read mode (CR). RAC adds SCUR, shared
current mode. None of those modes imply that the buffer is "locked" into the
buffer cache or that there is any current lock in effect on any row(s) in that block.

Row level locking goes more something like this:
Transaction begins: Either explicitly via 'set transaction ...' or implicitly, due to any
DML (select for update, update, insert, or delete)

Slot is allocated in rollback segment header: This is either chosen round-robin,
or, if specified, taken from 'set transaction use rollback segment ...' statement.

Statement is executed, blocks to be changed are identified: This is done via the
execution plan, utilizing whatever access paths are specified there. As each block
is identified rows are taken as follows:

Now, transaction looks for an empty ITL slot, and allocates it. If no slots are
available, one will be allocated from free space in the block, if available, and
not limited by MAXTRANS. If that fails, transaction will wait on a TX enqueue in
mode 4. When the ITL slot has been allocated it will be set to point to the
rollback segment header that was previously reserved by this transaction.

Once an ITL slot has been allocated in the block, specific rows must be marked
as locked. This is done in the row directory. The lock byte will be set to point to
ITL slot of this transaction. This is how an actual row-level lock is maintained
by Oracle. Once that's done, the row may be updated, (or not, if it's just a select
for update). If changes to the block are done, the before images are recorded in
the rollback segment where the header slot has already been reserved.

Finally, a commit or rollback will release the lock. Note that in the case of commit,
the only thing that *must* happen to release the lock is the rollback segment header
slot is marked as committed. Cleanup of the block itself can, any in many cases will,
be delayed to a later date.

Hope that helps,

-Mark

[mbobak]

Hi Don,

I like your new definitions of XCUR and CR much better, but I'm not sure I'd call CR a "downgraded"
block, and and the reason why will also directly answer your first question.

I'm fairly certain, but not 100% sure, that Oracle creates a CR block from a XCUR (or SCUR? I'm
not a RAC expert), by cloning the XCUR block, and then referring to rollback, rolls back the block
till it's sufficiently old, to be read consistent to the point in time required. So, if a query's
snapshot SCN is N, but a particular DBA (data block address) is only available in current mode,
and the SCN is N+100, then Oracle will clone it, and mark it as CR, and start rolling back, using
the information it the block's ITL slot(s) to bakctrack where the various before images are.
Eventually, one of two things will happen, either the block will be sufficiently old (with an SCN of
N or less) or, Oracle will not be able to find the required information in the rollback segment,
which would result in ORA-1555.

And yes, I believe the XCUR block is kept in the buffer cache after a transaction completes.
(Again, there may be some gotchas in the RAC case, I'm not sure about that case.)
Consider also, that a block in XCUR mode can be flushed to disk, even if there is a transaction
open on the block. Oracle doesn't care. If you think about how the locking model works, and
how read consistency works, it really doesn't matter. If a block with one or more row level locks
is flushed to disk, and then the transaction commits, what happens? Well, Oracle certainly
won't re-read the block just to update the row lock and ITL slot. So, what does it do? It
marks the transaction as committed in the rollback segment slot header, and moves on.
If another process comes along and reads or updates that block, delayed block cleanout will
kick in to clean up the mess that was left behind.

Some of the above info borders on Oracle Internals, and may be out-of-date or just plain
wrong....but I believe it to be correct.

Hope that helps,

-Mark

P.S. I really like how, when entering this text, I could scroll back and see the previous messages,
and in reverse order, even! Nice touch!

[burleson]

Hi Mark,

Interesting! Thanks much! I have updated my description, and I hope that you don't mind if I quote you:

http://www.dba-oracle.com/t_v$bh_stat...ffer_blocks.htm

I'm currently working on a production 10g database where ASMM was installed as the default, and even thbough I have identified the issue as a disk update bottleneck (RAID 5), I see evidence that ASMM is not allocating enough data buffer blocks:

Here is an actual example from an ASMM 10g database showing only one free block in the data buffer:

Here is an actual example from an ASMM 10g database showing only one free block in the data buffer:

STATUS  NUMBER_BUFFERS
------- --------------
cr                616
free                1 
xcur            14790

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                        % Total
Event                                Waits    Time (s)  DB Time    Wait Class
------------------------------ ------------ ----------- --------- --------------
log file parallel write              9,670        291    55.67    System I/O
log file sync                        9,293        278    53.12      Commit
CPU time                                          225    43.12
db file parallel write                4,922        201    38.53    System I/O
control file parallel write          1,282        65    12.42    System I/O

Have you had any real-world experience with ASMM?

[mbobak]

Hi Don,

I have no real experience w/ 10g. I've set up a test instance and played w/ it,
but that's about it, unfortunately. We are preparing to move some of our current
8i databases directly to 10g, probably in the next 3-6 months. That should provide
me a good opportunity to get some real-world 10g experience.

Um, what is ASMM? I'm familiar with ASM (Automated Storage Management),
introduced in 10g, where you've got to set up a separate ASM instance, and Oracle
will manage you disk subsystem for you, taking care of I/O load balancing, striping, etc.

Also, I'm familiar with ASSM (Automatic Segment Space Management), which
is introduced in 9i, which uses bitmap blocks to replace free lists.

-Mark

[nlitchfield]

Hi Don,

I'm assuming that by ASMM you are referring to automatic memory management in 10g? I get confused by the automatic everything as well. Anyway, if that is the case then the buffer cache *may* be undersized as a result of an artificially low SGA_TARGET and/or high values (required or otherwise) for the other memory components - shared pool etc. If the overall allocation of RAM is low, my experience is that the buffer cache tends to lose out to the shared pool.

Incidentally I'm a little thrown by the %db time in your statspack? extract. It appears to show the top two database events as taking over 100% of the database time. That doesn't seem correct.

Niall

[nlitchfield]

Hi Don

Is there anything else running on the server?, I'm not actually convinced that the db needs more memory allocated to it, but if you are concerned about the amount of memory allocated to the buffer cache it might well make sense to raise the SGA_TARGET and SGA_MAX_SIZE parameters from 280mb on a 4gb ram box. Certainly I'd give Oracle more RAM to play with before I altered the memory management from dynamic to manual.

If anything the output from the AWR suggests to me that the db isn't doing much, which is fortunate because if it was it looks like its using a load of unshareable sql and committing too often.

Niall

[burleson]

Also note that this database has one disk read/sec. but 2 disk writes/sec.

~~~~~~~~~~~~                            Per Second      Per Transaction
                                  ---------------      ---------------
                  Redo size:              7,828.67              2,944.45
              Logical reads:                464.62                174.75
              Block changes:                52.33                19.68
            Physical reads:                  1.03                  0.39
            Physical writes:                  2.80                  1.05

[JGarmany]

Niall,
From the report (and other scripts) we were pretty sure that the buffer needed some room. The real question (which I guess we will have to run some test to find out is ) if we give it more room for the SGA will it allocate it to the buffer cache? As you say, the lack of bind variables is driving the shared pool much larger that I would have it set if I was manually tuning it. Will ASSM just continue to load more SQL in the shared pool?

John