Stored procedures locking behaviour 
Author Message
 Stored procedures locking behaviour

I have today downloaded Rafal Czerniawski's
excellent article on Stored Procedures  from the
IIUG website.

It mentions exclusive locking of system tables when
creating and re-optimising SPs.  This is not
covered as far as I can tell in TFM.

Does anyone know of where I can get more detail
on this or alternatively provide the answers to the
following questions?

1.  What tables are locked during the create
process.
2. What tables are locked during re-optimisation.
3. What table access is required to *run* an SP
(without re-optimising.)

Our current policy is to allow new or changed SPs
to go live with an application level outage only.  On
reading Rafal's article I am wondering whether we
have been lucky so far, and need to only put SPs
live during a general database outage.

TIA

Zespri International Limited            
This posting may not be used by any party to vilify
another.  Standard disclaimers apply.



Wed, 18 Jun 1902 08:00:00 GMT
 Stored procedures locking behaviour



Quote:
>I have today downloaded Rafal Czerniawski's
>excellent article on Stored Procedures  from the
>IIUG website.

>It mentions exclusive locking of system tables when
>creating and re-optimising SPs.  This is not
>covered as far as I can tell in TFM.

>Does anyone know of where I can get more detail
>on this or alternatively provide the answers to the
>following questions?

>1.  What tables are locked during the create
>process.

  sysprocplan, sysprocbody(?) sysproctext(?).

Quote:
>2. What tables are locked during re-optimisation.

  again sysprocplan

Quote:
>3. What table access is required to *run* an SP
>(without re-optimising.)

  Generally the sysproc tables in share mode i.e. nothing to worry
about.

Quote:

>Our current policy is to allow new or changed SPs
>to go live with an application level outage only.  On
   Sounds fine.
>reading Rafal's article I am wondering whether we
>have been lucky so far, and need to only put SPs
>live during a general database outage.

  The only problem is that no-one must be exeucting the procedure whilst
  it is being updated ot else they will run the old one.

Quote:
>TIA

>Zespri International Limited            
>This posting may not be used by any party to vilify
>another.  Standard disclaimers apply.

--
David Williams


Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. locking behaviour while droping database procedure

2. HELP !! - ODD BEHAVIOUR IN STORED PROCEDURE

3. Different behaviour w/ same stored procedure

4. strange behaviour of SqlServer-Stored Procedure

5. Strange behaviour from Stored procedure after starting SQL Server

6. Strange behaviour with Stored procedures and Views

7. Strange behaviour with Stored procedures and Views

8. Peculiar Behaviour in Stored Procedure - reg

9. Peculiar Behaviour in Stored Procedure - reg

10. Odd behaviour with Stored Procedures and Oracle (that old chestnut)

11. ADO, Sql Server, RaisError, Stored Procedure behaviour

12. Calling a Java Stored Procedure from another Java Stored Stored Procedure


 
Powered by phpBB® Forum Software