| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Locking mode in 10g seems to have changed
Thanks for your reply.
Sybrand Bakker wrote:
> So you have an unscalable app. That problem should be resolved!
With every new release of Oracle we test and make adjustments if necessary. The product is quite scalable given that it runs on Oracle 7, 8 and 9. I am at the stage of making it work for 10 now.
> I don't agree it can't be done. It can.
I didn't say it couldn't be done. It's a matter of finding time to overhaul a working legacy product and fully test it versus the payoff for doing so. We already have other plans for the future of this product (in a new application) that do not include the time it would take to completely change the way the existing application works.
> You are using explicit locking. Could you explain why? Oracle locks
> automatically. The lock table statement below is redundant.
It isn't redundant because of the following situation. If two processes attempt to find the next available unique ID for the table then they could both retrieve the same value if they don't implement the lock.
Table1: Current maximum value of ID field = 25
Without lock:
Session1: Retrieve maximum value of ID + 1 = 26 Session1: Insert into table1 Session2: Retrieve maximum value of ID + 1 = 26 Session2: Insert into table1 Session1: Commit Session2: Commit -- receive constraint error (at best)
With the lock:
Session1: Lock table1 Session2: Lock table1 ... rejected (try lock again in loop) Session1: Retrieve maximum value of ID + 1 = 26 Session1: Insert into table1 Session1: Commit Session2: Lock table1 Session2: Retrieve maximum value of ID + 1 = 27 Session2: Insert into table1 Session2: Commit
> There are two different lock types involved. You don't show *which*
> object has the lock. The DML lock definitely isn't a record lock.
Both of those records appeared in the dba_lock table after I initiated the lock. They both had the same session_id. I am no DBA so I cannot fully interpret those results however the DML lock is the one in question. Why would it be different in two versions of Oracle. More importantly why is it an SX lock in 10g and an SS lock in 9i? Here is a different query that I just received from my DBA. Maybe it will clarify this issue:
SELECT object_name,a.locked_mode, DECODE(a.locked_mode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
With this query the results are thusly (and only one record is returned so there is nothing to confuse):
10g
LOCKED_MODE = 3
MODE_HELD = Row-X (SX)
9i
LOCKED_MODE = 2
MODE_HELD = Row-X (SS)
> If there would have been changed behavior, that would have been
> documented in the new features manual and/or the release notes.
Intentional features are often documented (though not always). In my experience I believe Oracle does have a great documentation practice. However, Oracle has introduced unexpected behavior in the past (as have countless other companies ... mine included) so it is not unreasonable for me to believe that this changed behavior was introduced in 10g without anyone's knowledge. Whether it was intentional or not it exists for the 10g boxes that I have access to at my company and at those sites that have granted us access.
We can go back and forth with this argument but that won't answer the question of why this behavior changed. I might have no other choice but to dig into the product and use sequences (which has been a recommendation for a while) but I would still like some closure on the broader question.
 |
 |