We are developing a complex OO application, and have put immense effort into
making sure our locking model is intact. Oracle's locking model is definitely
watertight, but you must follow strict rules during app development. All
consistency hinges around select for update, if you are doing optimistic
locking. If you intend to do pessismistic locking, all update statements must
have a WHERE clause, with a version counter, to ensure the underlying record has
not changed.
Most applications use select for update (we use both models). We are also
developing on SQLServer7, and working out complex locking problems is a lot more
difficult on SS7, because of the fact that SS7 escalates row locks (above a
certain threshold per page) to page locks, which ends up locking more records
than intended. This is a 'feature' accoording to Microsoft, the thinking being
that locks can acquired faster (what rubbish). Oracle *Never* escalates locks,
which is the difference between a database that has been fully implemented, as
opposed to one which has not.
Oracle databases carry a major processing overhead in using rollback segments,
but it prevents the phantom read problem, which is a problem on many other
RDBMS's.
Microsoft have a white paper on converting oracle databases to SS on technet,
check it out, but watch out for the frequent Ms spin on reality.
Any database consultant that makes sweeping statements about the locking
integrity of a database with thousands of highend OLTP systems (Oracle or not)
doesnt know what he is talking about. Ask for test cases to demonstrate his
concerns.
Regards
Mark
- ERG Group --------------------------
The contents of this email and any attachments are confidential
and may only be read by the intended recipient.
Received on Fri Jul 28 2000 - 03:04:29 CDT
