Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Polymorphed Proc Expand / Collapse
Author
Message
Posted Monday, May 11, 2009 3:43 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 5, 2013 11:51 PM
Points: 488, Visits: 336
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.


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

Be Happy!
Post #713948
Posted Monday, May 11, 2009 10:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:08 PM
Points: 1,156, Visits: 801
"
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.
Post #714311
Posted Monday, May 11, 2009 2:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:08 PM
Points: 1,156, Visits: 801
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.
Post #714518
Posted Monday, May 11, 2009 2:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:38 PM
Points: 3,214, Visits: 2,335
self healing or mutation ??? hmmm ... sounds like malware or a virus to me ...



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #714521
Posted Monday, May 11, 2009 2:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 4, 2014 6:48 AM
Points: 1,111, Visits: 542
OMG!! It's the beginnings of Skynet!


"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
Post #714532
Posted Monday, May 11, 2009 3:14 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:38 PM
Points: 3,214, Visits: 2,335
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 ...




Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #714545
Posted Tuesday, May 12, 2009 1:46 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 5, 2013 11:51 PM
Points: 488, Visits: 336
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

Be Happy!
Post #714766
Posted Thursday, May 14, 2009 3:20 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 10, 2012 6:08 PM
Points: 1,156, Visits: 801
saurabh.dwivedy (5/12/2009)
Dear DPhillips,

Thank you for the painstakingly elaborate explanation.


Glad I could help!
Post #717441
Posted Tuesday, June 23, 2009 7:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:40 PM
Points: 8,743, Visits: 9,292
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
Post #740170
Posted Tuesday, June 21, 2011 4:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, June 9, 2014 1:24 AM
Points: 375, Visits: 114
apart from al syntax discussions, I enjoyed this question. Thanks QoD
Post #1128868
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse