May 11, 2009 at 3:43 am
DPhillips (3/17/2009)
Chris Harshman (3/17/2009)
I'm just suprised that SQL Server would let a stored procedure modify its own code. That's the real head scratcher here. :ermm:Many code environs can allow this, especially in DBs where the SPs are actually stored within the system, not individual physical disk files. This is not unique only to SQL Server.
The trick is, it isn't modifying its' own code... the in memory version is modifying the saved version... it doesn't need to lock the file for writes, as it has read the whole thing in. Any internal sub-executions are also read into memory and the saved object is still free for ALTER/DROP.
Having a valid reason to do so is another issue entirely, but functionally there is no reason to dis-allow it.
When you say the "in-memory" version is modifying the "saved version" - I seek a bit more clarity here. The difference between "in memory" and "saved version" will only be the difference between an uncompiled and compiled code -
I write a code, compile it and execute it. When I execute the code, it gets loaded in memory.
Now I make changes to it. I Save it but don't execute it yet. This, according to me, is the "saved version' and is - as it stands at the moment - different from the "in-memory" version because I still haven't executed the "saved code".
So, in the present example: I create a proc and then within the body of the proc I issue an Alter Proc command and then Execute the proc itself. Which PROC is it going to ALTER and EXECUTE? Which are the saved and in-memory versions here?
Regards
Please correct my understanding because I am totally lost here.
Regards
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 11, 2009 at 10:20 am
"
CREATE PROC GO AS BEGIN
EXEC ('ALTER PROC GO AS SELECT NULL')
EXEC GO
DROP PROC GO
END
GO
EXEC GO
"
So to start we have actually TWO primary initiated commands.
First:
"
CREATE PROC GO AS BEGIN
EXEC ('ALTER PROC GO AS SELECT NULL')
EXEC GO
DROP PROC GO
END
GO
"
And Second:
"
EXEC GO
"
The first one executes and completes whether or not it remains leftover in memory has no further effect on any subsequent processing of it.
The second one is the one that loads the saved version for execution.
So, at this point, what is loaded and has begun to execute is:
"
PROC GO AS BEGIN
EXEC ('ALTER PROC GO AS SELECT NULL')
EXEC GO
DROP PROC GO
END
"
Now, execution line-by-line is:
"EXEC ('ALTER PROC GO AS SELECT NULL')" Note that this is modifying the SAVED version, not the in-memory current run version.
The next line executes the newly modified saved version of:
"PROC GO AS SELECT NULL"
Note that at this moment, *TWO* different instances and versions of proc "GO" are running.
This SECOND "inner" instance in memory completes the Select, and returns a NULL. It reaches completion and ends.
The FIRST "outer" instance in memory now continues the next command:
"DROP PROC GO",
which serves to drop the saved version, and has no bearing on the currently running memory version.
Now the last command in the FIRST "outer" version is, "END", which terminates the only remain ("first") memory version.
Let's recap the order of events again, in other more human language:
1. Version 1 of PROC GO is saved.
2. Version 1 of that PROC is EXECuted (into memory) and becomes memory version 1.
3. Memory version 1 changes saved version 1 to saved version 2, keeping the same name, "GO".
4. Memory version 1 EXECutes new saved "GO" (version 2) into memory and completes having executed the SELECT, and goes into the ether, but version 2 still exists as saved PROC "GO".
5. Memory Version 1 continues by dropping whatever version is stored as "GO" (happens to be version 2 as the only version 1 that still exists is this running thread).
6. Memory version 1 ENDs and evaporates into the ether.
Hope this helps clear it all up. The in memory version is not the saved version, whether either is compiled or not.
May 11, 2009 at 2:43 pm
I wanted to add that it is definitely by design that multiple instances of the same named Procedure can be executed at the same time. If not, then recursive trees or structures can be very painful to write.
The fact that the procedure can be modified while other active instances of that procedure are still running is also by design, especially for adaptive code sets.
I still have never run into an instance where it was necessary for a piece of code to modify itself, but this borders on wild frontiers where code can be made to be self healing and/or self mutating. That is both scary and wonderfully thought-provoking.
I think we are yet only dabbling in the shore-line of an ocean of future possibilities.
May 11, 2009 at 2:46 pm
self healing or mutation ??? hmmm ... sounds like malware or a virus to me ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 11, 2009 at 2:59 pm
OMG!! It's the beginnings of Skynet! :hehe:
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
May 11, 2009 at 3:14 pm
Skynet begins to learn at a geometric rate. It becomes self-aware at 2:14 a.m. Eastern time, August 29th. In a panic, they try to pull the plug. ...
Glad it was in 1997 ... or is it yet to come ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 12, 2009 at 1:46 am
Dear DPhillips,
Thank you for the painstakingly elaborate explanation. Took me a while to figure out - not because the explanation was lacking in anyway - but due to my own capacity to assimilate the information.
Thanks once again.
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 14, 2009 at 3:20 pm
saurabh.dwivedy (5/12/2009)
Dear DPhillips,Thank you for the painstakingly elaborate explanation.
Glad I could help!
June 23, 2009 at 7:50 am
DPhillips (5/11/2009)
I still have never run into an instance where it was necessary for a piece of code to modify itself, but this borders on wild frontiers where code can be made to be self healing and/or self mutating. That is both scary and wonderfully thought-provoking..
One use for this sort of thing (but with conditional self-modification, raher than unconditional as in the question) is code that changes its behavious the first time it is run after certain kinds of intrusion are detected. I'm not sure if this is useful in the T-SQL case, but it has been used in other languages (where it tends to be much more diffiult than it is in T-SQL).
And as for code that drops itself - I used to use that sometimes when I want to be sure something only happens once, as part of a belt and braces approach to some kinds of data upgrade: the proc that does the upgrade firsts drops itself so that no-one can try calling it again, then checks whether the upgrade has already been done by seeing if a "this is already done" flag exists, then does the upgrade (altering much data) and creates the flag. Now I don't do it that way any more - not sure why.
Tom
June 21, 2011 at 4:29 am
apart from al syntax discussions, I enjoyed this question. Thanks QoD
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply