Stored Procedure Changes - Functional Specification
Description
Altering a stored procedure will make a new copy if the current stored procedure is in use. Dropping of a stored procedure will drop the procedure from system tables. However the cached procedure will not be dropped if it is in use. The obsolete procedure will be eventually be freed when no one uses it or when the last attachment to the database goes away.
User Interface/Usability
Consider the case of a simple procedure A (procedure that doesn't call any other procedures).
User1 executes procedure A. At the same time User2 drops or alters A. User2's request is successful. Meanwhile User1 continues his execution without being bothered and finishes successfully. Now if User1 tries to execute A again then a) He will get the new procedure if User2 altered it. b) He will not see A if User2 dropped it.
Consider the case of complex procedures (procedure that call other procedures but does not recurs or loop). For e.g. procedure A calling procedure B calling procedure C (A->B->C). User1 execute A. User2 comes in with the request to drop/alter A, B, or C
Case 1 drop/alter A:
User2 successfully drops/alters A. User1 meanwhile merrily execute A and completes successfully. Now User1 tries to executes A again. a) He will get the new procedure if User2 altered it. b) He will not see A if User2 dropped it.
Case 2 alter B:
User2 successfully alters B. User1 meanwhile merrily execute A and completes successfully. Now User1 tries to executes A again. He will continue to see old B. The reason being that A has a cached request which refers to old B. Until A is dropped or altered User1 will continue to see old B.
If User2 executes A now he too will continue to see old B despite the fact that it was him who altered/dropped B. The reason for this behavior is the fact that procedure A is still referring to old B and until it releases interest in B (which would happen when A is altered or dropped) old B continues to remain in memory.
However if either User1 or User2 tries to execute B directly they will see the new B.
Case 3 alter C:
User2 successfully alters C. User1 meanwhile merrily execute A and completes successfully. Now User1 tries to executes A again. He will continue to see old C. The reason being that B has a cached request which refers to C. Until B is dropped or altered User1 will continue to see old C.
If User2 executes A now he too will continue to see old C despite the fact that it was him who altered/dropped C. The reason for this behavior is the fact that procedure B is still referring to old C and until it releases interest in C (which would happen when B is altered or dropped) old C continues to remain in memory.
However if either User1 or User2 tries to execute C directly they will see the new C.
Note
Dropping of procedures B & C will be inhibited by the dependency check posed by the engine
Case 2 & 3 could be applied for all the procedure in the chain i.e. if A->B->C->D->....Z. The cached procedures gets cleaned up when the last attachment disconnects.
Consider the case of recursive procedures ( A->A, or A->B->A etc.)
A->A
User1 executes recursive procedure A. User2 comes in with the request to alter/drop A. User2's request is successful. Meanwhile User1 continues his execution without being bothered and finishes successfully. Now if User1 tries to execute A again then a) He will get the new procedure if User2 altered it b) He will not see A if User2 dropped it. Note that the behavior is very similar to that of simple procedure. However unlike simple procedure, The recursive procedures will remain to be around. The old procedure doesn't get cleaned up when no one else is using it thereby resulting in memory leaks. Eventually they are freed when the last attachment to that database disconnects.
A->B->A
User1 executes A. User2 comes in to alter/drop A or B
Case 1 User2 is altering/dropping A
User2 successfully drops/alters A. User1 meanwhile merrily execute A and completes successfully. Now User1 tries to executes A again. a) He will get the new procedure if User2 altered it. b) He will not see A if User2 dropped it.
If either User1 or User2 tries to execute B then then they will see old A because the cached request of B still points to old A.
Case 2 User2 is altering/dropping B
User2 successfully drops/alters B. User1 meanwhile merrily execute A and completes successfully. Now User1 tries to executes A again then he will still see old B.
If either User1 or User2 tries to execute B then then they will see new B.
Requirements and Constraints
General InterBase requirements