Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to make lock escalation look good...

Re: How to make lock escalation look good...

From: Jim Kennedy <kennedy-down_with_spammers_at_attbi.com>
Date: Wed, 21 May 2003 14:03:35 GMT
Message-ID: <XGLya.932362$F1.115198@sccrnsc04>


I've worked with DB2 and it requires throwing most transactions out the window. Worse than that dynamic sql MUST be followed immediately by a commit or not one else can do one. (their query tool does this for you) DB2 does NOT do dynamic sql; it does static sql. What it does with "dynamic" sql is create a plan and bind it in then run it - turning dynamic to static and that puts a lock on the plan table until you commit. So anyone else running dynamic sql can't until you commit. (plan table is a source of serialization for the entire system). When I worked with it (DB2 on a mainframe) we had to make sure all dml had a commit immediately after it. UGH. What a concurrency model! xbase was better than that. Jim

-- 
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Andy" <andy.spaven_at_eps-hq.co.uk> wrote in message
news:FJHya.199$95.40_at_newsr2.u-net.net...

> Nuno
>
> I especially like the absence of pieces of information or disingenous
white
> lies.
>
> 1 - no mention is made of what happens should the process trying to
escalate
> exclusive locks to an exclusive table lock find that other processes
> currently have row locks on rows in that table. This task that "can
> therefore finish its work and get out of everyone elses way" must surely
> have to wait for those locks to be released (here I'm talking about
> exclusive locks not read locks) and therefore not "get out of everyone
elses
> way".
>
> 2 - the implication that Oracle is storing locking information to disk
> whereas IBM use memory - the implication that this is faster. Given that
> any block currently being amended will be in the block buffer cache - the
> locking information will actually be added at this point in time - i.e. in
> memory.
>
> 3 - the implication that Oracle is wasteful in using more disk space - up
to
> 12% according to the document - has anyone ever seen an ITL of 4 or more
> even on an intensive OLTP app. I guess there must be some but I've not
yet
> witnessed one. Anyone know of sites still using 2K block size for new
> deployments for OLTP with Oracle ? That aside has anyone had a customer
> deploying a multi-terrabyte storage system even seriously consider trying
to
> save a few % of space and therefore a few % on disk costs by switching
> database vendors.
>
> Does anyone on this NG have experience of systems using page locking and
> lock escalation (I know there must but does anyone have any comments to
> make) especially on DB2 ? I'd be interested to learn more.
>
>
> It's the sort of disinformation seen in this doc that drives me crazy
> because it's just this sort of "information" that a sales & customer
account
> managers will give to some non technical manager tasked with purchasing a
> new system. I hate it because I then end up explaining to people why such
> and such an official document isn't quite so relevant / appropriate or is
in
> some cases not even true.
>
> Oracle are just as bad. I'd love to see a DB2 specialist take the Oracle
> document mentioned at the foot of the IBM one to pieces with
"inaccuracies".
> I'm sure there are some - it's marketing literature by another name as
> Daniel points out.
>
> Andy
>
>
> "Nuno Souto" <wizofoz2k_at_yahoo.com.au> wrote in message
> news:73e20c6c.0305201424.4b707c41_at_posting.google.com...
> > Just came across this pearl at an IBM site:
> >
> > http://www-3.ibm.com/software/data/pubs/papers/locking/locking.pdf
> >
> > Amazing! How to make lock escalation "look and feel" like
> > an "advantage", while completely distorting and misrepresenting
> > the Oracle lock mechanism.
> > <sigh>
> >
> >
> > Cheers
> > Nuno Souto
> > wizofoz2k_at_yahoo.com.au.nospam
>
>
Received on Wed May 21 2003 - 09:03:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US