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 ««12

All About Transactions - Part 1 Expand / Collapse
Author
Message
Posted Tuesday, November 16, 2004 3:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408

It is generally very bad practice to wait for user input inside a transaction.  But the scenario you lay out would be a worst-case.  Not only would you have to wait for user input within the transaction, you would also have to allow dirty reads.  As you point out, this is a recipie for disaster both from a performance as well as a security standpoint.  I would say that any developer and/or DBA that wrote an application that badly should most certainly be fired, no questions asked.

Without knowing all the details, I would say that the temp table would mitigate the security risk somewhat, but poorly concieved transactions are the root problem and it doesn't help at all with that.




/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #146603
Posted Tuesday, November 16, 2004 3:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 01, 2011 2:05 PM
Points: 32, Visits: 47
I've seen this in off the shelf software and fully agree with you.

Cheers

Stephen



Post #146605
Posted Thursday, November 25, 2004 12:57 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 06, 2013 2:16 AM
Points: 693, Visits: 124

Don, you wrote in the article:

"It doesn't matter where the BEGIN TRAN and COMMIT statements reside between the two procedures. [...] So where you place your BEGIN...COMMIT is pretty much a matter of preference, not function."

This is true, as long as you put both the BEGIN TRAN and the COMMIT in the SAME procedure. For example, the following code would result an error:

CREATE PROCEDURE test 
AS
BEGIN TRAN
-- and do some work here
GO
EXEC test 
COMMIT

And here is the result:

Server: Msg 266, Level 16, State 2, Procedure test, Line 4
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

Razvan




Post #148073
Posted Friday, November 26, 2004 6:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408
Thanks for the clarification. I had not thought of that interpretation of what I said...


/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #148254
Posted Wednesday, November 16, 2005 6:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 26, 2009 7:55 PM
Points: 35, Visits: 12

I think this is one of the best articles that I have read here. It is clear, stays focused, go straight to the point, has no grammatical errors (well, I did spot one tiny missing comma), no spelling errors and does not contain acronyms that only an old and seasoned DBA would understand. Your hired

 

Del

Post #237504
Posted Thursday, February 01, 2007 11:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 2:51 AM
Points: 820, Visits: 15
Hi,

I have never new that there is so much about transactions. Its really a very good article.

Raj
Post #341737
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse