Julian Sharp (73064.3277@CompuServe.COM) wrote:
: Cor

: With regards IX table level locks being taken on all tables in a 
: db procedure. This is correct (but not necessarily good) and 
: will happen whether the proc is fired by a rule or executed from 
: an application. It is true that locks are taken on a table even 
: if the SQL referencing the table is never executed. In fact the 
: IX locks are taken on all tables at the beginning when the 
: procedure is invoked and if the procedure is large and the 
: transactions long then multi-user performance is going to be hit. 
: I have often seen large db procs with 10 or more tables causing 
: problems when more often than not only one table is actually 
: referenced. There is no real get around other than to design for 
: it at the beginning. I hope this problem may be tackled in a 
: later release (if it hasn't already been done in OpenINGRES)

: -- 
: Julian Sharp
: 73064.3277@compuserve.com
>
>The reason for this is a feature (?) rather than a fault.  The feature
>being that you will never get a deadlock within a database procedure,
>hence the pre-emptive locks are taken.  There are two ways to consider
>the issue.  The thread so far tackles the issue in the manner of why
>take the locks if not required.  The answer is to guarantee no deadlocks.
>Whether or not this is the approach that should be taken is a matter for
>debate.  The other way to consider it is to write database procedures
> that only take locks on tables that are required, that is place the 
>decision logic outside of the DB Proc where possible.  This is obviously
>often easier said than done, however its effects are twofold.  Locks are
>only taken on tables that are to be used and the size of our DB Proc is
>smaller and hence easier for the OPF, QSF and QEF facilities to handle.
>
>
>Sal Ciardulli
>Database Consultant					salc@ingres.com
>-- 
>Axis Consulting International
>Email : axisnet@netcom.com
>Phone : (415) 434 2947
>Fax   : (415) 434 2951
>San Francisco California	Melbourne Australia		London England

I have to disagree, but this is not correct.  As Cor states, the purpose
of intent locks is to support a granular locking protocol as you will find
described in most books on transactions.

Deadlock can still occur within database procedures.  In fact, this whole
area is a "can of worms" in the current implementation of Ingres!  Look
at the description of bug 48557 in the 6.4/04 release notes and you will
see what I mean.

As it stands at 6.4, deadlock can occur *on entry* to a database procedure,
before the first statement is executed.  That means the first statement
in every procedure has to check for errors!  Alas, because of another bug
you can't do this reliably as *sometimes* 'iierrornumber' is set to a
random number on procedure entry, even though no error occurred.

So at the moment you have to live with the possibility of missing a deadlock
(and thus an aborted transaction) when database procedures are used.  If a
deadlock occurs while trying to open a table on entry to a database
procedure, the current transaction is aborted, but the procedure continues
and is entered (in a new transaction), hopefully with 'iierrornumber' set
to indicate what's wrong!  Serious stuff, I think, as this may result in
a lost update.

I've never seen a customer database procedure check for errors before
issuing any statements.

Technically, the implementation is quite complex, as internally the
server makes up to 20 attempts to acquire the locks needed on procedure
entry (hoping that the deadlock partner might have released some locks).
This also happens when a COMMIT statement or similar is issued inside
a procedure (which is probably a bad idea as a result).

In summary - a nightmare.

With regard to the last comment, putting decision logic outside of
database procedures negates much of the performance benefits, especially
in a client/server environment where the round-trip SQL statement time
is the dominating factor.  Having said that, it's still worth trying
to make procedures as short as possible given the very poor memory
handling in some of the server facilities (e.g. QSF).

-Mike Glendinning, Sequent Computer Systems Ltd. (mikeg@sequent.com).

Ingres Q & A

To William's Home Page

© William Yuan 2000

Email William